Search code examples
sqliteindexingfull-text-search

What's the difference of normal index and FTS in SQLite

Both use index to speed up string searching. So what's the difference?

In the docs for CREATE INDEX, there's no mention of tokenization, which makes me think that normal indices on text columns only speed up "=" queries, instead of more sophisticated MATCH query that can match words inside paragraphs, but I can't find any source that confirm this.

Solution

  • which makes me think that normal indices on text columns only speed up "=" queries

    In general standard B-Tree index on text column speeds up SARGable queries like:

    SELECT col FROM tab WHERE col = 'value'
SELECT col FROM tab WHERE col LIKE 'value'
SELECT col FROM tab WHERE col LIKE 'value%'

    But not:

    SELECT col FROM tab WHERE col LIKE '%value%';  
SELECT col FROM tab WHERE col LIKE '%value';
SELECT col FROM tab WHERE UPPER(col) = 'VALUE';

    They could still benefit from Index Scan instead of reading table, but it will not be as fast as Index Seek.