Skip to content
Tutorials Center
My learning

Topics

Lesson 22 of 33 · Beyond Basics

Full-Text Search with FTS5

Build fast word search in SQLite with FTS5: virtual tables, MATCH queries, bm25 ranking, snippets, tokenizers and external content tables, with live FTS4 demos.

Searching text with LIKE '%word%' has two problems. It reads every row of the table, so it gets slower as the data grows. And it has no idea of what a word is: LIKE '%car%' also finds "scarce" and "carpet", and it cannot rank the best match first. A full-text search index fixes both. It splits text into words (tokens), stores which documents contain each word, and answers "which rows mention these words?" in milliseconds, with relevance ranking and highlighted excerpts.

SQLite ships full-text search as built-in extensions. The current one is FTS5; its older sibling is FTS4 (and FTS3). The site's in-browser SQLite does not include FTS5, so the FTS5 code below is shown read-only, to copy into the sqlite3 shell or your application. But that browser build does include FTS4, which uses the same MATCH idea, so the live examples here use FTS4 as an honest stand-in for the shared concepts. Where the two differ, the lesson says so.

Why not LIKE?

Scans every row Slow on big tables.
Matches fragments Searching car also finds scarce.
No ranking All matches look equal.
1 / 2

Creating an FTS5 table

An FTS5 table is a virtual table: you create it with CREATE VIRTUAL TABLE ... USING fts5(...) and list the columns you want to be searchable. There are no column types, because every column holds text.

SQL
CREATE VIRTUAL TABLE docs USING fts5(title, body);

INSERT INTO docs (title, body) VALUES
  ('Intro to SQLite', 'SQLite is a small, fast database engine in a single file.'),
  ('Indexes',         'An index speeds up searches. Indexes cost space.'),
  ('Full-text search','Full-text search finds words quickly.');

You insert, update and delete in it like any table. Queries use the special MATCH operator, with the table's own name on the left:

SQL
SELECT title FROM docs WHERE docs MATCH 'search';

This returns every row where any column contains the token search. To restrict the match to one column, name it in the query: docs MATCH 'title : search'. An FTS5 table also has a hidden rowid, just like an ordinary table.

Here is the same idea running live on FTS4. Notice that the MATCH queries look the same; the porter tokenizer (see below) also makes "search" match "searches" and "searching".

SQL MATCH on an FTS4 table

Press Run (or Ctrl + Enter) to see the result here.

The first query finds the "Indexes" row (it contains "searches") and the "Full-text search" row. The second finds "Intro to SQLite" and "Backups", the two rows that contain both words. The LIKE query happens to return the same two rows as the first query here, but it will not match "searching" without a pattern for it, it does a full scan, and it can match fragments inside other words.

The query language

The text after MATCH is a small query language, not plain SQL. These forms work in FTS5, and all but the last two also in FTS4 as written:

Query Matches
database file rows containing both words (implicit AND)
database OR index either word
file NOT backup file but not backup
"full text" the exact phrase, words next to each other
data* any word starting with data (prefix search)
title : index index only in the title column (FTS4 writes title:index)
NEAR(sqlite fast, 5) both words within 5 tokens (FTS4: sqlite NEAR/5 fast)
^sqlite the token must be the first one in its column (FTS5 only)

Operators AND, OR and NOT must be in capitals, and parentheses group them. Because the argument is a query language, user input needs care (see the end of this lesson).

SQL Phrases, prefixes, columns and NEAR

Press Run (or Ctrl + Enter) to see the result here.

You get one row from each of phrase, column, not and near: "Full-text search", "Indexes", "Intro to SQLite" and "Intro to SQLite". The prefix query returns "Intro to SQLite" and "Backups" (both contain database), and the or query returns three rows: "Intro to SQLite", "Indexes" and "Backups". The NOT query keeps only the intro row, since "Backups" contains the stem of backup. A phrase in quotes matches the words next to each other, even though the tokenizer treats the hyphen in "Full-text" as a separator.

Ranking with bm25 and ORDER BY rank

Finding rows is half the job; the other half is putting the best ones first. FTS5 has this built in. It scores each match with the bm25 algorithm, which rewards documents where the search words are frequent and rare in the rest of the collection, and it exposes the score as a hidden column called rank. Lower (more negative) is better, so you sort ascending:

SQL
SELECT title, rank
FROM docs
WHERE docs MATCH 'database search'
ORDER BY rank;

You can weight columns, so a hit in the title counts more than a hit in the body, with the bm25() function. Its arguments after the table name are one weight per column:

SQL
SELECT title, bm25(docs, 10.0, 1.0) AS score
FROM docs
WHERE docs MATCH 'sqlite'
ORDER BY score;

FTS4 does not have rank or bm25. It exposes raw counters through matchinfo() and expects you to compute a score yourself. That is one of the main reasons to prefer FTS5 for new work.

Snippets and highlighting

Search results look far better with the matching words marked. FTS5 gives two auxiliary functions:

SQL
SELECT title,
       snippet(docs, 1, '<mark>', '</mark>', '...', 12) AS excerpt,
       highlight(docs, 0, '<mark>', '</mark>')           AS marked_title
FROM docs
WHERE docs MATCH 'search';

snippet() takes the table, the column number (0 is the first), the opening and closing marker, the text to show where the excerpt is cut, and the maximum number of tokens, and returns a short passage around the best match. highlight() returns the whole column text with each match wrapped in markers. If the text goes into a web page, remember to escape the stored text yourself first, since the markers are inserted without escaping.

FTS4 has a snippet() too, with a slightly different argument order:

SQL snippet() in FTS4

Press Run (or Ctrl + Enter) to see the result here.

Each excerpt is a short passage with the matched word in square brackets: ...index speeds up [searches]. Indexes cost... for "Indexes", and ...text [search] finds words quickly. [Searching]... for the other. Both stems match, so "searches", "search" and "Searching" are all marked.

Tokenizers

The tokenizer decides how text is cut into words and how words are normalized. You choose it per table:

SQL
CREATE VIRTUAL TABLE docs USING fts5(title, body, tokenize = 'porter unicode61');
Tokenizer What it does
unicode61 The default. Splits on anything that is not a letter or digit, is case-insensitive, and removes accents by default (cafe finds Café)
ascii Like unicode61, but case folding covers ASCII only and every non-ASCII character counts as part of a word
porter A stemmer wrapped around another tokenizer: running, runs and run become one token. English only
trigram Indexes every three-character sequence, so you can find any substring (like LIKE '%ere%', but indexed) and match case-insensitively. Larger index

Changing the tokenizer changes what matches, so pick it before you load data. The difference is easy to see in FTS4, which has simple, porter and unicode61 tokenizers:

SQL Tokenizers change what matches

Press Run (or Ctrl + Enter) to see the result here.

With the simple tokenizer, search finds nothing (0) because only the exact word searches is in the index, which the second query finds (1). With porter, search finds the row (1). With unicode61, cafe matches Café (1) because accents are removed.

External-content tables: keep your data in one place

By default an FTS5 table stores its own copy of the text. If your text already lives in an ordinary table, you can avoid duplicating it: declare an external content table that holds only the search index and reads the text back from the real table:

SQL
CREATE TABLE articles (id INTEGER PRIMARY KEY, title TEXT, body TEXT);

CREATE VIRTUAL TABLE articles_fts USING fts5(
  title, body,
  content = 'articles', content_rowid = 'id'
);

The catch: SQLite does not keep the index in step on its own. You add three triggers (see Views and Triggers). For deletes and updates, FTS5 needs to be told the old values, using a special 'delete' command:

SQL
CREATE TRIGGER articles_ai AFTER INSERT ON articles BEGIN
  INSERT INTO articles_fts (rowid, title, body) VALUES (new.id, new.title, new.body);
END;

CREATE TRIGGER articles_ad AFTER DELETE ON articles BEGIN
  INSERT INTO articles_fts (articles_fts, rowid, title, body)
  VALUES ('delete', old.id, old.title, old.body);
END;

CREATE TRIGGER articles_au AFTER UPDATE ON articles BEGIN
  INSERT INTO articles_fts (articles_fts, rowid, title, body)
  VALUES ('delete', old.id, old.title, old.body);
  INSERT INTO articles_fts (rowid, title, body) VALUES (new.id, new.title, new.body);
END;

Search then joins back to the real table: SELECT a.* FROM articles_fts JOIN articles a ON a.id = articles_fts.rowid WHERE articles_fts MATCH 'word' ORDER BY rank. FTS4 supports the same pattern with content="articles", and a rebuild command that indexes everything already in the real table:

SQL An external-content index (FTS4)

Press Run (or Ctrl + Enter) to see the result here.

The search finds articles 1 and 2 and returns their columns from the real articles table, while the text is stored only once. For maintenance in FTS5, the same special-insert style gives you INSERT INTO docs(docs) VALUES('optimize') (merge index segments), 'rebuild' (re-index from the content table) and 'integrity-check'.

Using FTS5 safely and well

  • User input is a query language. A search box value such as c++, AND or "unclosed makes MATCH fail with a syntax error. A safe default is to turn each word into a quoted phrase: split the input on spaces, wrap each word in double quotes (doubling any quote inside it), and join with spaces. Pass the result as a parameter, never pasted into SQL (Parameters and SQL Injection).
  • Do not combine MATCH with an ordinary filter and expect it to be free. A condition such as WHERE docs MATCH 'x' AND year = 2024 works, but the full-text part runs first and the rest filters its results.
  • Prefix search (data*) is fast for longer prefixes. To make very short prefixes quick, create the table with prefix = '2 3'.
  • Not every build has FTS5. Most SQLite distributions do, including the ones in Python and most desktop tools, but some minimal builds (and this site's browser build) do not. Check with SELECT sqlite_compileoption_used('ENABLE_FTS5') (it returns 1 when the module is compiled in), or just try CREATE VIRTUAL TABLE.
  • The index takes space. Depending on the text and tokenizer it can be a sizeable fraction of the size of the text itself (a trigram index is much larger), so check the file size on realistic data.

Check your understanding

Quick check · 5 questions

of 5 answered You scored / 5 · perfect!

  1. 1. Why is full-text search faster than LIKE '%word%' on a large table?

    The FTS index maps each token to the rows that contain it, so a search jumps straight to the candidates instead of reading all the text.
  2. 2. In FTS5, how do you get the best matches first?

    FTS5 exposes a hidden rank column computed with bm25; lower (more negative) scores are better, so an ascending sort puts the best result first.
  3. 3. Which tokenizer lets a search for run also find running in English text?

    Porter is a stemmer that reduces words to their root form, so related word forms share one token.
  4. 4. What is the point of an external-content FTS table?

    The FTS table keeps only the index and reads text from your ordinary table; you must tell it about every insert, update and delete.
  5. 5. A user types c++ tutorial into your search box. What is the safest way to build the MATCH query?

    Raw input can contain query operators and punctuation that cause syntax errors. Quoting each word makes it a plain phrase, and a bound parameter avoids SQL injection.

Summary

  • Full-text search indexes words, not characters, so queries are fast, understand word boundaries and can be ranked.
  • FTS5 tables are created with CREATE VIRTUAL TABLE ... USING fts5(columns) and queried with table MATCH '...'; phrases, prefixes, AND/OR/NOT, column filters and NEAR are part of the query language.
  • ORDER BY rank (bm25, lower is better), bm25() weights, snippet() and highlight() handle ranking and presentation.
  • The tokenizer (unicode61, ascii, porter, trigram) decides what counts as a match; choose it before loading data.
  • External-content tables avoid duplicating text but need triggers; treat user input as a query language and quote it.
  • The site's browser engine has FTS4 but not FTS5, so the live examples use FTS4 for the shared ideas.

Next: Concurrency, Locking and WAL explains how SQLite lets several connections share one file safely.