Full-Text Search (FTS5)

Prev Next

RecipeBox's search box needs to find recipes by keyword — "chicken", "quick pasta", "spicy" — across titles and descriptions. A regular index can't help here: it speeds up exact matches and sorting, not "does this text contain this word anywhere." SQLite's answer is FTS5 (Full-Text Search, version 5), a purpose-built extension for exactly this kind of search, and dramatically faster than scanning every row with LIKE '%...%'.

Creating an FTS5 Virtual Table

FTS5 tables are virtual tables — created with CREATE VIRTUAL TABLE ... USING fts5(...) — that store and index text specifically for fast keyword search, separate from your regular recipes table.

CREATE VIRTUAL TABLE recipes_fts USING fts5(title, description);

You keep it populated either manually or with triggers that copy data in whenever a recipe is inserted or updated:

-- Manually copy existing recipes into the FTS index once
INSERT INTO recipes_fts (rowid, title, description)
SELECT id, title, description FROM recipes;

-- Keep it in sync going forward with a trigger
CREATE TRIGGER trg_recipes_fts_insert
AFTER INSERT ON recipes
BEGIN
  INSERT INTO recipes_fts (rowid, title, description)
  VALUES (NEW.id, NEW.title, NEW.description);
END;

Searching with MATCH

Query an FTS5 table with the MATCH operator instead of LIKE or =. FTS5 has its own small query syntax: plain words, quoted phrases, boolean operators, and a prefix wildcard.

-- Simple keyword search
SELECT title FROM recipes_fts WHERE recipes_fts MATCH 'chicken';

-- Exact phrase (quotes matter -- order and adjacency count)
SELECT title FROM recipes_fts WHERE recipes_fts MATCH '"chicken soup"';

-- Boolean operators: AND, OR, NOT (must be uppercase)
SELECT title FROM recipes_fts WHERE recipes_fts MATCH 'chicken AND soup';
SELECT title FROM recipes_fts WHERE recipes_fts MATCH 'chicken OR turkey';
SELECT title FROM recipes_fts WHERE recipes_fts MATCH 'chicken NOT curry';

-- Prefix search: matches "spicy", "spice", "spiced", etc.
SELECT title FROM recipes_fts WHERE recipes_fts MATCH 'spic*';

To pull back full recipe rows rather than just the FTS columns, join the FTS table's rowid back to recipes.id:

-- Full recipe details for a text search, newest match first
SELECT r.id, r.title, r.category
FROM recipes r
JOIN recipes_fts ON recipes_fts.rowid = r.id
WHERE recipes_fts MATCH 'chicken AND quick*';

Why FTS5 Beats LIKE '%...%'

WHERE title LIKE '%chicken%' can never use a normal index, because the wildcard on both sides means SQLite has no fixed prefix to search from — it must scan and check every row. FTS5, by contrast, builds an inverted index (a word-to-row lookup) at write time, so a search for "chicken" jumps straight to the matching rows regardless of table size — the difference grows dramatically as RecipeBox's recipe count grows.

FTS3 and FTS4: You may see these mentioned in older references — they were SQLite's earlier full-text search modules. FTS5 is their modern successor: faster, more accurate with its ranking, and the one you should use for anything new.

Next up: Head to WAL Mode & Concurrency to let RecipeBox read and write at the same time without one blocking the other.

Prev Next