---
id: ML-169.8
title: "Fix: StatsLive.Index 10 sync queries blocking TTFB"
status: To Do
assignee: []
created_date: "2026-05-19 11:48"
labels:
  - perf
  - fix
dependencies: []
references:
  - >-
    backlog/documents/doc-27 -
    Phase-4-Performance-Audit-Low-Hanging-Fruit-Findings.md
modified_files:
  - lib/music_library_web/live/stats_live/index.ex
parent_task_id: ML-169
priority: high
ordinal: 29000
---

## Description

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

Fix performance issue where the StatsLive.Index mount callback executes 10 synchronous Ecto queries before returning, blocking the initial render (TTFB) on the home page.

## Problem

The mount callback executes 10 synchronous queries before returning `{:ok, socket}`:

1. `Collection.get_latest_record()` — ORDER BY purchased_at LIMIT 1
2. `Collection.count_records_by_artist(limit: 20)` — json_each + GROUP BY
3. `Collection.count_records_by_genre(limit: 20)` — json_each + GROUP BY + subquery exclusion
4. `Collection.count_records_by_release_year(limit: 20)` — substr + GROUP BY
5. `Collection.get_records_on_this_day(date)` — strftime + ORDER BY
6. `Collection.count_records_by_format()` — GROUP BY format
7. `Collection.count_records_by_type()` — GROUP BY type
8. `Wishlist.count()` — FTS search count
9. `ListeningStats.recent_activity(timezone)` — 4 correlated subqueries × 100 rows
10. `ListeningStats.scrobble_count()` — simple COUNT

## Impact

10 blocking queries on the home page is the worst TTFB bottleneck in the app. The `assign_async` pattern is already in use for TopAlbums/TopArtists on the same page — apply it consistently.

## Files

- `lib/music_library_web/live/stats_live/index.ex:67-109` — mount callback

## Fix

Use `assign_async` to defer non-critical queries. The simple ones (collection_count, wishlist_count, scrobble_count for the counter badges) can stay synchronous since they're fast. Move the heavy ones to `assign_async`:

- `count_records_by_artist` / `count_records_by_genre` / `count_records_by_release_year`
- `get_records_on_this_day`
- `recent_activity` (the most expensive — 4 correlated subqueries)
- `get_latest_record`

Show skeleton/loading placeholders while async data loads.

<!-- SECTION:DESCRIPTION:END -->

## Acceptance Criteria

<!-- AC:BEGIN -->

- [ ] #1 StatsLive.Index mount completes in under 200ms (sync queries only)
- [ ] #2 Charts and activity feed load with loading skeleton placeholders
- [ ] #3 Counter badges (collection, wishlist, scrobbles) show immediately
- [ ] #4 No visual flash of empty data containers
- [ ] #5 Assigns that are now async handle nil/failure states gracefully
<!-- AC:END -->
