---
id: ML-169.2
title: "Phase 4: Performance low-hanging fruit audit"
status: Done
assignee: []
created_date: "2026-05-08 08:59"
updated_date: "2026-05-19 08:50"
labels:
  - audit
  - ready
dependencies: []
references:
  - lib/music_library_web/live/stats_live/index.ex
  - lib/music_library_web/live_helpers/index_actions.ex
  - lib/music_library_web/live_helpers/record_actions.ex
  - .agents/skills/query-reporter/SKILL.md
  - /tmp/queries-ml-audit.sql
  - >-
    backlog/documents/doc-27 -
    Phase-4-Performance-Audit-Low-Hanging-Fruit-Findings.md
parent_task_id: ML-169
priority: medium
ordinal: 2000
---

## Description

<!-- SECTION:DESCRIPTION:BEGIN -->

Identify unnecessary DB queries, redundant full-stream reloads, server round trips that could be client-side, missing optimistic UI opportunities, and blocking mount work that could be deferred.

Use QueryReporter skill to capture SQL traces for key page loads as evidence.

Key pre-flagged areas:

- StatsLive.Index: 5 synchronous queries in mount (TTFB impact)
- Display toggle (grid/list): full FTS query when CSS-only suffices
- ScrobbleRulesLive + OnlineStoreTemplateLive: redundant stream_insert + immediate full reload
- ScrobbledTracksLive + StatsLive: full stream reset on every scrobble batch instead of incremental insert
- Search inputs: verify phx-debounce is set
<!-- SECTION:DESCRIPTION:END -->

## Acceptance Criteria

<!-- AC:BEGIN -->

- [x] #1 SQL traces captured via QueryReporter for /collection, /collection/:id, /stats, /artists/:id
- [x] #2 Stream reset audit complete: every stream(:name, data, reset: true) call site evaluated for incremental-update alternatives
- [x] #3 Display toggle (grid/list) evaluated: confirmed whether CSS-only toggle can replace full DB reload
- [x] #4 StatsLive.Index mount blocking work quantified: 5 queries identified, assign_async feasibility assessed
- [x] #5 Search input debounce verified: phx-debounce attribute presence checked on all search forms
- [x] #6 Optimistic UI opportunities identified for delete, form save, toggle operations
- [x] #7 N+1 audit complete: handle_params, stream rendering, and LiveComponent Repo calls checked
- [x] #8 Findings report written as a Backlog.md document with file:line references, severity ratings, and fix recommendations
<!-- AC:END -->

## Final Summary

<!-- SECTION:FINAL_SUMMARY:BEGIN -->

## Audit Complete: 9 findings across 4 severity levels

**SQL traces captured** via QueryReporter at `/tmp/queries-ml-audit.sql` for all 4 key pages (StatsLive, CollectionLive.Index, CollectionLive.Show, ArtistLive.Show).

### Key findings:

**HIGH severity (3)**:

1. **Display toggle triggers full DB reload** — grid/list toggle in `handle_set_display` re-executes FTS search when CSS-only toggle suffices. Fix: remove `load_and_assign_records` call.
2. **StatsLive.Index mount: 10 sync queries** — home page TTFB blocked by 10 sequential queries. `assign_async` already used elsewhere on the same page — extend it to cover heavy queries.
3. **Scrobble updates trigger full stream reset** — every 5-minute cron batch causes `reset: true` on all connected clients for both StatsLive and ScrobbledTracksLive.

**MEDIUM severity (4)**: 4. **Redundant stream_insert + immediate full reload** in ScrobbleRulesLive and OnlineStoreTemplateLive — `stream_insert` is immediately nullified by `load_and_assign_*`. 5. **Full resets on pagination** — `reset: true` on page changes when append could suffice. 6. **Missing optimistic UI** for delete operations — DB waits block UI. 7. **Correlated subqueries** in `ListeningStats.tracks_with_record_info_query` — 3 subqueries per row × 100 tracks.

**PASS (2)**: 8. All search inputs have `phx-debounce` set. 9. No direct N+1 issues found in LiveView/LiveComponent layer (no direct Repo calls).

Full report: `docs/backlog/documents/doc-27` — includes file:line references, SQL traces, and fix recommendations for each finding.

<!-- SECTION:FINAL_SUMMARY:END -->