MySQL Indexes and EXPLAIN: A Practical Introduction
Learn when indexes help and how to start reading a query execution plan.
What an index does
An index helps MySQL locate rows without scanning every row. It consumes storage and adds work to inserts, updates and deletes.
Create a composite index
CREATE INDEX idx_orders_customer_createdON orders (customer_id, created_at);
Composite indexes are ordered by their leftmost column. Choose order based on real query patterns.
Inspect a query
EXPLAINSELECT id, total
FROM orders
WHERE customer_id = 42
ORDER BY created_at DESC
LIMIT 20;
Review the chosen key, access type, estimated rows and extra operations.
Tuning workflow
1. Identify a slow query using measurements.
2. Capture SQL and representative parameters.
3. Run EXPLAIN (and EXPLAIN ANALYZE where supported and safe).
4. Review indexes and filtering.
5. Change one thing and benchmark again.
6. Monitor write latency and index size.
Interview practice
Explain a composite index, leftmost-prefix principle, covering index and why indexing every column is usually a poor strategy.
Try the concepts in a small project and verify version-specific details with official documentation.
More MySQL resources