Reading an EXPLAIN Plan
Interpreting scan types, join methods, and cost estimates in a query plan.
Why Interviewers Ask About EXPLAIN
Once you reach a senior screen, interviewers stop asking write a query and start asking why is this query slow. The tool that answers that is EXPLAIN.
EXPLAIN shows the database's execution plan: the step-by-step strategy the planner chose to run your SQL. It reveals which tables are scanned, in what order they are joined, and roughly how expensive each step is.
Being able to read a plan signals that you understand the engine, not just the syntax. That is exactly the line interviewers use to separate mid-level from senior.
EXPLAIN vs EXPLAIN ANALYZE
There are two flavors and interviewers love the distinction.
- EXPLAIN shows the planner's estimated plan without running the query. Fast and safe.
- EXPLAIN ANALYZE actually executes the query and reports the real row counts and timings alongside the estimates.
The gold mine is comparing estimated rows to actual rows. A big mismatch means the planner has bad statistics and is likely making a poor choice.
Caution: EXPLAIN ANALYZE runs the query for real, so it will perform any INSERT or UPDATE unless wrapped in a rolled-back transaction.
EXPLAIN ANALYZE
SELECT * FROM orders WHERE customer_id = 42;