SQLite Full Text Search

Full Text Search (FTS) lets you search inside text content quickly and naturally — finding documents that contain specific words, phrases, or combinations, without writing complex LIKE patterns. SQLite provides FTS through a virtual table module called FTS5.

LIKE vs FTS — The Difference

TABLE: articles (1 million rows, each with a long "body" TEXT)

LIKE approach:
  SELECT title FROM articles WHERE body LIKE '%database optimization%';
  → Full table scan, checks every character of every row
  → Slow on large datasets
  → Cannot rank by relevance

FTS approach:
  SELECT title FROM articles WHERE articles MATCH 'database optimization';
  → Uses pre-built inverted index
  → Fast even on millions of rows
  → Returns relevance scores for ranking

Creating an FTS5 Table

-- FTS5 virtual table for article search
CREATE VIRTUAL TABLE articles USING fts5(
  title,
  body,
  author
);

-- Insert data
INSERT INTO articles VALUES
  ('SQLite Internals', 'SQLite stores data in a B-tree structure...', 'Alice'),
  ('Database Optimization', 'Index selection is key to fast queries...', 'Bob'),
  ('Python and SQLite', 'Python includes sqlite3 module by default...', 'Carol'),
  ('SQL for Beginners', 'SQL stands for Structured Query Language...', 'Dan');

Basic Full Text Search

-- Find articles containing "SQLite"
SELECT title, author FROM articles WHERE articles MATCH 'SQLite';

title               │ author
────────────────────┼───────
SQLite Internals    │ Alice
Python and SQLite   │ Carol

Search Operators

OPERATOR    MEANING                  EXAMPLE
────────────────────────────────────────────────────────────────
word        Contains this word       MATCH 'database'
"phrase"    Exact phrase             MATCH '"fast queries"'
word1 word2 Both words present       MATCH 'SQLite index'
word1 OR word2  Either word          MATCH 'SQLite OR PostgreSQL'
NOT word    Exclude this word        MATCH 'database NOT MySQL'
word*       Prefix match             MATCH 'optim*'
col:word    Search specific column   MATCH 'title:SQLite'

Phrase Search

-- Find exact phrase "B-tree structure"
SELECT title FROM articles WHERE articles MATCH '"B-tree structure"';

title
─────────────────
SQLite Internals

Prefix Search

-- Find words starting with "optim" (optimize, optimization, optimal...)
SELECT title FROM articles WHERE articles MATCH 'optim*';

title
───────────────────────
Database Optimization

Column-Specific Search

-- Search only in the title column
SELECT title FROM articles WHERE articles MATCH 'title:SQLite';

title
────────────────────
SQLite Internals
Python and SQLite

-- Search only in author column
SELECT title FROM articles WHERE articles MATCH 'author:Alice';

title
────────────────
SQLite Internals

Relevance Ranking with bm25()

-- Return results ranked by relevance (most relevant first)
SELECT title, bm25(articles) AS score
FROM articles
WHERE articles MATCH 'SQLite'
ORDER BY bm25(articles);   ← lower (more negative) = more relevant

title               │ score
────────────────────┼────────────
SQLite Internals    │ -1.32...    ← most relevant
Python and SQLite   │ -0.44...    ← less relevant

Highlighting Matched Terms

-- Highlight matched words in the body snippet
SELECT highlight(articles, 1, '[', ']') AS highlighted_body
FROM articles
WHERE articles MATCH 'SQLite'
LIMIT 1;

highlighted_body:
[SQLite] stores data in a B-tree structure...
↑ matched word is wrapped in [ ] brackets

Snippet Function

-- Return a short excerpt around the match
SELECT snippet(articles, 1, '<b>', '</b>', '...', 10) AS excerpt
FROM articles
WHERE articles MATCH 'index';

excerpt:
...Index selection is key to <b>fast</b> queries...

Using FTS with a Regular Table

A common pattern stores regular data in a normal table and creates an FTS table as a search index, linked by rowid.

-- Real data table
CREATE TABLE posts (
  id      INTEGER PRIMARY KEY,
  title   TEXT,
  content TEXT,
  author  TEXT,
  created TEXT
);

-- FTS index table
CREATE VIRTUAL TABLE posts_fts USING fts5(
  title, content, content='posts', content_rowid='id'
);

-- Keep FTS in sync with triggers
CREATE TRIGGER posts_ai AFTER INSERT ON posts BEGIN
  INSERT INTO posts_fts(rowid, title, content)
  VALUES (new.id, new.title, new.content);
END;

Key Takeaways

• FTS5 creates a virtual table with a built-in inverted word index
• Use MATCH instead of LIKE for text search — much faster on large data
• Search operators: plain word, "phrase", prefix with *, OR, NOT, column-specific with col:
• bm25() returns a relevance score for ranking search results
• highlight() and snippet() format matched text for display