MySQL EXPLAIN: Reading Query Plans

What is it?

EXPLAIN shows the execution plan — which indexes are used, estimated rows, and extra operations like filesort.

Why does it matter?

EXPLAIN is the most important MySQL diagnostic tool. Reading it confidently is required for any backend developer.

Learn EXPLAIN FORMAT=JSON, EXPLAIN ANALYZE, and identifying bottlenecks.

Real-World Use Cases

💡 Real-world - Practical use.
⚡ Performance - Critical.
🏢 Enterprise - Industry.
📚 Learning - Essential.

Core

-- MySQL EXPLAIN: All Examples in One

-- 1. Full Table Scan (Bad)
EXPLAIN SELECT * FROM users WHERE email = 'a@test.com';
-- type: ALL → no index

-- Fix
CREATE INDEX idx_email ON users(email);

-- 2. Using Index (Good)
EXPLAIN SELECT * FROM users WHERE email = 'a@test.com';
-- type: ref, key: idx_email, rows: 1

-- 3. Filesort Issue
EXPLAIN 
SELECT * FROM orders 
WHERE user_id = 10 
ORDER BY created_at;
-- Extra: Using filesort

-- Fix (composite index)
CREATE INDEX idx_user_created 
ON orders(user_id, created_at);

-- 4. EXPLAIN ANALYZE (real execution)
EXPLAIN ANALYZE 
SELECT * FROM orders WHERE user_id = 10;
-- compare estimated rows vs actual rows

-- 5. Too Many Rows (low selectivity)
EXPLAIN SELECT * FROM orders WHERE status = 'pending';
-- rows: very high

-- Fix
CREATE INDEX idx_status ON orders(status);

Best Practice

✔ Always use EXPLAIN ANALYZE (real data)
✔ Aim for ref or better access type
✔ Keep rows low
✔ Avoid ALL, filesort, temporary
✔ Use composite indexes for WHERE + ORDER BY
✔ Check index selectivity (high cardinality is better)
✔ Rewrite complex queries (JOIN > subquery often)