MySQL EXPLAIN
debt(d9/e3/b3/t7)
Closest to 'silent in production until users hit it' (d9). The detection_hints state automated: no, and the code_pattern relies on slow query log entries — meaning missing EXPLAIN usage (or misreading its output) produces no warnings, no linter signals, and no tooling alerts. Performance degradation from unanalyzed queries is only noticed when users experience slowness in production.
Closest to 'simple parameterised fix' (e3). The quick_fix is a single EXPLAIN SELECT invocation, but acting on the results (adding an index or rewriting a query) typically involves a small, localized change — one or a few files/migrations. It rarely requires cross-cutting refactors, so e3 is appropriate.
Closest to 'localised tax' (b3). EXPLAIN is a diagnostic tool rather than a structural choice baked into the codebase. Its burden is localized: the developer must remember to run it and interpret it for slow queries, but it doesn't impose a persistent tax on every future maintainer or every code change.
Closest to 'serious trap' (t7). The misconception field is explicit: developers assume EXPLAIN shows actual execution, but standard EXPLAIN only shows estimated plans. This directly contradicts how EXPLAIN ANALYZE (PostgreSQL) works by default, and many developers coming from other databases or newer MySQL versions are misled into trusting row estimates as actuals — a serious behavioral contradiction.
Also Known As
TL;DR
Explanation
EXPLAIN prefixed to a SELECT/UPDATE/DELETE shows the query execution plan. Key columns: 'type' (ALL = full scan, ref = index lookup, eq_ref = unique index — lower is better), 'key' (which index was chosen), 'rows' (estimated rows examined), 'Extra' (Using filesort, Using temporary — both indicate optimisation opportunities). EXPLAIN ANALYZE (MySQL 8+) executes the query and shows actual vs estimated row counts.
Common Misconception
Why It Matters
Common Mistakes
- Not running EXPLAIN before adding an index — the index may already exist or may not be used.
- Ignoring the 'Extra' column — 'Using filesort' and 'Using temporary' are expensive operations.
- Running EXPLAIN ANALYZE on a slow write query in production — it executes the query, causing the write.
Avoid When
- Do not run EXPLAIN ANALYZE on write queries (INSERT/UPDATE/DELETE) in production — it actually executes them.
When To Use
- Run EXPLAIN on any query that feels slow or affects many rows.
- Run EXPLAIN before and after adding an index to verify it is actually used.
Code Examples
-- Debugging slow query by guessing
-- Adding indexes blindly without checking EXPLAIN first-- Check execution plan
EXPLAIN SELECT * FROM orders WHERE user_id = 42 AND status = 'pending';
-- Look for: type=ref or better, key=idx_user_status, rows=small number
-- MySQL 8+: actual execution stats
EXPLAIN ANALYZE SELECT * FROM orders WHERE user_id = 42;