Full Text Search in SQL: FTS5, Ranking, Phrases and Speed
Full text search finds documents by the words they contain, ranks the best matches first and stays fast on large tables, which LIKE ‘%word%’ cannot do. We built a search index with SQLite FTS5, tested word, phrase, prefix, NEAR and boolean queries, stemming, BM25 ranking, snippets and highlights, hit two real gotchas, and timed it against LIKE on 200,000 documents.
- Full text search uses an inverted index from words to documents; in SQLite it is a virtual table created with
USING fts5(...). - On 200,000 documents, finding a rare word took 0.13 ms with FTS5 and 67.9 ms with
LIKE '%word%'; both found the same 40 rows. - With the porter tokenizer, a search for “run” also found “Running”, and “thread” found “Threads” and “threading”.
- An unquoted hyphen broke a query (
pytest-xdistgave “no such column: xdist”) and new rows were invisible until the index was synced.
Users expect search boxes to work like a search engine: type a few words, get the most relevant results first, even if the words appear in a different order or form. Plain SQL with LIKE '%word%' cannot do that efficiently. Full text search is a separate index type built for words, available in SQLite (FTS5), PostgreSQL (tsvector), MySQL (FULLTEXT) and SQL Server.
All tests ran on SQLite 3.50.4 with its built-in FTS5 extension, using a small set of six articles for features and a 200,000-row table for timing. We summarise PostgreSQL and MySQL syntax from their documentation without running them.
Why not just use LIKE?
- Speed: a pattern with a leading wildcard cannot use a normal index, so every row is read; see our SQL LIKE guide.
- Words, not characters:
LIKE '%gil%'also matches “agile” and “fragile”; full text search matches whole words. - No ranking: LIKE returns matches in no useful order.
- No language features: no stemming, no phrases, no boolean operators.
How full text search works
When text is indexed, a tokenizer splits it into words, lowercases them and optionally reduces them to a stem. The index then records, for each word, which documents contain it and where. A query looks up its words in that inverted index instead of scanning text, then combines and ranks the matching documents. This is the same basic structure search engines use.
Creating an FTS5 index
CREATE TABLE articles (id INTEGER PRIMARY KEY, title TEXT, body TEXT);
-- 6 short articles about Python, SQL and Git
CREATE VIRTUAL TABLE articles_fts USING fts5(
title, body,
content='articles', content_rowid='id',
tokenize='porter unicode61'
);
INSERT INTO articles_fts(rowid, title, body) SELECT id, title, body FROM articles;
An FTS5 table is a virtual table with one or more text columns. content='articles' makes it an external content index: it stores only the index and reads the original text from the articles table, so the text is not duplicated. The porter unicode61 tokenizer handles Unicode, case and diacritics, then applies English stemming.
Basic word search
SELECT rowid FROM articles_fts WHERE articles_fts MATCH 'python' ORDER BY rank;
-- [(2,), (1,), (5,)]
MATCH 'PYTHON' -- same 3 rows: matching is case-insensitive
MATCH 'title:python' -- [(1,), (2,), (5,)] only in the title column
Use the MATCH operator on the table name to search all columns, and prefix a word with a column name to search one column. Without ORDER BY rank, results come back in no particular order.
AND, OR and NOT
MATCH 'python gil' -- [(1,), (2,)] both words (AND is implicit)
MATCH 'worktree OR index' -- [(3,), (4,), (6,)] either word
MATCH 'python NOT gil' -- [(5,)] python but not gil
Several words mean all of them. OR and NOT must be written in capitals, and parentheses group expressions. This boolean syntax is what users expect from search boxes, though you will usually build it from their input rather than exposing it raw.
Phrases, prefixes and NEAR
MATCH '"cpu core"' -- [(2,), (5,)] exact phrase (stemmed)
MATCH '"python gil"' -- [] words present, but not adjacent
MATCH 'parall*' -- [(2,), (5,)] prefix: parallel
MATCH 'NEAR(full search, 2)' -- [(6,)] within 2 words of each other
Double quotes search for words in order, next to each other. Two documents contained both “python” and “gil”, but never side by side, so the phrase found nothing. A trailing asterisk matches any word starting with the prefix, useful for search-as-you-type. NEAR finds words close together without requiring exact order.
Stemming
MATCH 'run' -- [(2,), (5,)] matches "run" and "Running"
MATCH 'thread' -- [(1,)] matches "Threads" and "threading"
The porter stemmer reduces English words to a common root, so a search for “run” also found the article titled “Running Python tests”. Stemming improves recall in English, but it is language-specific and occasionally merges unrelated words; for other languages use the plain unicode61 tokenizer or a language-specific one.
Ranking with BM25
SELECT rowid, bm25(articles_fts) FROM articles_fts WHERE articles_fts MATCH 'python'
ORDER BY bm25(articles_fts);
-- [(2, -0.0), (1, -0.0), (5, -0.0)]
ORDER BY bm25(articles_fts, 10.0, 1.0) -- title matches weigh 10x
MATCH 'parallel' -- [(5,), (2,)] "parallel" in the title of 5 ranks it first
FTS5 ranks with BM25, the standard relevance formula used by search engines: rarer words count more, words that repeat in a document count more (with diminishing returns), and shorter documents get a boost. Lower (more negative) scores are better. Our first query showed a property of BM25 worth knowing: “python” appeared in half of our six documents, so its weight dropped to zero and every match scored the same. Very common words carry no ranking signal. Column weights in bm25() let a title match outrank a body match, which put the article titled “Running Python tests in parallel” first.
Snippets and highlights
SELECT snippet(articles_fts, 1, '[', ']', '~', 6) FROM articles_fts WHERE articles_fts MATCH 'wildcard';
-- 'LIKE with a leading [wildcard] scans~'
SELECT highlight(articles_fts, 0, '<b>', '</b>') FROM articles_fts WHERE articles_fts MATCH 'sql';
-- '<b>SQL</b> indexes explained', 'Searching text in <b>SQL</b>'
snippet returns a short extract around the match with markers and an ellipsis string of your choice, ready for a results page. highlight wraps every matched word in a column. If you output HTML markers, escape the rest of the text first.
Two gotchas we hit
MATCH 'pytest-xdist' -- error: no such column: xdist
MATCH '"pytest-xdist"' -- [(5,)]
INSERT INTO articles(title, body) VALUES ('New post', 'Fresh content about kubernetes');
MATCH 'kubernetes' -- [] not indexed yet
INSERT INTO articles_fts(articles_fts) VALUES ('rebuild');
MATCH 'kubernetes' -- [(7,)]
Query syntax: FTS5 treated the hyphen as an operator and “xdist” as a column name. User input must be quoted or cleaned before it reaches MATCH; wrapping each word in double quotes (and doubling any quotes inside) is a simple safe approach. Index sync: an external content index does not update itself. The new row was invisible until we ran the rebuild command. In production, keep it in sync with insert, update and delete triggers on the source table, as the SQLite documentation shows; see our SQL triggers guide.
Speed: FTS5 vs LIKE on 200,000 documents
-- 200,000 rows of 40 random technical words; 'zebra' added to every 5,000th row
SELECT id FROM big WHERE body LIKE '%zebra%'; -- 67.9 ms (40 rows)
SELECT rowid FROM big_fts WHERE big_fts MATCH 'zebra'; -- 0.13 ms (40 rows)
Both found the same 40 documents, but the full text index was about 500 times faster, because it went straight to the list of documents containing that word instead of reading 200,000 strings. The gap widens as data grows. The price is index size and slightly slower writes, which are usually acceptable for search features. For ordinary prefix lookups and exact matches, a normal B-tree index and our case-insensitive search techniques may be enough.
Full text search in PostgreSQL and MySQL
-- PostgreSQL (not run here)
SELECT id, ts_rank(to_tsvector('english', body), q) AS r
FROM articles, plainto_tsquery('english', 'python threads') q
WHERE to_tsvector('english', body) @@ q ORDER BY r DESC;
CREATE INDEX ON articles USING GIN (to_tsvector('english', body));
-- MySQL (not run here)
ALTER TABLE articles ADD FULLTEXT(title, body);
SELECT id FROM articles WHERE MATCH(title, body) AGAINST ('python threads');
PostgreSQL converts text to a tsvector, queries with tsquery and the @@ operator, ranks with ts_rank and speeds it up with a GIN index; storing the tsvector in a generated column keeps it current automatically. MySQL uses a FULLTEXT index with MATCH ... AGAINST, with natural-language and boolean modes. The concepts are identical; only the syntax differs.
Beyond keywords: semantic search
Full text search matches words, so “car” will not find “automobile” unless you add synonyms. Semantic search instead compares meaning using vector embeddings and cosine similarity. Many modern systems combine both, keyword ranking for exact terms and names, vectors for concepts, in a “hybrid” search. For dedicated needs at large scale, search engines such as Elasticsearch or OpenSearch add features like typo tolerance and faceting.
Final thoughts
Full text search turns a slow, crude LIKE scan into fast, ranked, language-aware search. In SQLite, create an FTS5 table with a suitable tokenizer, query with MATCH, order by rank or a weighted bm25(), and use snippet and highlight for results pages. Quote user input, keep external content indexes in sync with triggers, and expect very common words to add little to ranking. In our test it was about 500 times faster than LIKE for the same results.
Frequently asked questions
What is full text search in SQL?
A special index and query syntax for searching words in text. It splits text into words, stores an inverted index from words to rows, and supports ranking, phrases and stemming.
How do I do full text search in SQLite?
Create a virtual table with the fts5 module, insert your text into it, then query it with the MATCH operator and order by rank for the most relevant results first.
Why is full text search faster than LIKE?
LIKE with a leading wildcard reads every row. Full text search looks up the word in an inverted index and goes straight to the matching rows. In our test it was about 500 times faster.
What is BM25?
A relevance scoring formula that ranks documents higher when they contain rare query words, contain them more often and are shorter. SQLite FTS5 and most search engines use it.
Why does my FTS5 query fail with a no such column error?
Characters such as hyphens and colons have special meaning in FTS5 queries. Wrap user search terms in double quotes, or clean the input, before passing it to MATCH.
Keep learning
Need help with testing, WordPress or SEO?
We help teams ship better software and grow their sites. Tell us what you are working on and we will suggest a clear next step.