Searching for Data using FTS — Full Text Search

There are many ways you can search for data in database using queries. For example using LIKE keyword. But FTS is fast and robust. This approach is possible using VIRTUAL tables and MATCH keyword.

VIRTUAL table uses indexing to search but the LIKE however has got nothing of this. It is forced to linearly scan the sentence/string and find all matching terms. Adding wild card adds to the mess. It works great for small length strings, as you can imagine, but will fail miserably for longer sentences. And definitely not comparable when having a paragraph or a whole page of text etc.

FTS1 and FTS2 are obsolete full-text search modules for SQLite. FTS3 and FTS4 are SQLite virtual table modules that allows users to perform full-text searches. Below example uses FTS3.

1. Create FTS Table

CREATE VIRTUAL TABLE fts_table USING fts3 ( col_1, col_2, text_column )

2. Populate FTS Table

INSERT INTO fts_table VALUES ('3', 'apple', 'Hello. How are you?')
INSERT INTO fts_table VALUES ('24', 'car', 'Fine. Thank you.')
INSERT INTO fts_table VALUES ('13', 'book', 'This is an example.')

3. Query FTS Table

SELECT * FROM fts_table WHERE fts_table MATCH ?

I have created the demo project in Android which uses FTS3.

deepak786/FTS

Other than advantages of Virtual tables, there are some disadvantages. SQL statements can do almost anything to a virtual table that they can do to a real table, with the following exceptions:

• One cannot create a trigger on a virtual table.
• One cannot create additional indices on a virtual table. (Virtual tables can have indices but that must be built into the virtual table implementation. Indices cannot be added separately using CREATE INDEX statements.)
• One cannot run ALTER TABLE … ADD COLUMN commands against a virtual table.

For more info regarding virtual tables visit https://sqlite.org/vtab.html

The virtual FTS3 table above has a problem with it. Every column is indexed, but this is a waste of space and resources if some columns don’t need to be indexed. To solve this problem we will use a combination of a regular table and a virtual FTS table. The FTS table will contain the index but none of the actual data from the regular table. Notice that we have to use FTS4 to do this rather than FTS3. I’ll share the information and demo using FTS4 ASAP.

Don’t forget to share your thoughts in the comments.