sql
CREATE INDEX idx_notes_user_id_created_at ON notes (user_id, created_at DESC);Notes
A compact way to speed up per-user timelines or history screens.
SQLite Indexes and Query Planner
SQLite CREATE INDEX patterns, expression and partial indexes, EXPLAIN QUERY PLAN, and lightweight performance tuning.
Export
Copy the compact sheet, download it, or print it.
Download
`D` dense toggle · `C` copy all
SQLite index creation
Practical CREATE INDEX examples for apps and embedded databases.
sql
CREATE INDEX idx_users_lower_email ON users (lower(email));Notes
Helpful when the query uses the same expression in predicates.
sql
CREATE INDEX idx_tasks_open_due_at ON tasks (due_at) WHERE completed_at IS NULL;Notes
A smaller index can be more efficient when only open tasks matter to the hot path.
Planner tools
Validate the actual plan chosen by SQLite.
sql
EXPLAIN QUERY PLAN
SELECT id, created_at
FROM notes
WHERE user_id = 42
ORDER BY created_at DESC
LIMIT 20;Notes
SQLite reports whether it scans a table, searches with an index, or can use a covering index optimization.
sql
ANALYZE;Notes
Statistics can improve plan selection in nontrivial schemas.
bash
sqlite3 app.db ".expert"Notes
The SQLite CLI includes tools that can help surface candidate indexes for sample queries.
More in Database Indexing
Indexing Anti-Patterns and Maintenance
Avoid over-indexing, duplicate indexes, low-value indexes, stale statistics, and maintenance mistakes.
EXPLAIN, ANALYZE, and Verify Index Usage
Use EXPLAIN tools across PostgreSQL, MySQL, SQLite, and MongoDB to prove whether an index actually helps.
Partial, Expression, and Functional Indexes
High-leverage index patterns for subsets of data and computed predicates across PostgreSQL, MySQL, and SQLite.
MongoDB Index Types and Query Patterns
Single-field, compound, multikey, text, wildcard, partial, TTL, and unique index patterns for MongoDB.
MySQL Index Strategies and Verification
Composite indexes, invisible indexes, descending indexes, generated-column indexing, and EXPLAIN workflows in MySQL.
PostgreSQL Index Types and Strategies
Use PostgreSQL B-tree, GIN, GiST, BRIN, partial indexes, expression indexes, and concurrent builds effectively.