EXPLAIN shows the plan the optimiser chose without running the query. It is the difference between fixing a slow query and changing things until it gets faster, and it is the first thing to run - before adding an index, before rewriting anything.
Four columns carry most of the information. type is the access method, and it is a ranking: const and eq_ref are single-row lookups, ref and range use an index, index scans the whole index, and ALL is a full table scan - the one to look for. key names the index actually used, which is frequently NULL when you expected otherwise. rows is the optimiser's estimate of rows examined; compare it with the rows returned, and a large gap is the problem. Extra carries the warnings: Using filesort means a sort the index could not provide, Using temporary means a temp table, and Using index is the good one - a covering index.
EXPLAIN ANALYZE goes further by actually running the query and reporting real timings next to the estimates, which is how you catch an optimiser whose statistics are stale.
The fixes, in the order worth trying: remove functions from around indexed columns in the WHERE; add or extend an index so the filter and the sort are both served; select fewer columns so an index can cover the query; and rewrite correlated subqueries as joins. If the estimate is wildly wrong, ANALYZE TABLE refreshes the statistics the optimiser is reasoning from.
-- Always start here, before changing anything.
EXPLAIN
SELECT o.reference, o.total
FROM orders o
WHERE o.customer_id = 7
ORDER BY o.placed_at DESC
LIMIT 20;
-- type: ALL -> full table scan, the thing to fix
-- key: NULL -> no index used
-- rows: 2,400,000 -> examined, to return 20
-- Extra: Using filesort -> and then sorted them all
-- The fix: one index serving both the filter and the sort.
CREATE INDEX idx_orders_customer_placed ON orders (customer_id, placed_at);
-- type: ref, key: idx_orders_customer_placed, rows: 31, no filesort.
-- Real timings beside the estimates - which exposes stale statistics.
EXPLAIN ANALYZE
SELECT c.city, COUNT(*) FROM customers c
JOIN orders o ON o.customer_id = c.id
GROUP BY c.city;
-- When the estimate is wildly wrong, refresh what it reasons from.
ANALYZE TABLE orders;
-- Find the slow queries in the first place.
SELECT digest_text, count_star,
ROUND(avg_timer_wait / 1e9) AS avg_ms,
ROUND(sum_rows_examined / count_star) AS rows_per_call
FROM performance_schema.events_statements_summary_by_digest
ORDER BY avg_timer_wait DESC
LIMIT 10;