SQLite Indexes and Query Planner

CREATE INDEX idx_notes_user_id_created_at ON notes (user_id, created_at DESC);

# Add a basic lookup index.

CREATE INDEX idx_users_lower_email ON users (lower(email));

# Index a computed expression used in the query.

CREATE INDEX idx_tasks_open_due_at ON tasks (due_at) WHERE completed_at IS NULL;

# Index only matching rows.

EXPLAIN QUERY PLAN
SELECT id, created_at
FROM notes
WHERE user_id = 42
ORDER BY created_at DESC
LIMIT 20;

# Inspect scans, searches, and covering-index use.

ANALYZE;

# Populate or refresh planner statistics.

sqlite3 app.db ".expert"

# Ask the SQLite shell for index advice.