Reading MySQL EXPLAIN Output: A Practical Guide

EXPLAIN shows the query execution plan chosen by the MySQL optimizer. It is the first tool to reach for when a query is slow — it reveals which indexes are used, how many rows are estimated, and whether expensive operations like filesorts or temporary tables are required. Understanding EXPLAIN output is the core skill for MySQL query optimization.

Running EXPLAIN

-- Basic plan
EXPLAIN SELECT * FROM orders WHERE customer_id = 42 ORDER BY created_at DESC LIMIT 10;

-- JSON format — more detail, including cost estimates
EXPLAIN FORMAT=JSON SELECT ...;

-- Tree format (MySQL 8.0.16+) — readable nested plan
EXPLAIN FORMAT=TREE SELECT ...;

-- EXPLAIN ANALYZE (MySQL 8.0.18+) — actually runs the query and shows real vs. estimated rows
EXPLAIN ANALYZE SELECT ...;

The EXPLAIN Output Columns

id

The query block number. Each SELECT has its own id. Higher id = inner/subquery (executed first). When rows share the same id, they are part of a JOIN and are processed together.

select_type

How this SELECT relates to the overall query:

• SIMPLE — no subqueries or UNIONs
• PRIMARY — outermost SELECT in a UNION or subquery
• SUBQUERY — first SELECT in a subquery (in the WHERE clause)
• DERIVED — a subquery in the FROM clause (materialized into a temp table)
• UNION — second or subsequent SELECT in a UNION
• DEPENDENT SUBQUERY — subquery that references the outer query (runs once per outer row — often very slow)

table

The table being read. <derived2> means the result of query block id=2 (a derived table). <union1,2> means the UNION of blocks 1 and 2.

partitions

Which partitions are accessed. NULL for non-partitioned tables.

type — the most important column

The access method. Best to worst:

ALL and index on large tables are almost always problems. range, ref, and eq_ref are good. const is ideal.

possible_keys

Indexes MySQL considered but might not use. NULL means no index is relevant.

key

The index actually chosen. NULL means no index is used (combined with type: ALL = full table scan).

key_len

Bytes of the chosen index used. For composite indexes, a shorter key_len means fewer columns of the index were used. This tells you which prefix of a composite index the optimizer matched.

-- Example: index on (status CHAR(1), customer_id INT, created_at DATETIME)
-- key_len = 1 → only status used
-- key_len = 5 → status + customer_id used (1 + 4)
-- key_len = 13 → all three columns used (1 + 4 + 8)

ref

What is being compared to the key: a constant (const), another column (table.column), or a function.

rows

Estimated number of rows examined. For JOINs, multiply the rows across all tables in the plan to get the total estimated row combinations examined. A product of 10M is a bad plan.

filtered

Percentage of rows the storage engine returns that pass the WHERE filter. rows × filtered / 100 = rows passed to the next step. Low filtered (e.g., 1%) combined with high rows means the server is reading many rows to find a few.

Extra — the second most important column

Additional information about the query execution:

Red flags:

• Using filesort on a large result — add a covering index that includes the ORDER BY columns
• Using temporary — redesign the query or add indexes to allow in-memory sort
• Using join buffer — add an index on the join column of the inner table

Reading a Multi-Table EXPLAIN

EXPLAIN
SELECT o.id, c.name, oi.product_id, oi.qty
FROM orders o
JOIN customers c ON c.id = o.customer_id
JOIN order_items oi ON oi.order_id = o.id
WHERE o.status = 'pending'
ORDER BY o.created_at;

Example output:

+----+-------------+-------+--------+-------------------+--------------+---------+------------------+------+-------+
| id | select_type | table | type   | possible_keys     | key          | key_len | ref              | rows | Extra |
+----+-------------+-------+--------+-------------------+--------------+---------+------------------+------+-------+
|  1 | SIMPLE      | o     | ref    | idx_status        | idx_status   | 1       | const            |  250 | Using filesort |
|  1 | SIMPLE      | c     | eq_ref | PRIMARY           | PRIMARY      | 4       | mydb.o.customer_id| 1  |       |
|  1 | SIMPLE      | oi    | ref    | idx_order_id      | idx_order_id | 4       | mydb.o.id        |   3 |       |
+----+-------------+-------+--------+-------------------+--------------+---------+------------------+------+-------+

Reading this:

1. o is the driving table — 250 rows estimated in pending status (ref on idx_status)
2. For each of those 250 rows, c is looked up by primary key (eq_ref — fast)
3. For each of those 250 rows, oi fetches ~3 rows by index on order_id
4. Total estimated row combinations: 250 × 1 × 3 = 750
5. Using filesort on o — the ORDER BY created_at cannot use the existing idx_status index

Fix the filesort by creating a composite index:

ALTER TABLE orders ADD INDEX idx_status_created (status, created_at);

EXPLAIN ANALYZE (MySQL 8.0.18+)

EXPLAIN ANALYZE runs the query and reports actual rows, actual loops, and actual time alongside the estimates:

EXPLAIN ANALYZE
SELECT * FROM orders WHERE customer_id = 42 ORDER BY created_at DESC LIMIT 10;

Output (FORMAT=TREE style):

-> Limit: 10 row(s)  (actual time=0.523..0.524 rows=10 loops=1)
    -> Sort: orders.created_at DESC  (actual time=0.521..0.522 rows=10 loops=1)
        -> Filter: (orders.customer_id = 42)  (actual time=0.089..0.501 rows=127 loops=1)
            -> Table scan on orders  (actual time=0.084..0.461 rows=50000 loops=1)

The actual rows=127 vs rows=50000 scanned tells you: a full table scan read 50,000 rows to find 127 for customer 42. Fix: add INDEX idx_customer_id (customer_id, created_at DESC).

Key numbers from EXPLAIN ANALYZE:

• actual rows vs. estimated rows — large divergence means stale statistics
• actual time=start..end — the start time is the time to get the first row, end is for all rows
• loops — how many times this node was executed (high loops on inner tables = nested-loop join concern)

Common Patterns and Fixes

-- Pattern: Extra = "Using filesort" + type = ALL
-- Cause: No usable index for ORDER BY, full scan
-- Fix: Add an index on the ORDER BY column(s) that also covers the WHERE clause

-- Pattern: type = ALL + rows = (table row count)
-- Cause: Full table scan — no WHERE or no usable index
-- Fix: Add an index on the WHERE predicate columns

-- Pattern: Extra = "Using temporary; Using filesort" on GROUP BY
-- Cause: GROUP BY cannot use an index
-- Fix: Add an index on the GROUP BY column(s) (leftmost prefix of a composite index)

-- Pattern: type = index + key = PRIMARY
-- Cause: Full index scan (reads entire clustered index in order) — slightly better than ALL
-- but still reads every row
-- Fix: Add a WHERE condition, or a secondary index that supports a range scan

-- Pattern: rows product across JOIN tables >> actual result rows
-- Cause: Bad join order or missing index on join column
-- Fix: Add index on the join column of the inner table; optimizer may reorder joins

Additional Information

• EXPLAIN does not run the query (except EXPLAIN ANALYZE). The rows column is an estimate that can be wrong by orders of magnitude on tables with stale statistics. Run ANALYZE TABLE tablename to refresh statistics before relying on EXPLAIN output.
• EXPLAIN FORMAT=JSON includes the cost_info block with the optimizer's cost estimates for each step. This is useful for understanding why the optimizer chose one plan over another.
• MySQL 8.0's optimizer_trace (SET SESSION optimizer_trace = "enabled=on") produces a complete JSON trace of the decision process, including all evaluated access paths and their costs. This is the authoritative source for understanding optimizer choices.
• EXPLAIN for DML (EXPLAIN INSERT ... SELECT, EXPLAIN UPDATE, EXPLAIN DELETE) shows the plan for the SELECT part of the statement.

Frequently Asked Questions

Q: EXPLAIN shows rows=1 but the query takes 5 seconds. What's happening? A: The rows estimate can be wrong. Use EXPLAIN ANALYZE to see actual rows. The slowness may also be in lock waits — check Lock_time in the slow query log.

Q: My query has type=ref but is still slow. Is that a problem with ref? A: ref is generally good but depends on selectivity. A ref scan returning 500,000 rows from a low-cardinality index (e.g., status IN ('active', 'pending') on a mostly-active table) is effectively a full scan. Check rows and filtered.

Q: EXPLAIN shows key=NULL but possible_keys lists an index. Why? A: The optimizer considered the index but estimated a full table scan to be cheaper. Common causes: stale statistics (run ANALYZE TABLE), or the predicate returns too many rows for the index to help. Check key_len — if it's less than expected, the index is only partially matched.

Q: What does key_len tell me about my composite index? A: key_len is the total bytes of the index used. Each column contributes based on its type: INT = 4 bytes, BIGINT = 8 bytes, DATETIME = 8 bytes, VARCHAR(N) utf8mb4 = N×4+2 bytes (nullable adds 1). A key_len shorter than the full index means not all columns are being used — check whether the WHERE clause covers all prefix columns.

Q: Is Using index condition good or bad? A: Good — it means Index Condition Pushdown (ICP) is active, evaluating filter conditions inside the storage engine on index records before fetching the full row. It reduces the number of full-row reads. ICP is used when part of the WHERE can be evaluated on index data before returning to the table.