4 ms·

Any ideas why an index in SQLite would make a query slower? I have a 22 column table created with the city of Chicago crime data loaded (7.6M rows). There is a

Any ideas why an index in SQLite would make a query slower? I have a 22 column table created with the city of Chicago crime data loaded (7.6M rows). There is a block column that has the block within the city where the crime occurred (about 58K unique block names). If I want to find all the blocks that start with a 1, I use 'SELECT block FROM crimes WHERE block LIKE '1%'; which returns about 370K rows in about 1600 ms. If I then create an index on the block column, the same query takes about 1800 ms (about 200 milliseconds longer). I ran each query 5 times and the queries with the index are always slower. I am much more familiar with Postgres and creating an index on the same table there always speeds things up quite a bit.

yread 4y ago
Sometimes SCAN is faster than SEARCH. For example if the query returns a significant percentage of all values.
- didgetmaster 4y ago
  I am aware of that, but in this case the query returns less than 5% of the total rows and I think the database should choose to ignore the index if a scan is faster.
skissane 4y ago
Could it have something to do with the data type of your block column? (Check with typeof() function.) Just guessing, but I would expect LIKE ‘1%’ to perform very differently for string vs numeric data. Originally SQLite had no types for (most) columns - but it still had types for the values. Now with STRICT tables the columns can have types too.
- didgetmaster 4y ago
  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...

yread 4y ago

Sometimes SCAN is faster than SEARCH. For example if the query returns a significant percentage of all values.

didgetmaster 4y ago
I am aware of that, but in this case the query returns less than 5% of the total rows and I think the database should choose to ignore the index if a scan is faster.

skissane 4y ago

Could it have something to do with the data type of your block column? (Check with typeof() function.) Just guessing, but I would expect LIKE ‘1%’ to perform very differently for string vs numeric data. Originally SQLite had no types for (most) columns - but it still had types for the values. Now with STRICT tables the columns can have types too.

didgetmaster 4y ago
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...