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
Topics: SQLITE