CODEWITHSUNDEEP
sqliteindexingtextsql-like
lurf jurv
lurf jurv

Reputation: 277

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

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?

Upvotes: 5

Views: 3643

Answers (2)

EchoMike444
EchoMike444

Reputation: 1692

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

Upvotes: 10

Shawn
Shawn

Reputation: 52336

Yes, it will, with the right index. If using the default case-insensitive LIKE mode, the index also needs to be case-insensitive. There are other restrictions too, see the LIKE optimization documentation for full details (It's quite long and hard to summarize).

Example:

sqlite> CREATE TABLE paths(id INTEGER PRIMARY KEY, path TEXT);
sqlite> CREATE INDEX paths_idx_path ON paths(path); -- case sensitive index
sqlite> EXPLAIN QUERY PLAN SELECT * FROM paths WHERE path LIKE 'foo%';
QUERY PLAN
`--SCAN TABLE paths
sqlite> DROP INDEX paths_idx_path;
sqlite> CREATE INDEX paths_idx_path ON paths(path COLLATE NOCASE); -- case insensitive index
sqlite> EXPLAIN QUERY PLAN SELECT * FROM paths WHERE path LIKE 'foo%';
QUERY PLAN
`--SEARCH TABLE paths USING COVERING INDEX paths_idx_path (path>? AND path<?)

As you can see, with the case-insensitive index, the query gets rewritten to do a search of rows in a particular range, not a scan of all rows in the table.

You can also specify that all comparisons of the column be case-insensitive in the table definition:

CREATE TABLE paths(id INTEGER PRIMARY KEY, path TEXT COLLATE NOCASE);

and then the index doesn't need the COLLATE because it's already implied.

Upvotes: 2

Related Questions