Full-text search

Search is part of an ordinary query. There is no full-text CREATE INDEX form and no shadow table: name the columns to search and the engine handles the rest.

SELECT product_id, name
FROM products
WHERE MATCH(name) AGAINST 'espresso grinder'

Several columns at once, or every column in the row:

SELECT name FROM products WHERE MATCH(name, brand) AGAINST 'copper kettle'
SELECT name FROM products WHERE MATCH(*) AGAINST 'yirgacheffe'

MATCH(*) searches every declared column, including numbers and dates through their canonical text form, so MATCH(*) AGAINST '2025' finds rows by a date column as well as by text. Internal row locators are never searchable or returned by SELECT *.

Ranking

BM25 scores a row against the same query, and is an ordinary expression — select it, order by it, filter on it:

SELECT name,
       BM25(name, brand) AGAINST 'single origin ethiopia' AS score
FROM products
WHERE MATCH(name, brand) AGAINST 'single origin ethiopia'
ORDER BY score DESC
LIMIT 20

Scores use the whole column's term statistics, so they are comparable across rows in a way a naive term count is not.

Search text can be a parameter, which keeps one statement reusable as the user types:

const sql =
  "SELECT name FROM products WHERE MATCH(name) AGAINST $1 ORDER BY BM25(name) AGAINST $1 DESC";
const result = await db.query(sql, { params: [searchText] });

Prefix matching

A trailing * matches by prefix, which is what a search-as-you-type box needs:

SELECT name FROM products WHERE MATCH(name) AGAINST 'grind*'

Multiple terms are combined; a row matches when it contains all of them.

Indexes build themselves

A MATCH on an unindexed column scans and re-verifies, which is fast enough on small tables and slow on large ones. Above a threshold — 4,096 visible rows by default — the first MATCH on an append-only column schedules a background index build and answers from the scan meanwhile. Correctness never waits on it. The build runs a few milliseconds at a time between other work, and its stored chunks grow as a large corpus needs, so it completes whatever the table's size.

To build one explicitly, before a user's first search rather than during it:

await db.buildFtsIndex("products", "name");

Later inserts land as small index deltas so commits stay proportional to the rows they add. When the tail exceeds 16 delta-bearing commits, the committing engine schedules a rebuild even if nobody searches the column again. The persistent tail and the work of the next search therefore do not grow with an all-day write session.

Full-text indexes support append-only tables; orders has keyed mutations

What it does to a term

Terms are Unicode-normalized (NFKC), lowercased, and split on non-alphanumeric boundaries. CJK runs are indexed as character bigrams, so those languages search without a dictionary. There is no stemming, no stop-word list, and no language configuration: running does not match run. That floor is deliberate — a tokenizer that guesses a language is wrong for somebody, and this one a caller can predict and pre-process around.

Tuning the threshold

const db = new MinnowDatabase(store, { ftsAutoIndexRows: 20_000 });

Raise it when tables are small and searches rare. Set it to 0 to build on the first search at any table size; set it above the largest expected table to keep searches scan-only and manage indexes yourself.