EXPLAIN & Query Optimization

EXPLAIN SELECT e.name, d.dept_name
FROM employees e
JOIN departments d ON e.dept_id = d.dept_id
WHERE e.salary > 70000;

system   → single row in MyISAM/MEMORY (best)
const    → at most one matching row (PK/unique lookup)
eq_ref   → one row per join row (PK/unique join)
ref      → multiple rows per join (non-unique index)
range    → index range scan (BETWEEN, <, >, IN)
index    → full index scan (better than ALL)
ALL      → full table scan (WORST — avoid!)

-- Use EXPLAIN ANALYZE (MySQL 8.0.18+) for actual execution stats
EXPLAIN ANALYZE SELECT * FROM employees WHERE dept_id = 1;

-- Use FORMAT=JSON for more detail
EXPLAIN FORMAT=JSON SELECT * FROM employees WHERE salary > 50000;

-- ❌ BAD: type = ALL (full table scan)
EXPLAIN SELECT * FROM employees WHERE YEAR(hire_date) = 2020;

-- ✅ FIX: use range condition for index
EXPLAIN SELECT * FROM employees
WHERE hire_date BETWEEN '2020-01-01' AND '2020-12-31';

-- ❌ BAD: Extra = "Using filesort" on large tables
EXPLAIN SELECT * FROM employees ORDER BY salary;
-- Fix: Add index on salary

-- ❌ BAD: Extra = "Using temporary"
EXPLAIN SELECT dept_id, COUNT(*) FROM employees GROUP BY dept_id;
-- Fix: Add index on dept_id