sqlite-index-not-used.md

SQLite version 3.24.0 2018-06-04 14:10:15
Enter ".help" for usage hints.

sqlite> .schema
CREATE TABLE stats (
  date        TEXT,
  group_id    TEXT,
  artifact_id TEXT,
  version     TEXT,
  downloads   INT,
  CONSTRAINT prim UNIQUE (date, group_id, artifact_id, version)
);

sqlite> .timer on

sqlite> select count(downloads) from stats where artifact_id = 're-frame' and group_id = 're-frame' and date like '2018-%';
4106
Run Time: real 0.966 user 0.789593 sys 0.174458

sqlite> explain query plan select count(downloads) from stats where artifact_id = 're-frame' and group_id = 're-frame' and date like '2018-%';
QUERY PLAN
`--SCAN TABLE stats
Run Time: real 0.000 user 0.000086 sys 0.000019

A full table scan is necessary for the query above — despite the UNIQUE constraint.

sqlite> create index stats_keys on stats (date, group_id, artifact_id, version);
Run Time: real 14.217 user 10.871043 sys 3.053484

sqlite> explain query plan select count(downloads) from stats where artifact_id = 're-frame' and group_id = 're-frame' and date like '2018-%';
QUERY PLAN
`--SCAN TABLE stats
Run Time: real 0.000 user 0.000100 sys 0.000015

Adding another index doesn't immediately solve the issue.

But running ANALYZE stats does:

sqlite> ANALYZE stats;
Run Time: real 4.970 user 4.575637 sys 0.385702

sqlite> explain query plan select count(downloads) from stats where artifact_id = 're-frame' and group_id = 're-frame' and date like '2018-%';
QUERY PLAN
`--SEARCH TABLE stats USING INDEX stats_keys (ANY(date) AND group_id=? AND artifact_id=?)
Run Time: real 0.000 user 0.000105 sys 0.000021

Why is that? Is it expected that users run ANALYZE on their own?