MySQL Indexing Strategies for Query Performance
The right indexes turn slow queries into instant ones. Here's how to choose and design them.
Key Insights
- Composite indexes follow the leftmost prefix rule — column order matters
- Covering indexes eliminate table lookups by including all needed columns
- EXPLAIN is your best friend for validating index usage
Composite Index Design
-- For queries filtering on status AND sorting by created_at
CREATE INDEX idx_status_created ON orders(status, created_at DESC);
-- Leftmost prefix: this index also serves queries filtering only on status
SELECT * FROM orders WHERE status = 'active'; -- uses the index
SELECT * FROM orders WHERE created_at > '2024-01-01'; -- does NOT use it
Covering Indexes
-- Include all selected columns to avoid table lookup
CREATE INDEX idx_covering ON users(email, name, created_at);
-- This query is served entirely from the index
SELECT name, created_at FROM users WHERE email = 'test@example.com';