MySQL EXPLAIN

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;

References

• ↗ https://dev.mysql.com/doc/refman/8.0/en/explain.html

Tags

Related Terms