sql
CREATE INDEX idx_users_email ON users (email);Notes
A classic lookup index for logins, unique checks, or profile fetches.
SQL B-tree, Composite, and Covering Indexes
Practical SQL indexing patterns for equality, range filters, ordering, include columns, and covering scans.
Export
Copy the compact sheet, download it, or print it.
Download
`D` dense toggle · `C` copy all
Create index patterns
Common B-tree patterns that support app queries.
sql
CREATE INDEX idx_orders_status_created_at ON orders (status, created_at DESC);Notes
Useful for queries that filter by `status` and then sort or range on `created_at`.
PostgreSQL covering index with INCLUDE
Store extra columns for index-only scans.
sqlANYpostgresqlincludecovering
sql
CREATE INDEX idx_orders_status_created_at_inc ON orders (status, created_at DESC) INCLUDE (total_amount, user_id);Notes
`INCLUDE` can help PostgreSQL serve some queries directly from the index when conditions are right.
sql
-- Often effective
INDEX (tenant_id, status, created_at)
-- Query
WHERE tenant_id = ? AND status = ? AND created_at >= ?Notes
A strong general pattern is equality predicates first, then range or ordering columns.
Covering and ordering
Use indexes to avoid extra sorting or table lookups.
sql
SELECT id, created_at
FROM orders
WHERE status = 'paid'
ORDER BY created_at DESC
LIMIT 50;Notes
An index on `(status, created_at DESC)` may let the planner filter and return rows in the needed order.
sql
SELECT status, created_at, total_amount
FROM orders
WHERE status = 'paid'
ORDER BY created_at DESC
LIMIT 50;Notes
Covering or index-only strategies are strongest when the query reads only columns present in the index.
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.
SQLite Indexes and Query Planner
SQLite CREATE INDEX patterns, expression and partial indexes, EXPLAIN QUERY PLAN, and lightweight performance tuning.
MySQL Index Strategies and Verification
Composite indexes, invisible indexes, descending indexes, generated-column indexing, and EXPLAIN workflows in MySQL.