SQLite Forum

FTS5 tables, = vs. MATCH, index 0, and "error: no such column"
If it just says "INDEX 0:", it means a linear scan of the entire table. But, for example, "INDEX 0:M1", means a full-text query. Technically the bit after "INDEX" is the idxNum value, a ":" character, and the idxStr value:



You can mess around with external content tables to get a data structure that supports full-text queries and other indexes as well, but there's no way to just add an index to a column of an fts5 table.


In some cases you could add a MATCH clause to prune the number of rows scanned. e.g. change:

    SELECT * FROM fts5_tab WHERE c='abc';


    SELECT * FROM fts5_tab WHERE c='abc' AND c MATCH 'abc';

I guess in theory fts5 could do this automatically. It doesn't at the moment though.