SQLite – Partial Indexes
sqlite.org
sqlite.org
> Partial indexes have been supported in SQLite since version 3.8.0 (2013-08-26).
https://www.postgresql.org/docs/current/indexes-partial.html
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:
[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
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
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
NOTE! I goofed up the GLOB queries: it should be GLOB '1*', not '1%'.
https://stackoverflow.com/questions/8584499/sqlite-should-li...
The syntax looks like:
CREATE INDEX ON Asset (capturedAt) WHERE visible = 1;