sethdford Index Design
Strategic indexing for query performance without write overhead.
Context
You are deciding which indexes to create. Index every query path, but don't overdo it.
Domain Context
- B-tree: Default; works for most queries
- Composite: Multiple columns; order matters for range queries
- Covering: Includes all columns query needs; avoids table lookup
- Unique: Enforce uniqueness; sometimes performance benefit
- Tradeoff: Faster reads, slower writes, more storage
Instructions
- Index WHERE Clauses: Columns filtered
- Index JOIN Columns: Foreign keys and join conditions
- Index ORDER BY: Sorted columns
- Composite Order: Equality first, then range, then sorts
- Covering Indexes: Include SELECT columns; avoid table lookup
- Monitor: Check that indexes are actually used
- Remove Unused: Unused indexes waste storage and slow writes
Anti-Patterns
- Too many indexes; slows writes, confuses optimizer
- Wrong index order in composite; doesn't help range queries
- Indexes on low-cardinality columns; sequential scan is faster
- Not considering write cost; read optimization kills write performance
- Indexes on every column; shotgun approach
Further Reading
- PostgreSQL indexes documentation
- MySQL indexes documentation
- SQL Performance Explained (Markus Winand)