Database Index Optimize Workflow
Analyze slow queries, identify missing or redundant indexes, and apply changes with minimal impact on write performance.
Objective
Improve database query performance through strategic index optimization.
Environment
- A database with production-like data volume
- Slow query log or query analyzer access
- Database migration tool for index changes
Steps
1. Identify slow queries
Analyze the slow query log to find queries taking more than 100ms.
Expected state: A list of slow queries with their execution plans is compiled.
2. Analyze existing indexes
Review current indexes and identify redundant or unused indexes.
Expected state: A list of unused and redundant indexes is documented.
3. Create missing indexes
Add indexes for slow queries that lack appropriate index support.
Expected state: New indexes are created via migration.
4. Drop unused indexes
Remove indexes that are never used to reduce write overhead.
Expected state: Unused indexes are dropped via migration.
5. Verify improvement
Re-run slow queries to confirm improved execution times.
Expected state: Previously slow queries now execute in under 50ms.
Decision points
Composite or individual indexes?
- Composite for multi-column queries
- Individual for simplicity
Use composite indexes for frequently co-queried columns; individual indexes for single-column lookups.
Expected outcome
Database query performance is improved, and the index set is optimized for the current workload.
Verification
Compare query execution times before and after; confirm no query regressed.
Cleanup
Roll back index migrations if write performance degrades significantly.
Indexes trade read speed for write speed
Every index speeds up reads but slows down writes. Optimize for the actual query workload, not theoretical usage.
EXPLAIN ANALYZE SELECT * FROM orders WHERE customer_id = 123;
CREATE INDEX idx_orders_customer ON orders(customer_id, created_at);Implementation Notes
This workflow provides a structured approach to ensure consistent results across team members and environments.
Common Pitfalls
Skipping verification steps or rushing through the process can lead to incomplete results. Always follow each step in order and verify before proceeding.