Adding Full‑Text Search to a Small SQLite App with FTS5

The Problem: Search in a Tiny App

Many mobile and desktop apps store documents, notes, or logs in a single SQLite database. When the user wants to find a phrase, a simple LIKE scan can be painfully slow and returns fuzzy results. The challenge is to provide a fast, natural‑language search without pulling in a heavyweight external engine.

Thesis: Use SQLite’s Built‑In FTS5 Module

SQLite’s FTS5 (Full‑Text Search version 5) offers a virtual table that automatically builds a full‑text index. It is part of the core distribution on most platforms, requires no extra binaries, and can deliver millisecond latency for thousands of rows.

Getting Started: Create an FTS5 Table

1. Ensure the SQLite build includes FTS5. On most Linux, macOS, and Windows distributions this is true by default. If you compile SQLite yourself, use the --enable-fts5 flag.

2. In the database where you want full‑text search, run:

# Run inside the SQLite CLI or any client that can execute SQL
CREATE VIRTUAL TABLE docs USING fts5(
    title,
    body,
    tokenize = "porter"
);

Explanation of the statement:

• CREATE VIRTUAL TABLE tells SQLite to create a special table that is backed by an index, not a normal heap table.
• Columns title and body are stored in the index; you can add more columns as needed.
• The tokenize = "porter" option chooses the Porter stemming tokenizer, which normalizes words like "running" and "ran" to the same stem.

Inserting Data

Insert rows normally; each INSERT automatically updates the full‑text index.

INSERT INTO docs (title, body) VALUES
    ('Hello World', 'This is the first test document.'),
    ('SQLite Guide', 'Learn how to use SQLite FTS5 for full‑text search.');

Querying with MATCH

The MATCH operator powers natural‑language search. To find all rows containing the word "test":

SELECT rowid, title, body
FROM docs
WHERE docs MATCH 'test';

Results are returned in the order of relevance by default. If you need explicit ranking, use ORDER BY rank where rank is a pseudo‑column produced by FTS5.

Measuring Impact

To see how much space the index adds, compare the database size before and after creating the FTS5 table:

PRAGMA page_count;   -- number of pages used
PRAGMA page_size;    -- size of each page in bytes

Multiplying the two gives the total disk usage. For a 10,000‑row dataset, the index typically occupies a few megabytes, which is acceptable for most embedded scenarios.

Customizing the Tokenizer

SQLite ships with several tokenizers: simple, porter, unicode61, and unicode61 with custom options. Choose one that matches your language and desired stemming behavior.

Example: case‑insensitive, no stop words:

CREATE VIRTUAL TABLE docs USING fts5(
    title,
    body,
    tokenize = "unicode61 remove_diacritics=2"
);

Here remove_diacritics=2 removes accent marks, making "café" match "cafe".

Performance & Trade‑offs

• Speed: A MATCH query on 10,000 rows typically completes in < 5 ms on modern hardware, compared to 200–300 ms for a LIKE '%test%' scan.
• Disk Footprint: The index adds overhead. Use VACUUM or PRAGMA shrink_memory; to reclaim unused pages after bulk deletes.
• Build Compatibility: If you ship a custom SQLite binary, verify sqlite3 -version and sqlite3 -help | grep fts5 to ensure the module is present.
• Complex Queries: FTS5 does not support joins directly inside the MATCH expression. For advanced filtering, combine a MATCH query with a normal WHERE clause or run a subquery.

Actionable Takeaway

To add efficient full‑text search to your small SQLite‑based app:

1. Confirm FTS5 is enabled in your build.
2. Create a virtual table with the columns you need and choose a tokenizer that fits your language.
3. Insert data as usual; the index updates automatically.
4. Query with MATCH and optionally ORDER BY rank for relevance.
5. Periodically run PRAGMA page_count; and VACUUM to keep the index lean.

With these steps, you can provide a fast, native full‑text search experience without adding external dependencies.