3 ms·
LIKE is case-independent by default. This prevents using an index for optimization, even for LIKE '<prefix>%'. To fix this, you can switch to GLOB '1*', which
by prirun 4y ago
LIKE is case-independent by default. This prevents using an index for optimization, even for LIKE '<prefix>%'. To fix this, you can switch to GLOB '1*', which is case-sensitive, use the NOCASE collating sequence for the index, or use the case_sensitive_like pragma for a more global change.
To show that, here's an example session comparing LIKE and GLOB with and without an index:
[jim@mbp ~]$ sqlite3
-- Loading resources from /Users/jim/.sqliterc
SQLite version 3.38.0 2022-02-22 19:15:21 with the Encryption (see-aes128-ofb)
Copyright 2016 Hipp, Wyrick & Company, Inc.
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.
sqlite> create table t2 (v text);
sqlite> insert into t2 (v) values (1);
sqlite> insert into t2 (v) values ('2');
sqlite> select * from t2 where v like '1%';
v
-
1
sqlite> explain select * from t2 where v like '1%';
addr opcode p1 p2 p3 p4 p5 comment
---- ------------- ---- ---- ---- ------------- -- -------------
0 Init 0 10 0 0
1 OpenRead 0 3 0 1 0
2 Rewind 0 9 0 0
3 Column 0 0 3 0
4 Function 1 2 1 like(2) 0
5 IfNot 1 8 1 0
6 Column 0 0 4 0
7 ResultRow 4 1 0 0
8 Next 0 3 0 1
9 Halt 0 0 0 0
10 Transaction 0 0 2 0 1
11 String8 0 2 0 1% 0
12 Goto 0 1 0 0
GLOB behaves in a similar way because there is no index:
sqlite> explain select * from t2 where v glob '1%';
addr opcode p1 p2 p3 p4 p5 comment
---- ------------- ---- ---- ---- ------------- -- -------------
0 Init 0 10 0 0
1 OpenRead 0 3 0 1 0
2 Rewind 0 9 0 0
3 Column 0 0 3 0
4 Function 1 2 1 glob(2) 0
5 IfNot 1 8 1 0
6 Column 0 0 4 0
7 ResultRow 4 1 0 0
8 Next 0 3 0 1
9 Halt 0 0 0 0
10 Transaction 0 0 2 0 1
11 String8 0 2 0 1% 0
12 Goto 0 1 0 0
Create an index on v and see how things change:
sqlite> create index i2 on t2 (v);
sqlite> explain select * from t2 where v glob '1%';
addr opcode p1 p2 p3 p4 p5 comment
---- ------------- ---- ---- ---- ------------- -- -------------
0 Init 0 15 0 0
1 OpenRead 1 4 0 k(2,,) 0
2 Integer 1 1 0 0
3 String8 0 2 1 1% 0
4 SeekGE 1 13 2 1 0
5 String8 0 2 1 1& 0
6 IdxGE 1 13 2 1 0
7 Column 1 0 5 0
8 Function 1 4 3 glob(2) 0
9 IfNot 3 12 1 0
10 Column 1 0 6 0
11 ResultRow 6 1 0 0
12 Next 1 6 0 0
13 DecrJumpZero 1 3 0 0
14 Halt 0 0 0 0
15 Transaction 0 0 3 0 1
16 String8 0 4 0 1% 0
17 Goto 0 1 0 0
sqlite> explain select * from t2 where v like '1%';
addr opcode p1 p2 p3 p4 p5 comment
---- ------------- ---- ---- ---- ------------- -- -------------
0 Init 0 10 0 0
1 OpenRead 0 3 0 1 0
2 Rewind 0 9 0 0
3 Column 0 0 3 0
4 Function 1 2 1 like(2) 0
5 IfNot 1 8 1 0
6 Column 0 0 4 0
7 ResultRow 4 1 0 0
8 Next 0 3 0 1
9 Halt 0 0 0 0
10 Transaction 0 0 3 0 1
11 String8 0 2 0 1% 0
12 Goto 0 1 0 0
Now GLOB is using the index, but LIKE still doesn't because of the case issue.
NOTE! I goofed up the GLOB queries: it should be GLOB '1*', not '1%'.
https://stackoverflow.com/questions/8584499/sqlite-should-like-searchstr-use-an-index https://stackoverflow.com/questions/8584499/sqlite-should-li...
- didgetmaster 4y agoThanks. GLOB ran about 10x faster than LIKE for my data set.