EXPLAIN helps reveal how a database plans to execute a query. Plans can show access paths, join order, filters, estimates, and sometimes actual runtime measurements. Read the plan in context: syntax and output differ by engine, and a plan alone is not proof that a query is fast or slow.
Key Takeaways at a Glance:
- •EXPLAIN reveals the physical execution plan chosen by the Cost-Based Optimizer without running the query.
- •EXPLAIN ANALYZE (Postgres/MySQL 8+) executes the query and reports actual runtime durations and memory usage alongside estimated costs.
- •Access types (worst to best): ALL (full table scan) -> index (full index scan) -> range (index range scan) -> ref (non-unique index seek) -> eq_ref (unique join seek) -> const (primary key lookup).
- •Red Flags: 'Using filesort' (disk/memory sort), 'Using temporary' (temp table created), and 'type: ALL' on large tables.
In Simple Words (The Mental Model)
EXPLAIN is like asking a GPS: 'Show me the route you plan to take before I start driving'. The GPS tells you: 'I will take Highway 1 (Index Seek) for 5 miles, then turn onto Elm Street (Filter)'. If the GPS says: 'I will drive through every single alleyway in the country' (Full Table Scan), you know you need to build a new highway (add an index)!
Real-World Analogy
Think of an architectural blueprint. Before pouring concrete, the contractor inspects the blueprint to verify load-bearing walls and pipe routing. EXPLAIN lets you inspect the database execution blueprint before a query crashes your production cluster.