I'm sure this is news to many and I won't claim that this doesn't belong on HN (if it's upvoted then it's useful), but I feel it's important to point out that this is not a recent development:
> Partial indexes have been supported in SQLite since version 3.8.0 (2013-08-26).
Ah! I use these all the time with PhotoStructure--it's great for "work-in-progress" rows that shouldn't be shown to the user quite yet (but I need the row added as I need to populate join tables with the primary key), or rows that are no longer relevant (due to files disappearing or becoming unreadable), but might return, so I'm not wanting to delete it quite yet.
The syntax looks like:
CREATE INDEX ON Asset (capturedAt) WHERE visible = 1;
And (like with any index and any RDBMS), you have to remember to include that column in any relevant WHERE clause for the index to be considered relevant.
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.
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.
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:
16 comments
[ 4.3 ms ] story [ 47.5 ms ] thread> Partial indexes have been supported in SQLite since version 3.8.0 (2013-08-26).
https://www.postgresql.org/docs/current/indexes-partial.html
The syntax looks like:
And (like with any index and any RDBMS), you have to remember to include that column in any relevant WHERE clause for the index to be considered relevant.I am much more familiar with Postgres and creating an index on the same table there always speeds things up quite a bit.
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.
To show that, here's an example session comparing LIKE and GLOB with and without an index:
GLOB behaves in a similar way because there is no index: Create an index on v and see how things change: