Skip to content
Sapporta

Index relational search paths

View as Markdown

Literal substring matching uses a leading wildcard. It is not SQLite full-text search, and an ordinary B-tree index does not make LIKE '%blue%' scan-free. It fits operational tables where people remember fragments and the searchable set remains deliberate.

Index each child correlation

Section titled “Index each child correlation”

The relationship lookup inside a correlated EXISTS does have a useful index path. Put the child foreign key first:

export const bookCodesTable = sqliteTable(
  "book_codes",
  {
    // Columns omitted for focus.
  },
  (table) => [index("book_codes_book_id_idx").on(table.book_id)],
);

Sapporta logs a startup warning when a child foreign key used by relational search is not the first column of an index. The warning does not block startup, but it identifies the correlation the database will run for every candidate parent. Inspect EXPLAIN QUERY PLAN with representative data before broadening a search tree.

Changing only meta.search does not require a migration. Adding or changing the Drizzle index does. Generate, review, and apply that migration through the normal schema workflow.

For a large document corpus, language-aware ranking, stemming, or tokenized matching, use an application search endpoint backed by FTS or an external index. Generated table search intentionally remains literal, relational, and attached to ordinary table reads.

Section titled “Related documentation”