sqlite not using index with like query

Question

Ive been trying to get sqlite to use an index with a like to no avail. Ive tried collate nocase and still no luck. Anyone have any ideas on how to get sqlite to do a like hitting an index. Thanks in advance

DROP TABLE IF EXISTS "test";
DROP TABLE IF EXISTS "test2";
DROP TABLE IF EXISTS "test3";
create table test(name TEXT COLLATE NOCASE);
create table test2(name TEXT);
create table test3(name TEXT);
create index idx_test_name on test(name);
create index idx_test2_name on test2(name);
create index idx_test3_name on test3(name COLLATE NOCASE);
insert into test(name) values('dan');
insert into test2(name) values('dan');
insert into test3(name) values('dan');
--explain query plan select * from test where name like 'test%' 
-- explain query plan select * from test2 where name like 'test%' 
-- explain query plan select * from test3 where name like 'test%' 

Answer 1

Quote from sqlite mail list (http://www.mail-archive.com/[email protected]/msg27760.html)

LIKE is case-insensitive by default. To have it use your index, you need to either make the index case-insensitive:

CREATE INDEX test_name ON test (name COLLATE NOCASE);

or make LIKE case-sensitive:

PRAGMA case_sensitive_like = 1;

Answer 2

In SQLite 3.6.23.1, the index on test is used:

> explain query plan select * from test where name like 'test%';
TABLE test WITH INDEX idx_test_name

> explain query plan select * from test2 where name like 'test%';
TABLE test2

> explain query plan select * from test3 where name like 'test%';
TABLE test3

With a development version of SQLite 3.7.15, both test's and test3's indexes are used (the index on test2 is used for scanning, not searching):

> explain query plan select * from test where name like 'test%';
SEARCH TABLE test USING COVERING INDEX idx_test_name (name>? AND name<?) (~31250 rows)

> explain query plan select * from test2 where name like 'test%';
SCAN TABLE test2 USING COVERING INDEX idx_test2_name (~500000 rows)

> explain query plan select * from test3 where name like 'test%';
SEARCH TABLE test3 USING COVERING INDEX idx_test3_name (name>? AND name<?) (~31250 rows)

So the answer is to update SQLite.

Answer 3

From the docs:

Terms that are composed of the LIKE or GLOB operator can sometimes be used to constrain indices. There are many conditions on this use:

• The left-hand side of the LIKE or GLOB operator must be the name of an indexed column with TEXT affinity.
• The right-hand side of the LIKE or GLOB must be either a string literal or a parameter bound to a string literal that does not begin with a wildcard character.
• The ESCAPE clause cannot appear on the LIKE operator.
• The built-in functions used to implement LIKE and GLOB must not have been overloaded using the sqlite3_create_function() API.
• For the GLOB operator, the column must be indexed using the built-in BINARY collating sequence.
• For the LIKE operator, if case_sensitive_like mode is enabled then the column must indexed using BINARY collating sequence, or if case_sensitive_like mode is disabled then the column must indexed using built-in NOCASE collating sequence.