1. Home
  2. Blog
  3. Databases & SQL

Databases & SQL

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.

9 min read
Full text search in SQL: FTS5, ranking, phrases and speed

Key takeaways

  • 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-xdist gave “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

Work with Stack Quora

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.

Keep reading

More guides on testing, AI & modern development

Enjoyed this article? Pick a topic and jump straight into our newest tutorials.

Artificial Intelligence

Confusion Matrix Explained: Precision, Recall, F1 and Thresholds in Python

Confusion matrix explained with Python: TP, FP, FN, TN, precision, recall, F1, the accuracy trap, threshold choice by cost and…

Read article →
Software Development

API Pagination: Offset vs Cursor vs Keyset, Tested on 1 Million Rows

API pagination explained: offset vs keyset and cursor pagination timed on 1 million rows, duplicates when data changes, the ties…

Read article →
DevOps

Git Submodule Explained: Add, Clone, Update and Remove Safely

Git submodule explained: add, clone, update –init, update –remote, the detached HEAD trap, foreach and clean removal, tested step by…

Read article →
Test Automation

Pytest tmp path Explained: tmp_path, tmp_path_factory and Temp Files

Pytest tmp_path explained: per-test temporary folders, tmp_path_factory for shared files, monkeypatch.chdir, legacy tmpdir, retention and basetemp.

Read article →
Software Testing

Test Case Prioritization: 6 Techniques Compared With APFD

Test case prioritization compared: risk, coverage, cost, history and random orders scored with APFD and time to find all faults…

Read article →
Databases & SQL

Full Text Search in SQL: FTS5, Ranking, Phrases and Speed

Full text search in SQL with SQLite FTS5: MATCH queries, phrases, prefixes, NEAR, stemming, BM25 ranking, snippets, two gotchas and…

Read article →
Databases & SQL

Generated Columns in SQL: Virtual vs Stored, Indexes and Limits

Generated columns in SQL explained: virtual vs stored, automatic updates, write errors, storage cost, indexing, JSON fields and support in…

Read article →
Software Development

Object Freeze in JavaScript: Shallow vs Deep, Seal and const

Object freeze in JavaScript explained: freeze vs seal vs const, strict mode errors, shallow vs deep freeze, frozen arrays, Map…

Read article →
Software Development

Python hashlib: SHA-256, File Hashes, HMAC and Password Hashing

Python hashlib explained: SHA-256 for strings and files, algorithm speeds, why passwords need PBKDF2 or scrypt, safe verification and HMAC…

Read article →
Software Development

Python UUID Explained: uuid1, uuid4, uuid5, uuid7 and Database Keys

Python UUID explained: uuid4, uuid5, uuid1 privacy, time-ordered uuid7, parsing, storage formats and UUIDs as database keys, tested on Python…

Read article →
Artificial Intelligence

Random Forest in Python: Bagging, Feature Sampling and OOB From Scratch

Random forest in Python from scratch: bootstrap sampling, feature sampling, number of trees, out-of-bag evaluation and impurity vs permutation importance.

Read article →
Software Development

MIME Types Explained: Content-Type, nosniff and a Common Types List

MIME types explained and tested in a browser: Content-Type, nosniff, scripts and stylesheets with wrong types, a common MIME types…

Read article →
DevOps

Git Clean Explained: Remove Untracked Files Without Regret

Git clean explained with dry runs: -n, -f, -d, -x, -X, exclusions, paths, nested repositories and interactive mode, plus a…

Read article →
Test Automation

Pytest approx: Comparing Floats With rel, abs and Collections

Pytest approx explained: default tolerances, the near-zero trap, rel and abs, lists and dicts, NaN, NumPy, and how it differs…

Read article →
Software Testing

Accessibility Testing: WCAG Checklist, Automated Tools and Manual Checks

Accessibility testing explained: WCAG basics, an axe-core scan of a broken and a fixed page, what automation missed, contrast checks…

Read article →
Databases & SQL

SQL UNPIVOT: Turn Columns Into Rows, 4 Ways Tested

SQL UNPIVOT explained: turn columns into rows with UNION ALL, CROSS JOIN with CASE, JSON functions or native UNPIVOT, with…

Read article →
Software Development

Intl NumberFormat in JavaScript: Currency, Compact, Percent and Units

Intl.NumberFormat in JavaScript: locales, currency, compact notation, percent, units, rounding, formatToParts, the non-breaking space trap and formatter reuse.

Read article →
Databases & SQL

SQL EXPLAIN Plan: How to Read Query Plans and Fix Slow Queries

SQL EXPLAIN plan explained: read scans, index searches, covering indexes and temporary sorts, with before and after timings for five…

Read article →
Artificial Intelligence

Decision Tree in Python: Gini, Entropy and Overfitting From Scratch

Decision tree in Python from scratch: Gini impurity, entropy, information gain, best splits, readable rules, and how depth and leaf…

Read article →
DevOps

Git Blame Explained: Find Who Changed a Line, and Why

Git blame explained with a real repository: ignore whitespace, line ranges and functions, ignore-revs files for formatting commits, moved code…

Read article →
Test Automation

Pytest Benchmark: Measure Speed and Catch Performance Regressions

Pytest benchmark tutorial: the benchmark fixture, reading results, parametrized and pedantic benchmarks, saving baselines and failing the build on regressions.

Read article →
Software Testing

Defect Density: Formula, Worked Examples and How to Use It

Defect density explained: the formula, a five-module worked example, per KLOC vs per function point, severity weighting, defect removal efficiency…

Read article →
Databases & SQL

Case Insensitive Search in SQL: LOWER, LIKE, COLLATE and Indexes

Case insensitive search in SQL: LOWER, LIKE, COLLATE NOCASE and expression indexes compared with query plans, plus accented letters and…

Read article →
Databases & SQL

ON DELETE CASCADE in SQL: CASCADE vs SET NULL vs RESTRICT

ON DELETE CASCADE in SQL tested: CASCADE vs SET NULL vs RESTRICT, multi-level cascades, the SQLite foreign key pragma, row…

Read article →
Artificial Intelligence

Logistic Regression in Python: Sigmoid, Log Loss and Gradient Descent From Scratch

Logistic regression in Python from scratch: sigmoid, log loss, gradient descent, odds ratios, thresholds, sigmoid overflow and separable data, tested…

Read article →
DevOps

Git Worktree Explained: Work on Two Branches at Once

Git worktree explained: add, list, remove, prune, lock and detached worktrees, a hotfix workflow, and the errors you will hit,…

Read article →
Test Automation

Pytest ini Explained: addopts, markers, testpaths and Precedence

Pytest ini explained: testpaths, addopts, strict markers, xfail_strict, filterwarnings, minversion, config file precedence and command-line overrides, tested.

Read article →
Software Testing

Test Estimation Techniques: WBS, Three Point PERT and Monte Carlo

Test estimation techniques worked through: WBS, three point PERT, total uncertainty, a Monte Carlo check, percentage rules and Wideband Delphi.

Read article →
Artificial Intelligence

Linear Regression in Python: Least Squares From Scratch, With R Squared

Linear regression in Python from scratch: closed form, NumPy least squares and gradient descent, R squared, RMSE, outliers and curved…

Read article →
DevOps

Git Remote Explained: Add, Set-URL, Rename, Remove and Push URLs

Git remote explained: add, set-url, separate push URLs, rename, remove and prune, plus how to avoid leaking tokens in remote…

Read article →
Test Automation

Pytest Mock: mocker.patch, spy, side_effect and autospec Explained

Pytest mock explained: mocker.patch, return_value, side_effect, spy, autospec and PropertyMock, with tested examples for APIs, clocks and retries.

Read article →
Software Testing

System Testing Explained: Types, Process and a Real End-to-End Example

System testing explained: types, entry and exit criteria, the process, and a real end-to-end test suite run against a small…

Read article →
Artificial Intelligence

Naive Bayes Classifier Explained: Build a Spam Filter in Python

Naive Bayes classifier explained by building a spam filter in Python: Bayes rule, the naive assumption, Laplace smoothing, log probabilities…

Read article →
DevOps

Git Branch Explained: Create, Switch, Rename, Delete and Recover

Git branch explained with tested commands: list, create, switch, rename, delete, merged checks, recovering deleted branches, remote tracking and naming.

Read article →
Test Automation

Pytest raises: Testing Exceptions, match and excinfo

Pytest raises explained with tested examples: match regexes, excinfo, custom and chained exceptions, parametrized error cases, pytest.warns and common mistakes.

Read article →
Software Testing

Non Functional Testing: Types, Metrics and a Real Load Test

Non functional testing explained: 10 key types, measurable requirements, latency percentiles and what a small real load test revealed about…

Read article →