In this lesson, you will learn how to leverage the FTS5 extension in SQLite to perform high-speed, relevant text searches that go far beyond standard string matching.
The Problem with LIKE
When you search for text using the LIKE operator (e.g., WHERE content LIKE '%search_term%'), the database performs a "full table scan." It reads every row, scans every character of your text, and discards anything that doesn't match. On large datasets, this is incredibly slow because the standard B-Tree index cannot optimize for wildcard searches starting with %.
Enter FTS5: The Inverted Index
FTS5 (Full-Text Search version 5) solves this by building an inverted index. Think of this like the index at the back of a textbook: instead of scanning every page to find a word, you look up the word in the index to get a list of page numbers where it appears.
When you define a virtual table using FTS5, SQLite tokenizes your text (breaks it into individual words) and stores the locations of those words in a separate, optimized data structure.
Implementation
To use FTS5, you create a virtual table instead of a standard one. Here is how you initialize it:
-- Create the virtual table for full-text search
CREATE VIRTUAL TABLE articles_fts USING fts5(title, body);
-- Populate it with data
INSERT INTO articles_fts (title, body)
VALUES ('Database Engines', 'SQLite uses a virtual table engine for FTS5.');
-- Perform a high-speed search
SELECT * FROM articles_fts WHERE articles_fts MATCH 'virtual';
When you execute the MATCH query, FTS5 instantly retrieves only the rows containing the word "virtual" by consulting the inverted index. It ignores the rest of the table entirely. This remains incredibly fast even as your database grows into the millions of rows, making it the standard choice for search features in embedded applications where CPU and I/O resources are limited.
Key Takeaway: FTS5 replaces slow sequential scanning with an inverted index, allowing near-instant keyword searching that scales efficiently regardless of dataset size.