Using EXPLAIN
EXPLAIN SELECT * FROM orders WHERE user_id = 42 AND status = 'active';
EXPLAIN FORMAT=JSON SELECT ...; -- More detailed output
EXPLAIN ANALYZE SELECT ...; -- Actually runs the query with timing
Reading EXPLAIN Output
| Column | Good Values | Bad Values |
|---|
type | const, eq_ref, ref, range | ALL (full table scan) |
key | An index name | NULL (no index used) |
rows | Small number | Close to table row count |
Extra | Using index | Using filesort, Using temporary |
Index Strategies
Single Column Index
CREATE INDEX idx_user_id ON orders(user_id);
Composite Index (Multi-Column)
-- Leftmost prefix rule: index on (a, b, c) works for:
-- WHERE a = 1
-- WHERE a = 1 AND b = 2
-- WHERE a = 1 AND b = 2 AND c = 3
-- Does NOT work for: WHERE b = 2 (missing leftmost column)
CREATE INDEX idx_user_status ON orders(user_id, status);
Covering Index
-- Index contains all columns needed by the query (no table lookup)
CREATE INDEX idx_covering ON orders(user_id, status, total, created_at);
Common Anti-Patterns
-- Bad: function on indexed column prevents index use
SELECT * FROM users WHERE YEAR(created_at) = 2026;
-- Good: use range instead
SELECT * FROM users WHERE created_at >= '2026-01-01' AND created_at < '2027-01-01';
-- Bad: leading wildcard
SELECT * FROM users WHERE email LIKE '%@gmail.com';
-- Good: trailing wildcard (can use index)
SELECT * FROM users WHERE email LIKE 'john%';
-- Bad: SELECT * when you only need a few columns
SELECT * FROM orders WHERE user_id = 42;
-- Good: only select what you need
SELECT id, total, status FROM orders WHERE user_id = 42;
Check Index Usage
-- Show all indexes on a table
SHOW INDEX FROM orders;
-- Check which indexes are actually used
SELECT * FROM sys.schema_unused_indexes;