Today I learnt that using equals = does not use the same index as LIKE in sqlite
First one via ".expert in sqlite3 cli" <3
CREATE INDEX acl_simple_idx_0a4b252f ON acl_simple(access COLLATE NOCASE);
CREATE INDEX acl_access_lookup ON acl_simple (access);EXPLAIN SELECT * FROM acl_simple where access LIKE 'api:list:267ac81f-2411-5a8f-9f9d-19f7f1e1a5be:%';
addr opcode p1 p2 p3 p4 p5 comment
---- ------------- ---- ---- ---- ------------- -- -------------
0 Init 0 21 0 0 Start at 21
1 OpenRead 0 2 0 4 0 root=2 iDb=0; acl_simple
2 OpenRead 1 63 0 k(2,NOCASE,) 0 root=63 iDb=0; acl_simple_idx_0a4b252f
3 Integer 1 1 0 0 r[1]=1; LIKE loop counter
4 String8 0 2 1 API:LIST:267AC81F-2411-5A8F-9F9D-19F7F1E1A5BE: 0 r[2]='API:LIST:267AC81F-2411-5A8F-9F9D-19F7F1E1A5BE:'
5 SeekGE 1 19 2 1 0 key=r[2]
6 String8 0 2 1 api:list:267ac81f-2411-5a8f-9f9d-19f7f1e1a5be; 0 r[2]='api:list:267ac81f-2411-5a8f-9f9d-19f7f1e1a5be;'
7 IdxGE 1 19 2 1 0 key=r[2]
8 DeferredSeek 1 0 0 0 Move 0 to 1.rowid if needed
9 If 1 13 0 0
10 Column 1 0 5 0 r[5]= cursor 1 column 0
11 Function 1 4 3 like(2) 0 r[3]=func(r[4..5])
12 IfNot 3 18 1 0
13 Column 0 0 6 0 r[6]= cursor 0 column 0
14 Column 0 1 7 0 r[7]= cursor 0 column 1
15 Column 1 0 8 0 r[8]= cursor 1 column 0
16 Column 0 3 9 0 r[9]=acl_simple.created
17 ResultRow 6 4 0 0 output=r[6..9]
18 Next 1 7 0 0
19 DecrJumpZero 1 4 0 0 if (--r[1])==0 goto 4
20 Halt 0 0 0 0
21 Transaction 0 0 9 0 1 usesStmtJournal=0
22 String8 0 4 0 api:list:267ac81f-2411-5a8f-9f9d-19f7f1e1a5be:% 0 r[4]='api:list:267ac81f-2411-5a8f-9f9d-19f7f1e1a5be:%'
23 Goto 0 1 0 0 EXPLAIN SELECT * FROM acl_simple where access = 'api:list:267ac81f-2411-5a8f-9f9d-19f7f1e1a5be:read:fc7f0e39-aa15-52d4-b590-e3a2bf9ee86d';
addr opcode p1 p2 p3 p4 p5 comment
---- ------------- ---- ---- ---- ------------- -- -------------
0 Init 0 14 0 0 Start at 14
1 OpenRead 0 2 0 4 0 root=2 iDb=0; acl_simple
2 OpenRead 1 78 0 k(2,,) 2 root=78 iDb=0; acl_access_lookup
3 String8 0 1 0 api:list:267ac81f-2411-5a8f-9f9d-19f7f1e1a5be:read:fc7f0e39-aa15-52d4-b590-e3a2bf9ee86d 0 r[1]='api:list:267ac81f-2411-5a8f-9f9d-19f7f1e1a5be:read:fc7f0e39-aa15-52d4-b590-e3a2bf9ee86d'
4 SeekGE 1 13 1 1 0 key=r[1]
5 IdxGT 1 13 1 1 0 key=r[1]
6 DeferredSeek 1 0 0 0 Move 0 to 1.rowid if needed
7 Column 0 0 2 0 r[2]= cursor 0 column 0
8 Column 0 1 3 0 r[3]= cursor 0 column 1
9 Column 1 0 4 0 r[4]= cursor 1 column 0
10 Column 0 3 5 0 r[5]=acl_simple.created
11 ResultRow 2 4 0 0 output=r[2..5]
12 Next 1 5 1 0
13 Halt 0 0 0 0
14 Transaction 0 0 9 0 1 usesStmtJournal=0
15 Goto 0 1 0 Content for future blog post on blog.