SQLite Forum

FTS5 tables, = vs. MATCH, index 0, and "error: no such column"
Login
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:

[https://sqlite.org/vtab.html#outputs]

[http://www.sqlite.org/src/artifact/b4e4931c7fcc9?ln=545]

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.

[https://sqlite.org/fts5.html#external_content_tables]

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';

to

    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.

Dan