EXPLAIN Statement
EXPLAIN shows the query execution plan - how the database intends to execute your query. It reveals whether indexes are being used, how many rows are scanned, and where the bottlenecks are. EXPLAIN ANALYZE actually runs the query and shows real timings.
EXPLAIN Output Reading Guide
- Seq Scan - sequential (full) table scan: often indicates a missing index
- Index Scan - using a B-tree index to find rows
- Index Only Scan - data served from index itself (fastest)
- Bitmap Heap Scan - combines multiple index results then fetches rows
- Nested Loop / Hash Join / Merge Join - how tables are joined
- rows= - estimated row count (affects plan choice)
- cost= - optimizer's estimated cost (relative, not seconds)
- actual time= (ANALYZE only) - real execution time in ms
EXPLAIN Examples
-- Basic EXPLAIN (shows plan without running)
EXPLAIN SELECT * FROM orders WHERE user_id = 1;
-- Before index: Seq Scan on orders (rows=3, cost=0.00..1.03)
-- After index: Index Scan using idx_orders_user_id (rows=2, cost=0.00..8.02)
-- EXPLAIN ANALYZE (runs query + shows real timing)
EXPLAIN ANALYZE SELECT * FROM orders WHERE user_id = 1;
-- Output includes:
-- Seq Scan on orders (actual time=0.019..0.021 rows=2 loops=1)
-- Planning Time: 0.1 ms
-- Execution Time: 0.05 ms
-- EXPLAIN ANALYZE with BUFFERS (shows memory/disk hits)
EXPLAIN (ANALYZE, BUFFERS) SELECT o.*, u.name
FROM orders o JOIN users u ON o.user_id = u.id
WHERE o.status = 'delivered';
-- Reading Output:
-- Hash Join (cost=1.05..2.17 rows=2)
-- Hash Cond: (o.user_id = u.id)
-- -> Seq Scan on orders (filter: status='delivered')
-- -> Hash (Seq Scan on users)
-- Problem: Seq Scan on orders → add index on status or (user_id, status)
-- MySQL EXPLAIN
EXPLAIN SELECT * FROM orders WHERE user_id = 1;
-- Shows: type=ref (index used), key=idx_orders_user_id, rows=2
-- MySQL EXPLAIN FORMAT=JSON (detailed)
EXPLAIN FORMAT=JSON SELECT * FROM orders WHERE user_id = 1;Quick Quiz
Tip
Tip
Practice EXPLAIN Statement in small, isolated examples before integrating into larger projects. Breaking concepts into small experiments builds genuine understanding faster than reading alone.
Always EXPLAIN before optimizing. Seq Scan = add index.
Common Mistake
Warning
A common mistake with EXPLAIN Statement is skipping edge case testing - empty inputs, null values, and unexpected data types. Always validate boundary conditions to write robust, production-ready sql code.
Practice Task
Note
Practice Task - (1) Write a working example of EXPLAIN Statement from scratch without looking at notes. (2) Modify it to handle an edge case (empty input, null value, or error state). (3) Share your solution in the Priygop community for feedback.
Key Takeaways
- EXPLAIN shows the query execution plan - how the database intends to execute your query.
- Seq Scan - sequential (full) table scan: often indicates a missing index
- Index Scan - using a B-tree index to find rows
- Index Only Scan - data served from index itself (fastest)