SQLite with full-text index and B-tree index on another column

collimarco 0
Viewed 19

I have a table like this:

id | status | message
1  | 200    | Some long text
2  | 400    | Other text

Sometimes I need to find a row based on the id, sometimes based on the status (including range queries), sometimes I need to filter based on some words contained in the message. And sometimes I need to filter based on a mix of conditions on all columns.

The problem is that I have two alternatives:

  • Use a virtual table FTS5 for full-text search
  • Use a normal table with the index on id and status

It seems that I can't have both... Am I missing something? What should I do in this case?

full-text-search sqlite indexing
1 Answers

You can have both, though.

Add the needed indexes to your table. And then create an external content FTS5 table that uses it as the backing store.

CREATE TABLE my_table(id INTEGER PRIMARY KEY, status INTEGER, message TEXT);
CREATE INDEX my_table_status_idx ON my_table(status);
CREATE VIRTUAL TABLE my_table_fts USING fts5(message, content=my_table, content_rowid=id);

and then update the FTS table appropriately when inserting or deleting rows from my_table. The documentation gives example triggers to automate this part.

userU2LF0
Related