sqlite not using index with like query

sqlite index
sqlite drop index
sqlite indexed by
sqlite create unique index multiple columns
sqlite index primary key
sqlite show indexes
python sqlite3 create index
sqlite> explain query plan

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%' 

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.

The SQLite Query Optimizer Overview, Summary: in this tutorial, you will learn how to use SQLite indexes to query data faster, speed Unlike a table, an index has an opposite relationship: (row, rowid)​. Note that B stands for balanced, B-tree is a balanced tree, not a binary tree. SQLite query, 'LIKE' Ask Question Asked 7 years, 8 months ago. Reading around it sounds like it is not supported by default. – ocross Dec 14 '15 at 23:07.

Quote from sqlite mail list (http://www.mail-archive.com/sqlite-users@sqlite.org/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;

SQLite Index: An Essential Guide to SQLite Indexes, When you create an index, you often use one or more columns in a table. The SQLite query planner did not use the index because the expression in the  To query data based on partial information, you use the LIKE operator in the WHERE clause of the SELECT statement as follows: SELECT column_list FROM table_name WHERE column_1 LIKE pattern; Note that you can also use the LIKE operator in the WHERE clause of other statements such as the DELETE and UPDATE. SQLite provides two wildcards for constructing patterns. They are percent sign % and underscore _:

Using SQLite Expression Based Index To Imporve Query Performance, Not all LIKE filters are slow: the performance depends on the position of the wild The SQL LIKE operator very often causes unexpected performance behavior  Note: If you have important queries that require '%defg', you could use a persistent computed column where you REVERSE() the column and then index it. Your can then query on: WHERE Column1Reverse Like REVERSE('defg')+'%' --can use the persistent computed column's index

I also have the same issue with org.xerial/sqlite-jdbc "3.25.2". A simple TEXT based index is not getting used with LIKE operator. But, if I put =, the index gets used. My table schema :

 create table if not exists test (ID INTEGER PRIMARY KEY, VALUE TEXT NOT NULL)
 create index test_idx on test(value)
 insert into test (ID,  VALUE) values (1, 'gold bangles')
 insert into test (ID,  VALUE) values (2, 'gold bandana')
 insert into test (ID,  VALUE) values (2, 'gold bracelet')

 explain query plan select * from test where value='gold bandana'

 Output:
 0|0|0|SEARCH TABLE test USING COVERING INDEX test_idx(VALUE=?)

 explain query plan select * from test where value like 'gold%'

 Output:
 0|0|0|SCAN TABLE test

Tuning SQL LIKE using indexes, Imagine we've got a table that contains information about tags, In short: using the tag_titles index brings the query's performance from O(n) to When you use ORDER BY without an index on the column to be sorted, SQLite  SQLite LIKE operator is used to match text values against a pattern using wildcards. If the search expression can be matched to the pattern expression, the LIKE operator will return true, which is 1. If the search expression can be matched to the pattern expression, the LIKE operator will return true, which is 1.

Squeezing Performance from SQLite: Indexes? Indexes!, Simple, use SQL LIKE and put an index on it. Oh wait, that's weird, it didn't work like how I want this blog covers the common traps with searching text strings in SQLite. We will be running queries on the above data, similar to: ArcGIS Pro 2.5 Patch 2 is Available · Not all layers get loaded in Collector for  To check if SQLite uses the index or not, you use the EXPLAIN QUERY PLAN statement as follows: EXPLAIN QUERY PLAN SELECT first_name, last_name, email FROM contacts WHERE email = 'lisa.smith@sqlitetutorial.net'; Try It

Searching strings in SQLite with SQL LIKE, So, given that SQL Server does not have anything like this built in, how not be used in every single case where LIKE '%wildcard%' searches are slow. Now, if we run the same query we ran against the main table, we get  When you use ORDER BY without an index on the column to be sorted, SQLite needs to build up a temporary data structure that contains the sorted results each time the query is executed. That data

One way to get an index seek for a leading %wildcard in SQL Server, In sqlite indexes are used to improve the performance of data retrieval from Generally, in SQLite when we create a table (Person) without Index that will be like as records with RowId like as shown below to increase the query performance. The SQLite NOT condition can also be combined with the LIKE Condition. For example: SELECT last_name, first_name FROM employees WHERE last_name NOT LIKE 'A%'; By placing the SQLite NOT Operator in front of the LIKE condition, you are able to retrieve all employees whose last_name does not start with 'A'.

Comments
  • Having an index doesn't ensure it will be used.
  • Thanks for that, I was using the firefox extension to test which was a older version. Using a newer version works
  • Hi there,thanks for everyones replies, however after testing the collate nocase on the index, the index is still not being used. Ive updated my example code with a test3 to test the scenario if anyone wants to give it a try. Any other ideas?