Song artist typeahead scans the whole SongMetadata table per keystroke — consider a normalized SongArtist join table #669

Open
opened 2026-07-26 23:54:39 +02:00 by timothy · 1 comment
Owner

Filed at the #578 implementer's request. #578 shipped the list-valued artist suggestion source and accepted this cost explicitly rather than glossing it; the rejected alternative is named in api.search-field-values-sources with its full cost list.

The cost that was accepted

SongMetadata.Artists / AlbumArtists are EF9 primitive collections: one JSON array per row in a single TEXT/longtext column. Neither provider can project the elements server-side (SQLite needs SQL APPLY; Pomelo MySQL hasn't implemented primitive collections), so there is no server-side SELECT DISTINCT over elements.

#578's solution pre-filters on the raw JSON with a leading-wildcard LIKE, capped at 1000 rows ordered by Id:

SELECT Artists FROM SongMetadata WHERE Artists IS NOT NULL
  AND LOWER(Artists) LIKE @Pattern ESCAPE '/'
  ORDER BY Id LIMIT @Cap

ORDER BY Id (a unique integer PK) removed the full sort and the MySQL max_sort_length nondeterminism. But a leading-wildcard LIKE cannot seek an index, so every SongMetadata row is still scanned. The cap bounds rows returned, not rows scanned.

Every artist request now additionally scans the song table's JSON column — once per typeahead keystroke. It is authenticated by default, but any signed-in or machine-key caller triggers it, and Api:RequireKeyForReads=false makes the endpoint anonymous. There is no request-specific rate limit.

The rejected alternative

A normalized SongArtist join table — the shape MusicVideoArtist already has. Exact and indexable forever, but #578 rejected it on blast radius:

  • a dual-provider migration + data backfill
  • changes to every scanner write path populating SongMetadata.Artists
  • changes to the Lucene indexer
  • two representations of the same fact, free to drift

That was the right call for a typeahead convenience inside #578's scope. It is worth revisiting on its own terms.

Done-when

  • A decision is recorded on whether to normalize (extends or supersedes api.search-field-values-sources) — "keep the scan, it's fast enough at our scale" is a legitimate outcome if backed by a measurement
  • If normalizing: dual-provider migration via scripts/add-migration.sh, backfill, scanner write paths, and the Lucene indexer all updated together — no drift between the two representations
  • If keeping the scan: the cost is measured on a realistic song library (not estimated) and the endpoint is bounded against repeated calls
  • Suggestion results are unchanged for existing fields, pinned by the #578 tests
  • Adversarial review passed

Context: #578. Related: #668 (accented values on the EF-sourced fields).

Filed at the #578 implementer's request. #578 shipped the list-valued artist suggestion source and **accepted this cost explicitly rather than glossing it**; the rejected alternative is named in `api.search-field-values-sources` with its full cost list. ## The cost that was accepted `SongMetadata.Artists` / `AlbumArtists` are EF9 **primitive collections**: one JSON array per row in a single `TEXT`/`longtext` column. Neither provider can project the elements server-side (SQLite needs SQL `APPLY`; Pomelo MySQL hasn't implemented primitive collections), so there is no server-side `SELECT DISTINCT` over elements. #578's solution pre-filters on the raw JSON with a **leading-wildcard** `LIKE`, capped at 1000 rows ordered by `Id`: ```sql SELECT Artists FROM SongMetadata WHERE Artists IS NOT NULL AND LOWER(Artists) LIKE @Pattern ESCAPE '/' ORDER BY Id LIMIT @Cap ``` `ORDER BY Id` (a unique integer PK) removed the full sort and the MySQL `max_sort_length` nondeterminism. **But a leading-wildcard `LIKE` cannot seek an index**, so every `SongMetadata` row is still scanned. The cap bounds rows *returned*, not rows *scanned*. Every `artist` request now additionally scans the song table's JSON column — once per typeahead keystroke. It is authenticated by default, but any signed-in or machine-key caller triggers it, and `Api:RequireKeyForReads=false` makes the endpoint anonymous. There is no request-specific rate limit. ## The rejected alternative A normalized `SongArtist` join table — the shape `MusicVideoArtist` already has. Exact and indexable forever, but #578 rejected it on blast radius: - a dual-provider migration + data backfill - changes to every scanner write path populating `SongMetadata.Artists` - changes to the Lucene indexer - two representations of the same fact, free to drift That was the right call for a typeahead convenience inside #578's scope. It is worth revisiting on its own terms. ## Done-when - [ ] A decision is recorded on whether to normalize (extends or supersedes `api.search-field-values-sources`) — "keep the scan, it's fast enough at our scale" is a legitimate outcome **if backed by a measurement** - [ ] If normalizing: dual-provider migration via `scripts/add-migration.sh`, backfill, scanner write paths, and the Lucene indexer all updated together — no drift between the two representations - [ ] If keeping the scan: the cost is measured on a realistic song library (not estimated) and the endpoint is bounded against repeated calls - [ ] Suggestion results are unchanged for existing fields, pinned by the #578 tests - [ ] Adversarial review passed Context: #578. Related: #668 (accented values on the EF-sourced fields).
timothy added the apipriority: low labels 2026-07-26 23:54:39 +02:00
Author
Owner

Correction — this issue's cost premise is now out of date

I wrote the body from #578's round-1 design. That design changed twice during review, and the final shipped mechanism is materially cheaper. Correcting rather than leaving a stale premise for whoever picks this up.

What changed. Round 1's fixed ORDER BY Id LIMIT 1000 budget turned out to have a correctness bug, not just a cost: because the cap was applied before the exact filter, widening the pre-filter (needed to fix a Unicode false negative) let 1,000 non-matching rows starve out the single real match. album_artist?q=é with 1,000 "zzz" songs and one "éclair" at row 1,001 returned [].

The fixed budget is gone. Candidate rows are now keyset-paged on Id:

SELECT Id, Artists AS Payload FROM SongMetadata
WHERE Artists IS NOT NULL AND Id > @AfterId
  AND LOWER(Artists) LIKE @Pattern ESCAPE '/'
ORDER BY Id LIMIT @Batch

The loop walks past non-matching candidates and stops at the first of: enough distinct exact matches for limit; a short page (source exhausted); or ListValuedMaxScannedRows = 20000 candidates in ListValuedBatchRows = 2000 batches.

So the two claims in the body above are wrong as written:

  1. "every SongMetadata row is still scanned" — the leading wildcard forces scan access, but each page stops once it fills @Batch. A dense query (one where matches are common) finishes early and never approaches the ceiling. Only a sparse query over a large library walks far.
  2. "The cap bounds rows returned, not rows scanned" — inverted by the fix. The bound now limits effort and no longer decides the result. The lossy case requires 20,000 rows that have already passed the pre-filter, versus 1,000 before.

What remains true, and why this issue stays open: a leading-wildcard LIKE still cannot seek an index, so a sparse query over a large song library does real scan work per keystroke, and the 20,000-row ceiling is still a point past which results become incomplete. That is a genuine cost worth revisiting — it is just considerably further out than the body implies.

The ## Done-when above stands unchanged; the "measure it on a realistic library rather than estimate" box is now the load-bearing one, since the estimate in the body was mine and was wrong.

## Correction — this issue's cost premise is now out of date I wrote the body from #578's round-1 design. That design changed twice during review, and the final shipped mechanism is materially cheaper. Correcting rather than leaving a stale premise for whoever picks this up. **What changed.** Round 1's fixed `ORDER BY Id LIMIT 1000` budget turned out to have a *correctness* bug, not just a cost: because the cap was applied **before** the exact filter, widening the pre-filter (needed to fix a Unicode false negative) let 1,000 non-matching rows starve out the single real match. `album_artist?q=é` with 1,000 `"zzz"` songs and one `"éclair"` at row 1,001 returned `[]`. The fixed budget is gone. Candidate rows are now **keyset-paged on `Id`**: ```sql SELECT Id, Artists AS Payload FROM SongMetadata WHERE Artists IS NOT NULL AND Id > @AfterId AND LOWER(Artists) LIKE @Pattern ESCAPE '/' ORDER BY Id LIMIT @Batch ``` The loop walks past non-matching candidates and stops at the first of: enough distinct exact matches for `limit`; a short page (source exhausted); or `ListValuedMaxScannedRows = 20000` candidates in `ListValuedBatchRows = 2000` batches. **So the two claims in the body above are wrong as written:** 1. ~~"every `SongMetadata` row is still scanned"~~ — the leading wildcard forces scan **access**, but each page stops once it fills `@Batch`. A dense query (one where matches are common) finishes early and never approaches the ceiling. Only a sparse query over a large library walks far. 2. ~~"The cap bounds rows *returned*, not rows *scanned*"~~ — inverted by the fix. The bound now limits **effort** and no longer decides the result. The lossy case requires 20,000 rows that have *already passed the pre-filter*, versus 1,000 before. **What remains true, and why this issue stays open:** a leading-wildcard `LIKE` still cannot seek an index, so a sparse query over a large song library does real scan work per keystroke, and the 20,000-row ceiling is still a point past which results become incomplete. That is a genuine cost worth revisiting — it is just considerably further out than the body implies. The `## Done-when` above stands unchanged; the "measure it on a realistic library rather than estimate" box is now the load-bearing one, since the estimate in the body was mine and was wrong.
Sign in to join this conversation.
1 Participants
Notifications
Due Date
No due date set.
Dependencies

No dependencies set.

Reference: timothy/ersatztv#669