SQLite Cheatsheet 04 — Query Optimization

SQLite query optimization.

EXPLAIN QUERY PLAN

EXPLAIN QUERY PLAN SELECT * FROM users WHERE email = 'x';

Look for:

SEARCH ... USING INDEX (good).
SCAN ... (bad on big tables).

ANALYZE

ANALYZE;
ANALYZE users;
PRAGMA analysis_limit = 1000;

Helps optimizer pick indexes.

VACUUM

VACUUM;
PRAGMA auto_vacuum = INCREMENTAL;
PRAGMA incremental_vacuum(100);

Reclaim space after big deletes.

Optimize PRAGMA

PRAGMA optimize;

Run periodically; auto-runs ANALYZE on changed tables.

Indexes

See schema cheatsheet. Most queries should use index.

Covering indexes

CREATE INDEX idx_cover ON users(status, name);
SELECT name FROM users WHERE status = 'x';   -- index-only

Materialize CTEs

WITH expensive AS MATERIALIZED (
    SELECT ... FROM huge_table WHERE ...
)
SELECT * FROM expensive WHERE ...;

Prepared statements

cursor.execute("INSERT INTO users (name) VALUES (?)", (name,))

Reuses parsed plan. Much faster in loops.

Bulk insert

cursor.executemany("INSERT INTO users (name) VALUES (?)", data)
# Or
with conn:
    for row in rows:
        conn.execute("...", row)

Wrap in transaction for 100x speedup.

Pagination

SELECT * FROM users WHERE id > ? ORDER BY id LIMIT 20;

Cursor-based; faster than OFFSET for big tables.

SQLite cache

PRAGMA cache_size = -64000;        -- 64MB negative = KB; positive = pages

mmap

PRAGMA mmap_size = 268435456;       -- 256MB

Memory-map DB file. Faster reads.

temp_store

PRAGMA temp_store = MEMORY;

Temp tables in RAM.

Statement timing

.timer ON
SELECT ...

Common mistakes

No index on join column.
LIKE ‘%x’ (no prefix → no index).
Big OFFSET pagination.
Many small inserts without transaction.
COUNT(*) on huge table.

EXPLAIN QUERY PLAN#

ANALYZE#

VACUUM#

Optimize PRAGMA#

Indexes#

Covering indexes#

Materialize CTEs#

Prepared statements#

Bulk insert#

Pagination#

SQLite cache#

mmap#

temp_store#

Statement timing#

Common mistakes#

Read this next#