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
No Branch/Tag Specified
main
renovate/dotnet-monorepo
renovate/meziantou.analyzer-3.x
renovate/lucene.net
renovate/sqlitepclraw.bundle_e_sqlite3-3.x
renovate/cliwrap-3.x
913-scripted-playout-401
release/v26.15.0-notes
issue-806-guard-populations
scratch/767b-poisoned
scratch/767b-control
release/v26.14.0-notes
release/v26.14.0
docs/510-skill-logo-bug-policy
fix/510-watermark-resolution-policy
fix/629-verdict-classifier-falseopens
fix/609-decisions-edit-token-scope
issue-135-clear-to-none
release/v26.12.0-notes
fix/409b-lastscan-api-parity
fix/401-updatechannel-mirror-422
fix/327-playlist-rename-validation
fix/410-scancancel-log-level
fix/409-447-librariesscreen-neverscanned
fix/338-zap-exit-code
fix/367-plex-budget-message
fix/310-debom-legacy-cs
ci/604-lane-rebalance
feat/388-design-mirror
feat/247-test-ownership
feat/247-primary-action
feat/357-player-owned-playback
feat/357-jellyfin-plugin-poc
fix/289-mcp-hardening
issue58-mcp
feat/244-channels-extract
ci/auto-bump-prod-compose
feat/multi-rerun-collections-api
feat/collections-api
feat/quick-wins
feat/185-docs-part2
feat/140-collections-screen
feat/146-channel-edit
feat/147-classic-ui-link
issue22-renovate-dashboard
feat/91-cutover
feat/63-composite-create
feat/65-library-browse
feat/85-epg
feat/86-schedule-editor
feat/109-dashboard-data
feat/99-session-tracking
fix/dockerfile-node-tag
feat/59-spa-foundation
docs/59-ui-redesign-brief
feat/102-json-guide
feat/111-schedule-durations
feat/104-artwork-upload
feat/103-media-sources-api
feat/playouts-read-api
feat/108-health-api
feat/105-picker-list-endpoints
issue-97-channel-state-api
issue42-jellyfin-musicvideos
issue46-rest-api-error-contract
dependabot/nuget/ErsatzTV.FFmpeg.Tests/multi-d307a2e06f
qsv-improvements
hdr-vulkan-cuda-test
v26.16.0
v26.15.0
v26.14.0
v26.13.0
v26.12.0
v26.11.0
v26.10.0
v26.9.0
v26.8.0
v26.7.0
blazor-final
v26.6.0
v26.5.0
v26.4.0
v26.3.1
v26.3.0
v26.2.0
v26.1.1
v26.1.0
v25.9.0
v25.8.0
v25.7.1
v25.7.0
v25.6.0
v25.5.0
v25.4.0
v25.3.1
v25.3.0
v25.2.0
v25.1.0
v0.8.8-beta
v0.8.7-beta
v0.8.6-beta
v0.8.5-beta
v0.8.4-beta
v0.8.3-beta
v0.8.2-beta
v0.8.1-beta
v0.8.0-beta
v0.7.9-beta
v0.7.8-beta
v0.7.7-beta
v0.7.6-beta
v0.7.5-beta
v0.7.4-beta
v0.7.3-beta
v0.7.2-beta
v0.7.1-beta
v0.7.0-beta
v0.6.9-beta
v0.6.8-beta
v0.6.7-beta
v0.6.6-beta
v0.6.5-beta
v0.6.4-beta
v0.6.3-beta
v0.6.2-beta
v0.6.1-beta
v0.6.0-beta
v0.5.8-beta
v0.5.7-beta
v0.5.6-beta
v0.5.5-beta
v0.5.4-beta
v0.5.3-beta
v0.5.2-beta
v0.5.1-beta
v0.5.0-beta
v0.4.5-alpha
v0.4.4-alpha
v0.4.3-alpha
v0.4.2-alpha
v0.4.1-alpha
v0.4.0-alpha
v0.3.8-alpha
v0.3.7-alpha
develop
v0.3.6-alpha
v0.3.5-alpha
v0.3.4-alpha
v0.3.3-alpha
v0.3.2-alpha
v0.3.1-alpha
v0.3.0-alpha
v0.2.5-alpha
v0.2.4-alpha
v0.2.3-alpha
v0.2.2-alpha
v0.2.1-alpha
v0.2.0-alpha
v0.1.5-alpha
v0.1.4-alpha
v0.1.3-alpha
v0.1.2-alpha
v0.1.1-alpha
v0.1.0-alpha
v0.0.62-alpha
v0.0.61-alpha
v0.0.60-alpha
v0.0.59-alpha
v0.0.58-alpha
v0.0.57-alpha
v0.0.56-alpha
v0.0.55-alpha
v0.0.54-alpha
v0.0.53-alpha
v0.0.52-alpha
v0.0.51-alpha
v0.0.50-alpha
v0.0.49-prealpha
v0.0.48-prealpha
v0.0.47-prealpha
v0.0.46-prealpha
v0.0.45-prealpha
v0.0.44-prealpha
v0.0.43-prealpha
v0.0.42-prealpha
v0.0.41-prealpha
v0.0.40-prealpha
v0.0.39-prealpha
v0.0.38-prealpha
v0.0.37-prealpha
v0.0.36-prealpha
v0.0.35-prealpha
v0.0.34-prealpha
v0.0.33-prealpha
v0.0.32-prealpha
v0.0.31-prealpha
v0.0.30-prealpha
v0.0.29-prealpha
v0.0.28-prealpha
v0.0.27-prealpha
v0.0.26-prealpha
v0.0.25-prealpha
v0.0.24-prealpha
v0.0.23-prealpha
v0.0.22-prealpha
v0.0.21-prealpha
v0.0.20-prealpha
v0.0.19-prealpha
v0.0.18-prealpha
v0.0.17-prealpha
v0.0.16-prealpha
v0.0.15-prealpha
v0.0.14-prealpha
v0.0.13-prealpha
v0.0.12-prealpha
v0.0.11-prealpha
v0.0.10-prealpha
v0.0.9-prealpha
v0.0.8-prealpha
v0.0.7-prealpha
v0.0.6-prealpha
v0.0.5-prealpha
v0.0.4-prealpha
v0.0.3-prealpha
v0.0.2-prealpha
v0.0.1-prealpha
Labels
Clear labels
ad-hoc
api
bug
ci-cd
content
dependencies
enhancement
frontend
in-progress
jellyfin
parked
priority: high
priority: low
priority: medium
review
security
One-off / ad-hoc work not tracked by a dedicated issue
REST API / HTTP endpoints
Something isn't working
Build, test, deploy pipeline
Channel content / schedules / playlists
Dependency updates (Renovate)
New feature or improvement
ChicoryTV React SPA frontend
Claimed by an active session — do not pick up
Jellyfin tuner / IPTV integration
Excluded from automatic queue pickup; work only when explicitly selected
Adversarial review finding
Security / vulnerability fix
Milestone
No items
No Milestone
Projects
Clear projects
No projects
No Assignees
Notifications
Due Date
No due date set.
Dependencies
No dependencies set.
Reference: timothy/ersatztv#669
Reference in New Issue
Block a user
Blocking a user prevents them from interacting with repositories, such as opening or commenting on pull requests or issues. Learn more about blocking a user.
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-sourceswith its full cost list.The cost that was accepted
SongMetadata.Artists/AlbumArtistsare EF9 primitive collections: one JSON array per row in a singleTEXT/longtextcolumn. Neither provider can project the elements server-side (SQLite needs SQLAPPLY; Pomelo MySQL hasn't implemented primitive collections), so there is no server-sideSELECT DISTINCTover elements.#578's solution pre-filters on the raw JSON with a leading-wildcard
LIKE, capped at 1000 rows ordered byId:ORDER BY Id(a unique integer PK) removed the full sort and the MySQLmax_sort_lengthnondeterminism. But a leading-wildcardLIKEcannot seek an index, so everySongMetadatarow is still scanned. The cap bounds rows returned, not rows scanned.Every
artistrequest 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, andApi:RequireKeyForReads=falsemakes the endpoint anonymous. There is no request-specific rate limit.The rejected alternative
A normalized
SongArtistjoin table — the shapeMusicVideoArtistalready has. Exact and indexable forever, but #578 rejected it on blast radius:SongMetadata.ArtistsThat was the right call for a typeahead convenience inside #578's scope. It is worth revisiting on its own terms.
Done-when
api.search-field-values-sources) — "keep the scan, it's fast enough at our scale" is a legitimate outcome if backed by a measurementscripts/add-migration.sh, backfill, scanner write paths, and the Lucene indexer all updated together — no drift between the two representationsContext: #578. Related: #668 (accented values on the EF-sourced fields).
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 1000budget 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:The loop walks past non-matching candidates and stops at the first of: enough distinct exact matches for
limit; a short page (source exhausted); orListValuedMaxScannedRows = 20000candidates inListValuedBatchRows = 2000batches.So the two claims in the body above are wrong as written:
"every— the leading wildcard forces scan access, but each page stops once it fillsSongMetadatarow is still scanned"@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."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
LIKEstill 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-whenabove 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.