Search Very Slow (3 minute response)

[letterman 44]

**Confirmed on 4.11.0.8 / 1.4M items: the slow recursive queries are caused by degenerate sqlite_stat1, not by the search code. One full ANALYZE: 37 s → 3.5 s.**

@Luke – this is the same mechanism @flord22 described on page 7, reproduced on a much larger library and on the current beta, with before/after measurements on Emby's own SQL. The 4.11 search fixes were already installed and did not help.

**Setup:** Windows 11, Emby 4.11.0.8, bundled SQLite 3.53.4 (no STAT4), library.db 3.2 GB, 1,366,476 MediaItems (1.08M Audio), AncestorIds2 7.4M rows. Database settings at defaults: Analysis row limit 400, Optimize on shutdown ON.

**Symptom:** every user-scoped `Recursive=true` query ran 30–80 s or hit the 30 s timeout – `IncludeItemTypes=BoxSet` (103 results, 37,371 ms in the log), collections row on the home screen, search, `IncludeItemTypes=Playlist`.

**Cause:** sqlite_stat1 was built with `analysis_limit=400`:

```
AncestorIds2 | idxAncestorIds2_1 | 5945904 201 1     -- claims 201 items per AncestorId
```

Reality: root has 1,148,245 descendants, 47 folders have >10,000. With that estimate the planner runs the `WithAncestors` CTE inside-out – outer loop over ItemLinks2 by Type, then probing AncestorIds2 per link × 38 folders (~45M probes). With correct stats it iterates AncestorIds2 by AncestorId once and probes ItemLinks2 (~5M probes).

**Measured (same SQL from the debug log, copies of the DB):**

```
                              stats as shipped   after full ANALYZE (analysis_limit=0)
BoxSet recursive                   36.9 s                3.3 s
collections list (no LIMIT)        37.0 s                3.4 s
```

The full ANALYZE takes 5 seconds on this database.

**Applied on the live server (Emby stopped):** full ANALYZE + Optimize on shutdown OFF. Reason for the switch: `PRAGMA optimize` re-analyzes a table as soon as one of its indexes has no stat1 row (true after any update that adds an index) and always applies a row limit (min of the connection limit and SQLite's built-in 2000), so the stats degrade again on the next shutdown. After restart, from the server log: BoxSet recursive **3,535 ms**, Playlist recursive **21 ms** (was 30 s timeout), home sections 12–62 ms, search normal. No custom indexes, no hand-edited stat1.

**Two suggestions:**
1. Run a full ANALYZE (limit 0) after library scans or at least after migrations that add indexes – 5 s here – or ship SQLite with STAT4. The 400-row sampling cannot represent a hierarchy this skewed.
2. The remaining 3.4 s are structural: the query enumerates all 1.1M visible items and their links to find 103 BoxSets. Starting from `Type=9` → `ItemLinks2.LinkedId` → `EXISTS AncestorIds2` would be milliseconds.

log-essentials-2026-10-17.txt

[letterman 44]

Follow-up: the collections query rewritten – same 103 results, 3.5 s → 30 ms (verified on the real database)

Thanks for the kind replies. Good news first: I moved the same database to a much older machine (Core i3-3220 from 2012, 2 cores, 16 GB RAM) and with the statistics fixed, Emby is genuinely fast on it – playlists 20 ms, movie lists 30 ms, search 1.4 s, home screen under 2 s. Impressive for 1.4 million items on 14-year-old hardware.

The one query that doesn't scale is the collections query (IncludeItemTypes=BoxSet&Recursive=true, also the collections row on the home screen): 3.5 s on an i7, 5.7 s on the i3. It is pure single-core CPU time and the plan is already optimal, so stats, indexes and cache can't help. The query shape is the problem: it enumerates all ~1.1M visible items and their links to find 103 BoxSets.

@LukeI tested the inverted form on the real database (same WHERE semantics as the logged query, both branches of WithItemLinkItemIds preserved):

 

select ... from MediaItems A
where A.Type = 9 and (
  exists (select 1 from ItemLinks2 L cross join AncestorIds2 X
          where L.LinkedId = A.Id and L.Type = 6
            and X.ItemId = L.ItemId and X.AncestorId in (<allowed folders>))
  or
  exists (select 1 from ItemLinks2 L2 cross join ItemLinks2 L cross join AncestorIds2 X
          where L2.LinkedId = A.Id and L.LinkedId = L2.ItemId and L.Type in (7,0,1,5)
            and X.ItemId = L.ItemId and X.AncestorId in (<allowed folders>))
)

Verified with EXCEPT in both directions, for two different folder sets:

                      original   inverted   only-in-original   only-in-inverted   time (inverted)
38 folders (user 1)      103        103            0                  0            30 ms cold / 1 ms warm
 2 folders                 3          3            0                  0            16 ms

Same result set, >100× faster, no schema change, no new index. The reason: only 488 ItemLinks2 rows point at BoxSets at all, so starting from the 103 BoxSets touches a few hundred index entries instead of ~5 million.

One caveat worth knowing: without CROSS JOIN (which fixes the join order in SQLite) the planner picked AncestorIds2 (AncestorId=?) as the outer loop of the second subquery again and it was slow. So the order matters, not just the shape.

The same inversion should apply directly to the other WithItemLinkItemIds consumers (Playlist lists, prefixes, folder views – same CTE, same cost). Search (~1.4 s) and the home "Continue listening" row (~1.9 s) are different query shapes and not covered by this change, but they follow the same principle: start from the small side (the user's UserDatas rows, the FTS hits) rather than from all visible items. Happy to test a beta build against this database.

Happy to test a beta build against this database.

Edited Monday at 05:57 PM by letterman

[letterman 44]

Follow-up 2: "Continue listening" row 2.0 s → 0.35 s with the same approach, plus one caveat on where EXISTS does not help

 

Two more findings from the debug log and the CLI, same 1.4M-item database.

1. Home screen "Continue listening" (Audio next-up). Logged query: LEFT JOIN (select N.AlbumId, … from MediaItems N join UserDatas … where N.Type=11 and (Played=1 or playbackPositionTicks>0) group by N.AlbumId …) LastWatchedEpisodes on LastWatchedEpisodes.AlbumId = A.AlbumId … AND A.Id in WithAncestors. The LEFT JOIN forces SQLite to drive from mediaitems A (580k items under my two audiobook folders), although only 1,158 albums have any playback history. Since the WHERE clause already requires LastWatchedEpisodes.LastPlayedDateInt not null, that LEFT JOIN is semantically an inner JOIN. Measured on the real DB, identical 12 result rows in identical order:

as logged                                   1.0 s warm / 3.8 s cold
inner join only                             0.9 s
EXISTS ancestor condition only              2.5 s
inner join + EXISTS                         0.35 s   (plan: SCAN LastWatchedEpisodes → SEARCH A (type, AlbumId))
+ index UserDatas(userId, UserDataKeyId,
  played, playbackPositionTicks,
  LastPlayedDateInt)                        65 ms    (subquery starts from the user's 49k UserDatas rows
                                                      instead of scanning 1.08M audio rows)

Neither change alone is enough; a stat override does nothing here.

2. Caveat: EXISTS is not universally better. I tried the EXISTS shape on the per-library subview query from the log (GetSubviews_links: A.Type in (34,9,21) … WithItemLinkItemIds over one folder). There it is far worse: CTE 15 ms, EXISTS 4.4 s for the movie library, and for the 539k-item audiobook library the EXISTS form didn't finish in 5 minutes (47k artists probed one by one). So the inversion from my previous post pays off when the query spans the user's whole root (many folders, ~1.1M items), while the CTE wins for a single library. A cost decision based on the size of the ancestor set – or simply "user root → EXISTS, single folder → CTE" – would cover both.

Everything above is reproducible from the SQL in the debug log plus sqlite3 on a copy of library.db; happy to share the exact statements.

boxset-query-rewrite.sql resume-query-rewrite.sql

[kingom 16]

can confirm after updating to 4.10.2.0 search on LG App is fast as hell!

[Dan_86 10]

Confirming everything is fixed on my end too.

Thanks Luke and team