Can an index on a text column speed up prefix based LIKE queries?

Question

In SQLite, can an index on a text column speed up prefix based LIKE queries on the column?

For example, if I have a TEXT column named path and I run a query similar to WHERE path LIKE '/path/to/some/dir/%', will this query be able to benefit from an index on that path column?

Answer 1

LIKE will not benefit from the index ( with default option ) but you can rewrite you query by using GLOB or BETWEEN .

SOLUTION 1

With a regular index :

like => not optimized

sqlite> explain query plan select * from pathdta where path like '/path/to/some/dir/a%' ;
0|0|0|SCAN TABLE pathdta

GLOB => optimized

sqlite> explain query plan select * from pathdta where path GLOB '/path/to/some/dir/a*' ;
0|0|0|SEARCH TABLE pathdta USING COVERING INDEX ix_pathdta_dta (path>? AND path<?)

greater => optimized

sqlite> explain query plan  select * from pathdta where path >= '/path/to/some/dir/a' ;
0|0|0|SEARCH TABLE pathdta USING COVERING INDEX ix_pathdta_dta (path>?)

equality => optimized

sqlite> explain query plan  select * from pathdta where path = '/path/to/some/dir/a' ;
0|0|0|SEARCH TABLE pathdta USING COVERING INDEX ix_pathdta_dta (path=?)

between => optimized

sqlite> explain query plan  select * from pathdta
   ...>    where path between '/path/to/some/dir/a' and '/path/to/some/dir/b' ;
0|0|0|SEARCH TABLE pathdta USING COVERING INDEX ix_pathdta_dta (path>? AND path<?)

SOLUTION 2

use a collate nocase index .

like => optimized

sqlite> explain query plan select * from pathdta where path like '/path/to/some/dir/a%' ;
0|0|0|SEARCH TABLE pathdta USING COVERING INDEX ix_pathdta_dta (path>? AND path<?)

glob => not optimized

sqlite> explain query plan select * from pathdta where path GLOB '/path/to/some/dir/a*' ;
0|0|0|SCAN TABLE pathdta

greater => not optimized

sqlite> explain query plan  select * from pathdta where path >= '/path/to/some/dir/a' ;
0|0|0|SCAN TABLE pathdta

equality => not optimized

sqlite> explain query plan  select * from pathdta where path = '/path/to/some/dir/a' ;
0|0|0|SCAN TABLE pathdta

between => not optimized

sqlite> explain query plan  select * from pathdta
   ...>    where path between '/path/to/some/dir/a' and '/path/to/some/dir/b' ;
0|0|0|SCAN TABLE pathdta