Full-Text Search in SQLite with FTS5

You're building a search box. Your first instinct is WHERE title LIKE '%keyword%'. That works—until you have 100,000 rows and your users start complaining about slow results, or until they type "run" and expect it to match "running" too.

The professional answer is Full-Text Search (FTS), and SQLite has a powerful built-in engine for it: FTS5.

In this post, you'll learn how FTS5 works, how to create and populate FTS5 virtual tables, how to write MATCH queries, and how to rank results by relevance using BM25.

Why Not Just Use LIKE?

LIKE '%keyword%' has three fatal flaws:

1. It's slow: A leading % forces a full table scan—no index can help.
2. It's literal: LIKE '%run%' won't match "running" or "ran".
3. No relevance: Every match is equal. You can't order by "how good" a match is.

FTS5 solves all three by building an inverted index—a lookup table that maps every word to the list of rows that contain it. This makes lookups O(log n) instead of O(n), supports stemming via tokenizers, and powers relevance scoring with the BM25 algorithm.

Creating an FTS5 Virtual Table

FTS5 is a "virtual table"—it looks like a regular table but is powered by the FTS5 engine underneath.

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

The content= and content_rowid= options create a content table—FTS5 stores only the index, while the actual content lives in your regular articles table. This avoids data duplication.

For simplicity in this tutorial, we'll use a standalone FTS5 table that stores content directly:

Basic MATCH Queries

FTS5 uses the MATCH operator instead of LIKE:

Notice it found all three: the article about indexes, the one that mentions indexes as a cause of slowness, and the indexing best practices article.

Search Operators

FTS5 supports a rich query syntax:

Syntax	Meaning	Example
word	Simple term	index
word1 word2	Both terms (implicit AND)	sql index
word1 OR word2	Either term	index OR join
"phrase here"	Exact phrase	"execution plan"
word*	Prefix match	optim* matches "optimize", "optimizing"
col:word	Search specific column	title:index
NOT word	Exclude term	sql NOT join

Ranking by Relevance with BM25

Returning results is useful. Returning them in the right order is what makes search feel smart. FTS5 exposes the bm25() function, which returns a relevance score based on the BM25 algorithm—the same algorithm behind many search engines.

Important: In SQLite's FTS5, bm25() returns a negative number. More relevant = more negative. So we ORDER BY bm25(...) ascending (smallest value first) to get the most relevant results on top.

The article specifically about indexing best practices scores highest (most negative), followed by the intro to indexes article—which mentions "index" three times in a short body. BM25 rewards term frequency and document-level density.

Joining FTS5 Results with Your Main Table

In production, you'll typically keep the original data in a regular table and use an FTS5 content table for searching. The join is simple:

-- Regular table with full columns
CREATE TABLE articles (
  id      INTEGER PRIMARY KEY,
  title   TEXT,
  body    TEXT,
  author  TEXT,
  pub_date TEXT
);

-- FTS5 index pointing to articles
CREATE VIRTUAL TABLE articles_fts USING fts5(
  title,
  body,
  content='articles',
  content_rowid='id'
);

-- Populate (must be done after inserts into articles)
INSERT INTO articles_fts(articles_fts) VALUES('rebuild');

-- Search query
SELECT a.id, a.title, a.author, a.pub_date
FROM articles_fts f
JOIN articles a ON a.id = f.rowid
WHERE f MATCH 'query terms'
ORDER BY bm25(f);

The content='articles' directive tells FTS5 to read content from the articles table when needed. You do need to manually keep the FTS index in sync with the main table using triggers or by calling INSERT INTO articles_fts(articles_fts) VALUES('rebuild') periodically.

FTS5 Snippets: Highlighting Matches

One killer feature of FTS5 is the snippet() function, which extracts a short excerpt from each result with matching terms highlighted. This is exactly what search engines show under each result title.

The snippet() function takes: table name, column index (0-based), open tag, close tag, gap text, and token count. The output is ready to render directly as HTML in your search results page.

FTS5 vs. LIKE: A Quick Comparison

Feature	LIKE '%..%'	FTS5 MATCH
Speed	Slow (full scan)	Fast (inverted index)
Phrase search	No	Yes (quoted phrases)
Prefix search	Partial (LIKE 'abc%')	Yes (abc*)
Relevance ranking	No	Yes (BM25)
Snippet extraction	No	Yes (snippet())
Setup complexity	None	One CREATE VIRTUAL TABLE

The only reason to stick with LIKE is when you're querying a small table and can't add a virtual table. For anything production-grade, FTS5 is the right tool.

Best Practices

• Rebuild the index after bulk inserts: INSERT INTO fts_table(fts_table) VALUES('rebuild').
• Use triggers to keep the FTS index in sync with content table changes in real time.
• Avoid stopwords manually: FTS5's default tokenizer doesn't remove stopwords—consider omitting very common words from your query logic if precision matters.
• Combine with SQL filters: WHERE fts_table MATCH 'keyword' AND pub_date > '2025-01-01'—filter on structured columns alongside FTS for best performance.

Conclusion

SQLite FTS5 turns your database into a capable search engine without any external dependencies. With MATCH queries, BM25 relevance scoring, and the snippet() function, you can build search experiences that rival standalone search services—entirely in SQL.

Start by creating a virtual table for your text-heavy columns. Migrate your LIKE queries to MATCH. Your users—and your server—will notice the difference.