SQLite is a popular library that implements a self-contained, serverless, zero-configuration SQL database engine. One of its most powerful and flexible features is full-text search (FTS), which allows you to efficiently query unstructured textual data. In this article, we'll explore practical examples of how to implement full-text search in SQLite, using FTS5, the latest full-text search extension.
Understanding Full-Text Search
Full-text search allows users to search within large amounts of text data. Unlike simple pattern matching, FTS uses various indexing and querying strategies to efficiently search for words or phrases within text. SQLite's FTS5 module allows for powerful full-text querying capabilities that can include relevance scoring and ranking, prefix searches, and more.
Getting Started with FTS5
To utilize full-text search, you must create a virtual table using FTS5. This process involves specifying the structure and configuration for the FTS engine to know which text to index and search.
-- Creating a FTS5 table in SQLite
CREATE VIRTUAL TABLE documents USING fts5(title, content);
The above SQL command creates a virtual table named documents where the full-text index is built over the title and content columns. This index will make text search operations within these columns efficient.
Inserting Data
Once the table is established, it can be populated just like any regular SQLite table, enabling these textual fields to be indexed.
-- Inserting sample data into the FTS5 table
INSERT INTO documents (title, content) VALUES ('Introduction to SQLite', 'SQLite is a C-language library that implements a...');
INSERT INTO documents (title, content) VALUES ('Full-Text Search', 'FTS5 is the most recent full-text search extension supported by SQLite');
Executing a Full-Text Search
After populating data, you can execute full-text queries using the MATCH operator. Here's a basic example:
-- Searching for documents containing the keyword 'SQLite'
SELECT title, content FROM documents WHERE documents MATCH 'SQLite';
This query returns all documents where the title or content contains the word 'SQLite'. What's special about FTS5 is its use of an inversion index, which significantly speeds up searches compared to traditional LIKE queries for these operations.
Performing More Complex Queries
FTS5 offers more comprehensive search capabilities that include features such as prefix search, phrase search, and NEAR operations.
-- Performing a prefix search
SELECT title, content FROM documents WHERE documents MATCH 'SQL*';
-- Phrase search
SELECT title, content FROM documents WHERE documents MATCH '"full text search"';
-- NEAR operation for finding terms close to each other
SELECT title, content FROM documents WHERE documents MATCH 'FTS NEAR/5 SQLite';
In these examples, the prefix search looks for words that start with SQL, the phrase search finds documents containing the exact phrase "full text search", and the NEAR operation finds documents where the words FTS are within five words of SQLite.
Optimizing Search Ranking
FTS5 includes facilities to influence ranking by using relevance scores. By default, searches return results in order of match quality.
-- Fetching results with relevance scores
SELECT title, content, rank FROM documents, bm25(documents)
WHERE documents MATCH 'SQLite';
The bm25() function is an example of a ranking function, standard within information retrieval, enhancing the search result ordering.
Leveraging FTS5 Extensions
Beyond basic search and ranking, SQLite FTS5 can be extended with custom tokenizers and pre-processors, adapting searches to particular domains or languages. This customization capability makes FTS5 a flexible tool for varied applications in documents storage.
Conclusion
SQLite's full-text search capability with FTS5 empowers developers to implement powerful search functionalities with minimal setup effort. These practical examples illustrate only some of the possibilities. Understanding and utilizing text indexing and FTS capabilities can significantly enhance the performance of text-heavy applications. Experimenting with various search commands will further benefit such development.
