FTS5 Fundamentals: Full-Text Tables, MATCH, and Tokenization

Learning outcomes

Full-text search is not “LIKE, but faster.” FTS5 tokenizes documents, builds an inverted index from tokens to matching rows, and evaluates a dedicated query language. The lesson starts with a small searchable FieldNotes document set so every MATCH result can be reasoned about by hand.

The application problem: searchable maintenance knowledge

FieldNotes has short maintenance notes and operating articles. A user wants “cooling pump vibration,” phrase searches such as “bearing inspection,” and prefixes such as vibrat*. A query like WHERE body LIKE '%vibration%' can find a substring, but a normal B-tree index generally cannot turn an arbitrary leading wildcard into a full-text inverted index. It also lacks FTS concepts such as token phrases, document relevance, NEAR groups, and tokenizer configuration.

Confirm FTS5 exists before creating a table

SELECT sqlite_version();SELECT name FROM pragma_module_list WHERE name='fts5';SELECT compile_optionsFROM pragma_compile_optionsWHERE compile_options LIKE '%FTS5%';

Modern source builds commonly include FTS5, but an embedded host can omit it. If the module is missing, treat the lab as conceptual until you have an approved SQLite build; do not substitute untrusted binaries.

Create a small self-contained FTS5 table

The default FTS5 mode stores the supplied content as well as the full-text index. For this first lesson that simplicity is useful. We explicitly assign rowids so the search row identity remains stable and can later correspond to an application document id.

DROP TABLE IF EXISTS search_fts;CREATE VIRTUAL TABLE search_fts USING fts5(  title,  body,  tokenize='unicode61');INSERT INTO search_fts(rowid,title,body) VALUES(101,'Cooling pump vibration','Pump 7 shows rising vibration near the drive-end bearing.'),(102,'Bearing inspection','Inspect the cooling-water pump bearing and record vibration trend.'),(103,'Fan inspection due','Exhaust fan 14 requires belt and bearing inspection.'),(104,'Sensor calibration','Vibration sensor 3 calibration passed; verify MQTT telemetry.'),(105,'Pump seal leak','Cooling pump seal shows a small leak but vibration remains normal.');SELECT rowid,title FROM search_fts ORDER BY rowid;

The ordinary-looking rows are backed by FTS5's structures. Do not create a separate B-tree index on body; the virtual-table module owns its search index.

MATCH searches tokens, not arbitrary character fragments

-- A token query.SELECT rowid,title FROM search_ftsWHERE search_fts MATCH 'vibration'ORDER BY rowid;-- A phrase: adjacent tokens in this order.SELECT rowid,title FROM search_ftsWHERE search_fts MATCH '"bearing inspection"'ORDER BY rowid;-- A prefix token query.SELECT rowid,title FROM search_ftsWHERE search_fts MATCH 'vibrat*'ORDER BY rowid;

With the default tokenizer, punctuation and whitespace help define tokens. A phrase is about token adjacency after tokenization—not a byte-for-byte substring contract. If an application needs arbitrary substring search, investigate an intentionally configured trigram tokenizer or another search design rather than assuming the default FTS index does it.

Boolean expressions and NEAR groups express search intent

FTS5 supports explicit AND, OR, and NOT, plus documented NEAR groups. Keep the syntax readable and parameterize the query string in application code; a search expression is still user-controlled input even though it is not ordinary SQL syntax.

SELECT rowid,title FROM search_ftsWHERE search_fts MATCH 'pump AND vibration'ORDER BY rowid;SELECT rowid,title FROM search_ftsWHERE search_fts MATCH 'pump NOT leak'ORDER BY rowid;SELECT rowid,title FROM search_ftsWHERE search_fts MATCH 'NEAR(pump bearing, 8)'ORDER BY rowid;SELECT rowid,title FROM search_ftsWHERE search_fts MATCH 'sensor OR fan'ORDER BY rowid;

NEAR operates on token positions. It is useful when proximity carries meaning, but it is not a substitute for a domain-specific parser or semantic search model.

The tokenizer is part of your schema contract

FTS5's default unicode61 tokenizer follows Unicode 6.1 character categories. It performs case folding according to those rules and, by default, removes diacritics from Latin-script characters in the documented way. That is useful general-purpose behavior, but it is not a promise of language-aware stemming.

Prefix indexes are optional acceleration, not the meaning of *

FTS5 can be configured with prefix indexes such as prefix='2 3' to accelerate some prefix queries. The * query operator still defines prefix semantics even without that configuration; the extra prefix indexes trade database size/write work for potentially faster prefix searches.

CREATE VIRTUAL TABLE search_prefix USING fts5(  title,  body,  tokenize='unicode61',  prefix='2 3');

Do not add prefix indexes “just in case.” Measure your actual query mix, corpus size, ingestion rate, and database growth.

Content-storage choices exist because indexing and content are separate concerns

The default FTS5 table stores content and index data together. An external-content FTS5 table stores the full-text index while fetching displayed columns from another table; keeping the two synchronized becomes your responsibility. A contentless design stores no retrievable content columns in the FTS table. Newer SQLite versions also support contentless-delete, which improves update/delete ergonomics for that specialized mode. Lesson 3 develops these tradeoffs only after basic MATCH behavior is solid.

Compare evidence: ordinary substring scan versus FTS MATCH

CREATE TABLE ordinary_docs(  doc_id INTEGER PRIMARY KEY,  title TEXT NOT NULL,  body TEXT NOT NULL);INSERT INTO ordinary_docsSELECT rowid,title,body FROM search_fts;EXPLAIN QUERY PLANSELECT doc_id,title FROM ordinary_docsWHERE body LIKE '%vibration%';EXPLAIN QUERY PLANSELECT rowid,title FROM search_ftsWHERE search_fts MATCH 'vibration';

On a tiny five-row dataset timing is meaningless; the useful observation is that the first query is an ordinary-table pattern scan while the second delegates a MATCH constraint to the FTS5 virtual table. Scale tests belong on realistic corpora.

Lab: build a search-query matrix

Predict results before executing. Record which rows match each query and explain the result in terms of tokens, phrase order, prefix semantics, or Boolean logic.

SELECT 'term' AS kind,rowid,title FROM search_fts WHERE search_fts MATCH 'bearing';SELECT 'phrase',rowid,title FROM search_fts WHERE search_fts MATCH '"cooling pump"';SELECT 'prefix',rowid,title FROM search_fts WHERE search_fts MATCH 'calibrat*';SELECT 'boolean',rowid,title FROM search_fts WHERE search_fts MATCH 'pump AND normal';SELECT 'near',rowid,title FROM search_fts WHERE search_fts MATCH 'NEAR(sensor telemetry, 5)';

Then add one row containing punctuation, mixed case, and a Latin diacritic relevant to your application and document how your chosen tokenizer treats it. Search behavior should be tested as deliberately as schema constraints.

Verification checkpoint

Production judgment and bridge

FTS5 makes search semantics explicit: tokens, phrases, prefixes, and tokenizer policy. Lesson 3 adds the application-facing pieces—ranking, excerpts, external-content lifecycle, integrity checks, and index maintenance.

Authoritative references

• SQLite FTS5 Extension
• SQLite Virtual Table Mechanism
• SQLite EXPLAIN QUERY PLAN