/mysql

EXPLAIN Analysis

EXPLAIN SELECT ...;                    -- estimated plan
EXPLAIN FORMAT=JSON SELECT ...;        -- detailed with cost estimates
EXPLAIN FORMAT=TREE SELECT ...;        -- tree format (8.0+)
EXPLAIN ANALYZE SELECT ...;            -- actual execution (8.0.18+, runs the query, uses TREE format)

Access Types (Best → Worst)

system → const → eq_ref → ref → range → index (full index scan) → ALL (full table scan)

Target ref or better. ALL on >1000 rows almost always needs an index.

Key Extra Flags

key_len — How Much of Composite Index Is Used

Byte sizes: TINYINT=1, INT=4, BIGINT=8, DATE=3, DATETIME=5, VARCHAR(N) utf8mb4: N×4+1 (or +2 when N×4>255). Add 1 byte per nullable column.

-- Index: (status TINYINT, created_at DATETIME)
-- key_len=2 → only status (1+1 null). key_len=8 → both columns used.

rows vs filtered

• rows: estimated rows examined after index access (before additional WHERE filtering)
• filtered: percent of examined rows expected to pass the full WHERE conditions
• Rough estimate of rows that satisfy the query: rows × filtered / 100
• Low filtered often means additional (non-indexed) predicates are filtering out lots of rows

Join Order

Row order in EXPLAIN output reflects execution order: the first row is typically the first table read, and subsequent rows are joined in order. Use this to spot suboptimal join ordering (e.g., starting with a large table when a selective table could drive the join).

EXPLAIN ANALYZE

Availability: MySQL 8.0.18+

Important: EXPLAIN ANALYZE actually executes the query (it does not return the result rows). It uses FORMAT=TREE automatically.

Metrics (TREE output):

• actual time: milliseconds (startup → end)
• rows: actual rows produced by that iterator
• loops: number of times the iterator ran

Compare estimated vs actual to find optimizer misestimates. Large discrepancies often improve after refreshing statistics:

ANALYZE TABLE your_table;

Limitations / pitfalls:

• Adds instrumentation overhead (measurements are not perfectly "free")
• Cost units (arbitrary) and time (ms) are different; don't compare them directly
• Results reflect real execution, including buffer pool/cache effects (warm cache can hide I/O problems)