Fast-text search in SQLite with fts5

Full-text-search can be added to SQLite by using the fts5 extension.

Assuming we have a table like so:

CREATE TABLE tickers (
    id INTEGER PRIMARY KEY,
    symbol TEXT UNIQUE,
    name TEXT,
    clean_name TEXT,
    description TEXT,
    sector TEXT,
    industry TEXT,
    country TEXT,
    tags TEXT,
    created_at TEXT DEFAULT CURRENT_TIMESTAMP,
    updated_at TEXT	DEFAULT CURRENT_TIMESTAMP
);

To enable the full-text-search we need to create a virtual table using the FTS5 extension:

CREATE VIRTUAL TABLE fts_tickers USING FTS5(
    -- let's add the id for reference without indexing it for fast-text-search
    id UNINDEXED, 
    symbol, 
    name, 
    clean_name, 
    description, 
    sector, 
    industry, 
    country,
    tags
)

We can then index the data on the full-text-search table by using triggers or for example inserting from the source table directly:

INSERT INTO fts_tickers(
    name, 
    symbol, 
    clean_name, 
    sector, 
    industry, 
    country, 
    description, 
    tags
) SELECT id, name, symbol, clean_name, sector, industry, country, description, tags FROM tickers

Querying can then be performed by matching all the columns indexed:

SELECT * FROM fts_tickers WHERE fts_tickers MATCH 'ev' 

Or a specific column:

SELECT * FROM fts_tickers WHERE name MATCH 'ev' 

We can order it by rank, ie, the closest matches will appear at the top, eg:

SELECT * FROM fts_tickers WHERE fts_tickers MATCH 'ev' ORDER BY rank