Admin content-list search filter does an unindexable full-table scan

[khoinguyenpham04]

This Roadmap discussion mirrors #1517: Admin content-list search filter does an unindexable full-table scan.

Use this discussion to upvote the roadmap item and discuss priority, use cases, and product feedback. Keep implementation tracking, reproduction details, and PR-specific feedback on the source issue.

• Source issue: #1517
• Status: Open
• Labels: None
• Original author: MA2153

Original Issue

Description

The admin content-list q filter (the search box on a collection list view) runs a substring LIKE that cannot use any index, so it scans every non-deleted row in the locale on each search — including just to compute the result count. On large collections and on D1 (where this routes to the primary and reads every row) the rows-read / rows-returned ratio is terrible.

The generated count query looks like:

select count("id") as "count"
from "ec_resources"
where "deleted_at" is null
  and "locale" = ?
  and (lower("slug") like lower(?) escape '\'
       or lower("title") like lower(?) escape '\')

Two things make it unindexable by construction:

1. Leading % wildcard — pattern = %${term}%``, a substring match that no B-tree index can serve.
2. lower(col) wraps the column, defeating the index on slug/title regardless of the wildcard.

Notably, EmDash already ships an FTS layer (fts5 + bm25, packages/core/src/search/) that the content-list filter does not use. For collections with search_config.enabled, the list filter could be backed by FTS and fall back to LIKE otherwise.

Source of the query: ContentRepository.applySearchFilter / count:

• https://github.com/emdash-cms/emdash/blob/main/packages/core/src/database/repositories/content.ts#L786-L805
• https://github.com/emdash-cms/emdash/blob/main/packages/core/src/database/repositories/content.ts#L835-L860

Steps to reproduce

1. Create a collection with a non-trivial number of rows (e.g. resources).
2. Open the collection list view in the admin and type a search term.
3. Inspect the issued SQL / D1 analytics: the count(...) and find(...) queries scan the full non-deleted set for the locale; rows-read greatly exceeds rows-returned.

Environment

• emdash version: 0.20.0
• Runtime: Cloudflare Workers (D1) — also applies to Node/SQLite

Logs / error output

No error — this is a performance/scalability issue (full scan, poor rows-read/returned ratio), not a crash.

Possible direction

• Back the content-list filter with the existing FTS5 index when the collection has search enabled; fall back to LIKE otherwise.
• Alternatively, drop or cap the redundant full-scan count() on search (e.g. LIMIT n + "n+").