Every search re-reads the FTS schema metadata: 6 queries per search, 5 of them the same answer every time

Summary

One call to searchWithDb costs 6 database round trips, of which only 1 is the actual MATCH. The other five read schema metadata that does not change between requests, and nothing caches them. The count is identical whether the search matches anything or not, so it is a fixed floor on every keystroke-driven search, every empty query, and every bot hitting ?q=.

On our production site (Cloudflare D1, ~9,700 listings) a search that matches nothing costs 13 counted queries and ~142 ms, against 1 query for the same page filtered by place instead. D1 round trips dominate; the FTS match itself is fast.

Measured

Instrumented by wrapping db.getExecutor().executeQuery and counting, against the project's own setupTestDatabase, one collection with search enabled and 5 published entries:

MATCHING search: 6 queries, 5 results
NO-MATCH search: 6 queries, 0 results

let n = 0;
const exec = (db as any).getExecutor();
const orig = exec.executeQuery.bind(exec);
exec.executeQuery = async (...a: any[]) => { n++; return orig(...a); };
await searchWithDb(db, "farrier", { collections: ["articles"], status: "published" });

Where they go

searchWithDb (packages/core/src/search/query.ts), per call:

Checked in packages/core/src/search/fts-manager.ts: none of getSearchConfig, getCollectionsWithTitleColumn, ftsTableExists, getSearchableFields or hasTitleColumn memoises anything — each runs its own selectFrom on every call.

It also scales with collections: searching three collections pays the per-collection lookups three times, so a site-wide search is ~14 queries to return one page.

Why it matters more on D1 than on local SQLite

Every one of these is a network round trip on Cloudflare D1, not an in-process read. Five avoidable round trips is the difference between a search that feels instant and one that does not, and it is paid again for every page of results, since paging calls searchWithDb afresh.

Possible directions

Offered as starting points; the right seam is yours.

• Memoise the schema lookups for the lifetime of the request, or of the isolate. The FTS table name, its searchable fields, whether the collection has a title column and its search config all change only when the schema changes — which already goes through FTSManager, so it has a natural place to invalidate. An isolate-lifetime cache with explicit invalidation on enableSearch/disableSearch/schema change would take this from 6 queries to 1 in the steady state.
• Or fold the metadata into one query. The five reads hit _emdash_collections and sqlite_master; a single statement could return everything searchSingleCollection needs for all requested collections at once, which is one round trip regardless of how many collections are searched.
• ftsTableExists is arguably redundant with getSearchConfig(collection).enabled, which has already been checked by the time searchSingleCollection runs.

Happy to send a PR for the memoisation route if that is the direction you would take — though note I am currently at the open-PR limit, so it would follow #3193's fix.

Environment

• emdash 0.38.0, and the code paths above are unchanged on main
• Cloudflare D1 in production; counts reproduced against the repo's own in-memory SQLite test harness