Query Plan & EXPLAIN Analysis
debt(d7/e3/b3/t5)
Closest to 'only careful code review or runtime testing' (d7) — slow queries surface via monitoring (pt-query-digest, pganalyze) or runtime testing; no compiler or default linter flags a missing-index query.
Closest to 'simple parameterised fix' (e3) — quick_fix is running EXPLAIN ANALYZE and adding an index or rewriting the query; typically localized changes to one query plus a migration.
Closest to 'localised tax' (b3) — query plan analysis is a per-query concern; knowledge of EXPLAIN slightly taxes the data-access layer but doesn't shape system architecture.
Closest to 'notable trap' (t5) — per the misconception, EXPLAIN syntax and output differ across MySQL/PostgreSQL/MariaDB; also dev-dataset plans differ from prod, a well-documented gotcha most DB devs eventually learn.
Also Known As
TL;DR
Explanation
Every SQL query is compiled into an execution plan by the query optimiser. EXPLAIN (MySQL/PostgreSQL) shows this plan: table access type (const, ref, range, index, ALL — ALL is a full table scan and nearly always a problem), estimated row counts, indexes used, join strategy, and temp table/filesort usage. EXPLAIN ANALYZE (PostgreSQL) or EXPLAIN FORMAT=JSON (MySQL 8) shows actual execution statistics. Key red flags: type=ALL on large tables, Using filesort, Using temporary, and high rows_examined vs rows_returned ratios. Run EXPLAIN before adding indexes — the plan tells you exactly which index to create.
Common Misconception
Why It Matters
Common Mistakes
- Not using EXPLAIN before adding indexes — the plan shows whether an index would actually be used.
- Reading EXPLAIN output without checking actual row counts — estimated vs actual rows reveals stale statistics.
- Not using EXPLAIN ANALYZE (PostgreSQL) or EXPLAIN FORMAT=JSON (MySQL) for full detail.
- Optimising based on EXPLAIN on a small dev dataset — plans change dramatically with production data volumes.
Code Examples
-- Running slow query without checking the plan:
SELECT * FROM orders o JOIN users u ON o.user_id = u.id WHERE o.status = 'pending';
-- Just add EXPLAIN first:
EXPLAIN SELECT * FROM orders o JOIN users u ON o.user_id = u.id WHERE o.status = 'pending';
-- Look for: Seq Scan (bad on large tables), Nested Loop with high rows-- MySQL EXPLAIN
EXPLAIN SELECT * FROM orders
WHERE user_id = 42 AND status = 'paid'
ORDER BY created_at DESC LIMIT 10;
-- Look for: type (ALL=bad, ref/range=ok, const=best), key (index used), rows estimate
-- PostgreSQL EXPLAIN ANALYZE (actually executes)
EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT)
SELECT * FROM orders
WHERE user_id = 42 AND status = 'paid'
ORDER BY created_at DESC LIMIT 10;
-- Look for: Seq Scan on large tables, high rows estimate vs actual, cache hits
-- Paste output into explain.dalibo.com for visual analysis