✏️ Explanatory Question
Level: Very Hard — The quintessential senior interview question; they want your systematic methodology, not luck.
-- orders table: ~20 million rows
-- The slow dashboard query
SELECT o.customer_id, COUNT(*) AS orders, SUM(o.amount) AS revenue
FROM orders o
WHERE YEAR(o.created_at) = 2026
AND o.status = 'completed'
GROUP BY o.customer_id
ORDER BY revenue DESC
LIMIT 100;type, key, and rows. Look for type=ALL (full scan) and key=NULL.Using filesort and Using temporary are red flags for the GROUP BY / ORDER BY.YEAR(o.created_at) wraps the column in a function, so no index can be used.| type | key | rows | Extra |
|---|---|---|---|
| ALL | NULL | 20,000,000 | Using where; Using temporary; Using filesort |
Full table scan of 20M rows + temp table + filesort = 40 seconds.
-- STEP 1: make the WHERE sargable (remove YEAR() function)
SELECT o.customer_id, COUNT(*) AS orders, SUM(o.amount) AS revenue
FROM orders o
WHERE o.created_at >= '2026-01-01'
AND o.created_at < '2027-01-01'
AND o.status = 'completed'
GROUP BY o.customer_id
ORDER BY revenue DESC
LIMIT 100;
-- STEP 2: add a composite index covering the filter + group column
-- Order: equality (status) first, then range (created_at), then group/agg cols
CREATE INDEX idx_orders_dash
ON orders (status, created_at, customer_id, amount);
-- STEP 3: refresh optimizer statistics
ANALYZE TABLE orders;
-- STEP 4: verify improvement
EXPLAIN SELECT o.customer_id, COUNT(*), SUM(o.amount)
FROM orders o
WHERE o.created_at >= '2026-01-01' AND o.created_at < '2027-01-01'
AND o.status = 'completed'
GROUP BY o.customer_id ORDER BY SUM(o.amount) DESC LIMIT 100;| type | key | rows | Extra |
|---|---|---|---|
| range | idx_orders_dash | ~180,000 | Using where; Using index |
From 20M rows scanned to ~180K, "Using index" (covering), no filesort → ~200ms.
YEAR() function made the query non-sargable, and (2) there was no supporting index. As the table grew, the full scan cost grew linearly — 200ms at 500K became 40s at 20M.