How to use FTS3 in SQLite

Question

I have table with almost 200k entries. When I tried search with LIKE, it was very slow. Now I decided to use FTS. So I created two indexes where search will be held. Then I created fts virtual table.

`CREATE TABLE [search_eng] (
  [id] INTEGER PRIMARY KEY AUTOINCREMENT, 
  [entry_id] INTEGER, 
  [re_value] TEXT, 
  [ke_value] TEXT, 
  [g_value] TEXT);

CREATE INDEX idx_se_re ON search_eng (re_value);

CREATE INDEX idx_se_gv ON search_eng (g_value);


CREATE VIRTUAL TABLE search_eng_fts USING fts3(id, entry_id, re_value, ke_value, g_value);`

I have no idea how to use new created FTS table. So my questions is how to use that virtual table to make search? Can you give an example?

Pula

Answer 1

This is explained in the documentation.

You do not need the two indexes for FTS searches.

You should declare the id column as INTEGER PRIMARY KEY. You probably don't need the entry_id column in the FST table.

Copy the text into the FTS table:

INSERT INTO search_eng_fts(id, re_value, ke_value, g_value)
SELECT id, re_value, ke_value, g_value FROM search_eng;

Then you can use the MATCH operator to search in that table:

SELECT id FROM search_eng_fts WHERE re_value MATCH 'hello';

Reminisce