Reading a MySQL EXPLAIN Plan

Introduction¶

EXPLAIN prefixed to a SELECT (or UPDATE/DELETE) shows the optimizer's execution plan: which indexes it'll use, join order, and estimated row counts. It's the first thing to run on any slow query.

The tabular output¶

EXPLAIN SELECT o.id, c.name
FROM orders o JOIN customers c ON c.id = o.customer_id
WHERE o.status = 'shipped' AND o.created_at > '2026-08-01';

id  select_type  table  type   possible_keys        key            key_len  ref              rows   filtered  Extra
1   SIMPLE       o      range  idx_status_created   idx_status_created  5    NULL             1200   100.00    Using index condition
1   SIMPLE       c      eq_ref PRIMARY              PRIMARY            4     shop.o.customer_id  1    100.00    NULL

Read it top to bottom = join order. For each row:

type — the access method (best to worst)¶

ALL on a big table is the red flag. index is only slightly better. You want const, eq_ref, ref, or range.

The other columns¶

• possible_keys — indexes that could be used. Empty = no useful index exists for this predicate.
• key — the index actually chosen. NULL with type: ALL = table scan.
• key_len — how many bytes of the index are used. Shorter than expected means a multi-column index is only partly usable (a middle column isn't constrained).
• rows — estimated rows examined at this step. Multiply down the join to estimate total work. A number wildly off from reality = stale statistics (ANALYZE TABLE).
• filtered — % of rows expected to survive the WHERE after the index. Low filtered with high rows means the index gets you to a big chunk and then most is thrown away — a better index would help.
• Extra — the important notes:
• Using index — covering index, the query is answered from the index alone, no table lookup. Good.
• Using index condition — index condition pushdown; ok.
• Using where — filtering after reading rows; fine unless combined with a big rows.
• Using filesort — a sort that isn't served by an index. Ok for small results, expensive for large ones + LIMIT.
• Using temporary — a temp table (often GROUP BY/DISTINCT not matching an index). Expensive.
• Using join buffer (Block Nested Loop) — joining without an index on the join column. Add the index.

EXPLAIN ANALYZE (8.0.18+)¶

Actually runs the query and shows real timings and row counts vs estimates:

EXPLAIN ANALYZE SELECT ...;

-> Nested loop inner join  (cost=880 rows=1200) (actual time=0.05..14.2 rows=1180 loops=1)
    -> Index range scan on o using idx_status_created  (actual time=0.03..2.1 rows=1180 loops=1)
    -> Single-row index lookup on c using PRIMARY  (actual time=0.009..0.009 rows=1 loops=1180)

actual time=X..Y is first-row..last-row ms; loops is how many times that sub-plan ran. A step where actual rows ≫ estimated rows is where the optimizer went wrong (run ANALYZE TABLE).

FORMAT=JSON¶

EXPLAIN FORMAT=JSON SELECT ...\G

Verbose but shows cost_info, used_key_parts, attached_condition, and which part of a composite index each column matched — invaluable for tuning a multi-column index.

Verification and troubleshooting¶

ANALYZE TABLE orders;                    -- refresh index statistics
SHOW INDEXES FROM orders;
SET optimizer_trace = 'enabled=on'; SELECT ...; SELECT * FROM
  information_schema.optimizer_trace\G   -- why it chose that plan

• type: ALL on a filtered query — no index on the WHERE/JOIN column, or the index exists but a function/implicit cast defeats it (WHERE DATE(created_at) = ..., or comparing a VARCHAR column to a number). Rewrite so the column is bare on one side.
• Right index exists but key picks a different / no index — stale stats (ANALYZE TABLE), low selectivity (the optimizer thinks a scan is cheaper), or a type mismatch. FORCE INDEX to test the hypothesis, then fix the root cause.
• Using filesort on an ORDER BY ... LIMIT 10 — add an index matching the ORDER BY (and any equality WHERE columns first) so MySQL reads rows already sorted and stops at 10.
• Using temporary; Using filesort on GROUP BY — the group columns don't match an index prefix. A composite index on the group + aggregate columns can remove both.
• rows estimate looks fine but the query is still slow — the estimate is wrong; use EXPLAIN ANALYZE to see actuals, or check for a huge loops count (a nested loop over many rows with a per-row subquery).
• Composite index (a,b,c) only using a — key_len confirms it. b isn't an equality/range in the query, or there's an OR. Reorder the index or the predicates.

Related tools and reading¶

• On-site: JSON formatter (for FORMAT=JSON output).
• Related posts: MySQL slow query log analysis, InnoDB buffer pool sizing.