sqlite not using index with like query

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;

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.