SQLite: Index Recommendations

For databases, most of the read performance depends on indexing. SQLite has expert command which recommends the appropriate index creation based on the query.

sqlite❯ CREATE TABLE x1(a, b, c);                  -- Create table in database 
sqlite❯ .expert
sqlite❯ SELECT * FROM x1 WHERE a=? AND b>?;        -- Analyze this SELECT 
CREATE INDEX x1_idx_000123a7 ON x1(a, b);

0|0|0|SEARCH TABLE x1 USING INDEX x1_idx_000123a7 (a=? AND b>?)

sqlite❯ CREATE INDEX x1ab ON x1(a, b);             -- Create the recommended index 
sqlite❯ .expert
sqlite❯ SELECT * FROM x1 WHERE a=? AND b>?;        -- Re-analyze the same SELECT 
(no new indexes)

0|0|0|SEARCH TABLE x1 USING INDEX x1ab (a=? AND b>?)