3

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.

Minh Nghĩa
  • 854
  • 1
  • 11
  • 28
  • 1
    Full text search versus a normal index lookup. Some reading: https://www.sqlite.org/fts5.html https://www.sqlite.org/queryplanner.html – Shawn Nov 29 '20 at 13:08

2 Answers2

2

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.

Lukasz Szozda
  • 162,964
  • 23
  • 234
  • 275
2

phrases order in a text is important in a normal lookup, You can't find 'aaa bbb' when you search sth like:

Select * from table where text like 'bbb aaa'

but in a full-text search you can find it.

das
  • 49
  • 5