Faster Partial-Word Searches in SQLite With the FTS5 Trigram Tokenizer

Searching text in a database seems simple until an application needs to find records using only part of a word. A product search may need to match phone inside Smartphone, while a customer support tool may need to find an error message using a fragment copied from a log.

SQLite supports these searches through expressions such as LIKE '%phone%'. However, this approach can become expensive when the table contains thousands or millions of records. Because the search pattern begins with a wildcard, SQLite generally cannot use an ordinary B-tree index to locate matching text efficiently.

SQLite's FTS5 extension provides another option: the trigram tokenizer. It divides text into overlapping groups of three characters and indexes those groups. This allows suitable substring searches to use a full-text index instead of repeatedly scanning every row.

The technique is useful for product catalogs, document search, log viewers, and applications that need fast partial-text matching without introducing a separate search service.

Why Ordinary LIKE Searches Can Be Slow

Consider a table that stores application error messages:

CREATE TABLE ErrorLogs (
    Id INTEGER PRIMARY KEY,
    Message TEXT NOT NULL
);

INSERT INTO ErrorLogs (Message)
VALUES
    ('Database connection failed'),
    ('Connection timeout occurred'),
    ('Unable to connect to the server'),
    ('Authentication request was rejected');

An application might search for any message containing connect:

SELECT Id, Message
FROM ErrorLogs
WHERE Message LIKE '%connect%';

This query is easy to understand, but the leading wildcard makes it difficult for a conventional index on Message to help.

For a large table, SQLite may need to inspect a substantial portion of the stored messages to determine which rows match.

Creating a regular index does not necessarily solve the problem:

CREATE INDEX idx_errorlogs_message
ON ErrorLogs(Message);

The index may help with other query patterns, such as suitable prefix searches, but it generally cannot accelerate a contains search using a leading wildcard.

The FTS5 trigram tokenizer addresses this limitation by building an index specifically designed for character sequences within text.

What Is the FTS5 Trigram Tokenizer?

FTS5 is SQLite's full-text search extension. It supports several tokenizers, each designed for a particular way of dividing text into searchable units.

The default word-oriented tokenizers are useful when users search for complete words or terms. They are less suitable when the requirement is to match an arbitrary substring inside a word.

The trigram tokenizer divides text into overlapping groups of three characters.

For example, the text:

database

produces these trigrams:

dat
ata
tab
aba
bas
ase

A search for tab can use the corresponding indexed sequence to identify candidate rows.

A search for database can use multiple trigrams to narrow the results further.

The important difference is that the index is based on character sequences rather than only complete words.

Creating an FTS5 Table With Trigram Indexing

Before using the trigram tokenizer, confirm that the SQLite build includes FTS5. Some distributions include it by default, while others require a build configuration or package that enables the extension.

Create a virtual table using the trigram tokenizer:

CREATE VIRTUAL TABLE ErrorLogsSearch
USING fts5(
    Message,
    tokenize = 'trigram'
);

This creates a full-text search table with trigram indexing enabled for the Message column.

For a small example, insert some messages:

INSERT INTO ErrorLogsSearch (Message)
VALUES
    ('Database connection failed'),
    ('Connection timeout occurred'),
    ('Unable to connect to the server'),
    ('Authentication request was rejected'),
    ('The database is temporarily unavailable');

You can now search for a substring:

SELECT rowid, Message
FROM ErrorLogsSearch
WHERE Message LIKE '%connect%';

When the query meets the trigram tokenizer's requirements, SQLite can use the FTS5 index to accelerate the substring search.

The virtual table is separate from the original ErrorLogs table. If an application already stores its data in an ordinary table, it must keep the searchable FTS5 table synchronized or use an appropriate external-content FTS5 design.

Searching With LIKE and GLOB

The trigram tokenizer is particularly useful because it can accelerate certain LIKE and GLOB expressions.

Using LIKE

A contains search can use the familiar SQL syntax:

SELECT rowid, Message
FROM ErrorLogsSearch
WHERE Message LIKE '%database%';

This is useful when the application needs to locate text containing a particular sequence of characters.

For example, a support dashboard could search error messages for timeout, while a product catalog could search descriptions for wireless.

The actual performance improvement depends on the data, query pattern, SQLite version, and whether the query can use the trigram index.

Using GLOB

SQLite's GLOB operator supports wildcard patterns with its own matching rules.

For example:

SELECT rowid, Message
FROM ErrorLogsSearch
WHERE Message GLOB '*connect*';

The trigram index may accelerate suitable patterns, but not every possible LIKE or GLOB expression can take advantage of it.

Patterns that do not contain enough useful literal characters may still require scanning candidates or fall back to less efficient processing.

Searching for Short Strings

A trigram index is based on groups of three characters. As a result, searches shorter than three characters are an important limitation.

For example:

SELECT rowid, Message
FROM ErrorLogsSearch
WHERE Message LIKE '%db%';

The search term db is shorter than a trigram. SQLite cannot use the trigram index in the same way it can for a sufficiently long substring, so the query may require a scan.

Applications should decide how to handle short searches. Possible approaches include requiring at least three characters, using a separate search strategy for short input, or accepting a scan when the table is small.

Connecting FTS5 to an Existing Table

A production application usually already has an ordinary table that stores its records. Maintaining a second copy of every searchable value manually can introduce consistency problems.

One option is to use an external-content FTS5 table.

For example:

CREATE TABLE Products (
    Id INTEGER PRIMARY KEY,
    Name TEXT NOT NULL,
    Description TEXT NOT NULL
);

CREATE VIRTUAL TABLE ProductsSearch
USING fts5(
    Name,
    Description,
    content = 'Products',
    content_rowid = 'Id',
    tokenize = 'trigram'
);

The content option tells FTS5 where the original text is stored. The content_rowid option identifies the column that maps records between the content table and the search index.

However, external-content tables do not automatically keep their indexes synchronized with changes to the original table. The application must populate the index and maintain it when records are inserted, updated, or deleted.

Populate the Existing Records

If the Products table already contains data, rebuild the FTS5 index:

INSERT INTO ProductsSearch(ProductsSearch)
VALUES ('rebuild');

This command instructs FTS5 to read the existing content and build the search index.

Keep the Index Updated

For an application that writes directly to Products, triggers can maintain the search index:

CREATE TRIGGER products_ai
AFTER INSERT ON Products
BEGIN
    INSERT INTO ProductsSearch(rowid, Name, Description)
    VALUES (new.Id, new.Name, new.Description);
END;

CREATE TRIGGER products_ad
AFTER DELETE ON Products
BEGIN
    INSERT INTO ProductsSearch(
        ProductsSearch,
        rowid,
        Name,
        Description
    )
    VALUES (
        'delete',
        old.Id,
        old.Name,
        old.Description
    );
END;

CREATE TRIGGER products_au
AFTER UPDATE ON Products
BEGIN
    INSERT INTO ProductsSearch(
        ProductsSearch,
        rowid,
        Name,
        Description
    )
    VALUES (
        'delete',
        old.Id,
        old.Name,
        old.Description
    );

    INSERT INTO ProductsSearch(rowid, Name, Description)
    VALUES (new.Id, new.Name, new.Description);
END;

These triggers update the search index when the underlying records change.

Create the triggers after the FTS5 table has been created. If the original table already contains records, rebuild the index as shown above. For bulk imports or migrations, consider how index maintenance will affect write performance.

Searching Product Records

With the index configured, an application can search product names and descriptions together:

SELECT
    p.Id,
    p.Name,
    p.Description
FROM ProductsSearch AS s
JOIN Products AS p
    ON p.Id = s.rowid
WHERE s.Name LIKE '%wireless%'
   OR s.Description LIKE '%wireless%';

This example demonstrates the general approach, but its exact execution plan should be verified with EXPLAIN QUERY PLAN. Combining multiple conditions can affect how SQLite uses the index, and an FTS5 query should be tested against the actual schema and workload.

Another approach is to search the indexed columns using a single pattern:

SELECT rowid, Name, Description
FROM ProductsSearch
WHERE Name LIKE '%wireless%'
   OR Description LIKE '%wireless%';

Choose the query structure that matches the application's needs, and measure it with representative data.

Trigram Tokenizer Options

The trigram tokenizer supports configuration options that affect matching behavior.

Option	Purpose	Consideration
`case_sensitive`	Enables case-sensitive matching when configured	Affects which query operators can use the index efficiently
`remove_diacritics`	Controls removal of diacritic marks under supported configurations	May affect matching for accented characters and language-specific text
`detail`	Controls how much token information FTS5 stores	Can affect index size and which full-text operations are available

For example, a case-sensitive trigram table can be created as follows:

CREATE VIRTUAL TABLE CaseSensitiveSearch
USING fts5(
    Content,
    tokenize = 'trigram case_sensitive 1'
);

With this configuration, matching distinguishes between uppercase and lowercase characters. Case-sensitive trigram indexes have different optimization behavior: they can support indexed GLOB patterns, while LIKE optimization is not available in the same way.

Check the documentation for the SQLite version used by the application before relying on tokenizer options or reduced-detail configurations.

Common Mistakes

Assuming FTS5 Is Available Everywhere

An application may work on a developer's machine but fail on a device or server where SQLite was built without FTS5.

Check the SQLite version and build configuration used by the actual application, not just the command-line tool installed on the development machine.

Forgetting to Maintain the Index

An external-content FTS5 table does not automatically track changes to its content table.

If the application inserts new products without updating the search index, those products may not appear in search results. Updates and deletes can also leave the index inconsistent.

Use triggers or reliable application-level index maintenance, and test all write operations.

Expecting Two-Character Searches to Be Fast

A trigram tokenizer cannot create three-character tokens from a two-character search term.

Decide whether to reject very short searches, use another search strategy, or accept the cost for smaller datasets.

Assuming Every Search Uses the Index

The presence of an FTS5 table does not guarantee that every query will use its index.

Check the query plan and benchmark the actual SQL statements. Complex expressions, short patterns, and particular tokenizer settings may limit index usage.

Performance and Storage Considerations

Trigram indexing can make substring searches substantially more efficient on suitable workloads, but it has a cost.

The index requires additional storage, and inserting or updating text requires index maintenance. Longer text fields can produce many overlapping trigrams, increasing the size of the index.

For a small table, a normal scan may be simpler and fast enough. For a large table with frequent partial-word searches, the trigram index may provide a worthwhile improvement.

Test both approaches using realistic data volumes, common search terms, and expected write activity. Measure query latency, database size, and the effect of index updates rather than relying on assumptions.

Best Practices

Confirm that the SQLite build used in production supports FTS5 and the trigram tokenizer.
Use trigram indexing when the application needs substring matching rather than only word-based search.
Decide how the application will handle search terms shorter than three characters.
Keep external-content indexes synchronized with inserts, updates, and deletes.
Use EXPLAIN QUERY PLAN to investigate whether suitable queries can use the index.
Test case sensitivity and diacritic behavior with realistic user input.
Measure index size and write overhead before deploying to large databases.
Use parameterized queries when incorporating user-provided search input into application SQL.
Test search behavior against the exact SQLite version shipped with the application.

Summary

SQLite's FTS5 trigram tokenizer provides an efficient option for searching text by character sequence. Unlike an ordinary B-tree index, it is designed to support suitable substring searches, including patterns such as LIKE '%term%'.

To use it effectively, create a trigram virtual table, choose an appropriate data synchronization strategy, and verify that the intended queries can use the index. Be especially careful with short search terms, tokenizer settings, and external-content index maintenance.

For applications that need partial-word searches without a separate search service, FTS5 trigram indexing can improve search performance while keeping the data in SQLite.