sql
CREATE INDEX idx_posts_user_id ON posts(user_id);Notes
A simple index can dramatically reduce scan work on filters and joins.
SQLite Indexes and Query Planner Cheatsheet
CREATE INDEX patterns, covering and partial indexes, EXPLAIN QUERY PLAN, ANALYZE, and practical tuning tips for SQLite queries.
Export
Copy the compact sheet, download it, or print it.
Download
`D` dense toggle · `C` copy all
Create and use indexes
Speed up filters, joins, and sorts with the right index shape.
sql
CREATE INDEX idx_posts_user_created_at ON posts(user_id, created_at DESC);Notes
Composite indexes work best when they match common WHERE and ORDER BY patterns.
sql
CREATE UNIQUE INDEX idx_users_email ON users(email);Notes
Useful both for integrity and for fast point lookups.
sql
CREATE INDEX idx_sessions_active ON sessions(user_id)
WHERE revoked_at IS NULL;Notes
Partial indexes can be smaller and more selective than indexing the entire table.
Inspect query plans
Use planner output and statistics to tune real queries.
sql
EXPLAIN QUERY PLAN
SELECT * FROM posts WHERE user_id = 42 ORDER BY created_at DESC LIMIT 10;Notes
High-signal first step when a query feels slower than expected.
Enable automatic EXPLAIN QUERY PLAN in shell
Show plans before each entered statement.
textANYsqlite3eqpshell
text
.eqp onNotes
Handy during interactive tuning sessions in the sqlite3 shell.
Collect statistics with ANALYZE
Help the planner choose better indexes.
sqlANYsqliteanalyzestatistics
sql
ANALYZE;Notes
Planner statistics can improve decisions for complex queries and multiple indexes.
sql
REINDEX;Notes
Useful after collation changes or when rebuilding index structures.
sql
PRAGMA optimize;Notes
A low-cost maintenance step you can run periodically in long-lived applications.
More in SQLite
SQLite Triggers, Views, and Generated Columns Cheatsheet
CREATE VIEW, CREATE TRIGGER, audit logging, derived values, generated columns, and automation patterns in SQLite.
SQLite Full-Text Search (FTS5) Cheatsheet
FTS5 virtual tables, MATCH queries, prefix search, phrase search, bm25 ranking, snippets, and highlighting patterns for SQLite search features.
SQLite PRAGMA and Administration Cheatsheet
High-signal PRAGMA commands for foreign keys, cache, synchronous settings, page size, temp storage, health checks, and general SQLite administration.
SQLite JSON Functions Cheatsheet
json_extract, json_set, json_each, JSON operators, generated columns, and practical JSON query/update patterns in SQLite.
SQLite Import, Export, and Backup Cheatsheet
CSV import/export, SQL dumps, VACUUM INTO, backup copies, and repeatable data movement patterns for SQLite databases.
SQLite Transactions, WAL, and Locking Cheatsheet
BEGIN modes, COMMIT and ROLLBACK, savepoints, WAL mode, busy timeouts, and concurrency patterns for SQLite applications.