3 ms·

The block column is of type TEXT which should be fastest for LIKE '1%'

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 ago
  Thanks. GLOB ran about 10x faster than LIKE for my data set.

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 ago
Thanks. GLOB ran about 10x faster than LIKE for my data set.