SQLite Indexes and Query Planner Cheatsheet

CREATE INDEX idx_posts_user_id ON posts(user_id);

# Index a frequently filtered column.

CREATE INDEX idx_posts_user_created_at ON posts(user_id, created_at DESC);

# Index multiple columns in query order.

CREATE UNIQUE INDEX idx_users_email ON users(email);

# Enforce uniqueness and speed up lookups.

CREATE INDEX idx_sessions_active ON sessions(user_id)
WHERE revoked_at IS NULL;

# Index only rows that match a predicate.

EXPLAIN QUERY PLAN
SELECT * FROM posts WHERE user_id = 42 ORDER BY created_at DESC LIMIT 10;

# See whether SQLite scans or uses an index.

.eqp on

# Show plans before each entered statement.

ANALYZE;

# Help the planner choose better indexes.

REINDEX;

# Recreate indexes when needed.

PRAGMA optimize;

# Let SQLite perform lightweight maintenance.