Demystifying MySQL EXPLAIN FORMAT=TREE: Read Query Plans Like a DBA

A reporting query on an e-commerce platform was taking 28 seconds — slow enough that the team had already added indexes, rewritten joins, and bumped the buffer pool. Nothing helped. The traditional EXPLAIN output showed type: ref and indexes being used across every row, so on paper the query looked fine. It was only after running EXPLAIN FORMAT=TREE that the problem became visible: a nested loop deep inside the plan was iterating 4.2 million rows per outer row, the result of a forgotten cross join in a subquery that the tabular format had hidden behind an aggregate. In five seconds of reading a tree output, the team found what hours of debugging couldn't. EXPLAIN FORMAT=TREE is the query plan format MySQL 8.0 should have shipped with from day one — and if you're not using it, you're flying blind on complex queries.

Why EXPLAIN FORMAT=TREE?

MySQL has supported EXPLAIN since version 3.x. For most of that history, the only output format was the classic tabular format — a row per table in the query, with columns like type, key, rows, and Extra. MySQL 5.6 added FORMAT=JSON, which exposed more detail but became unwieldy to read on anything beyond a two-table join. MySQL 8.0.16 introduced FORMAT=TREE, and MySQL 8.0.18 added EXPLAIN ANALYZE (which uses the TREE format plus real timing data).

FORMAT=TRADITIONAL vs FORMAT=JSON vs FORMAT=TREE

The three formats expose different levels of detail and are suited to different tasks:

The key advantage of TREE over TRADITIONAL is that it reveals the actual execution order. TRADITIONAL shows one row per table in the order they appear in the query — not the order MySQL executes them. TREE shows the real iterator hierarchy the optimizer produced, making join reordering, subquery flattening, and derived table materialization immediately visible.

How to Run It

-- Static plan (no query execution)
EXPLAIN FORMAT=TREE
SELECT o.id, c.name, SUM(oi.quantity * oi.unit_price) AS total
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 = 'completed'
GROUP BY o.id, c.name;

-- With real timing (executes the query)
EXPLAIN ANALYZE
SELECT o.id, c.name, SUM(oi.quantity * oi.unit_price) AS total
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 = 'completed'
GROUP BY o.id, c.name;

How to Read the Tree Format

The TREE output uses indentation to represent the execution hierarchy. Every node starts with ->. Deeper indentation means earlier execution — the query engine evaluates the innermost nodes first and passes rows upward to their parent iterators.

Basic Structure

EXPLAIN FORMAT=TREE
SELECT u.name, COUNT(p.id) AS post_count
FROM users u
LEFT JOIN posts p ON p.user_id = u.id
GROUP BY u.id\G

-> Table scan on   (cost=2.50..4.87 rows=3)
    -> Aggregate using temporary table
        -> Left hash join (p.user_id = u.id)  (cost=3.75 rows=3)
            -> Table scan on u  (cost=0.55 rows=3)
            -> Hash
                -> Table scan on p  (cost=0.65 rows=6)

Reading this plan top-down describes the output: a table scan on a temporary table fed by an aggregate, which is fed by a left hash join between u and p. Reading bottom-up describes execution order: first scan p, build a hash table, then scan u and probe the hash, aggregate, then scan the temp table to return results.

The Arrow Convention

Every line beginning with -> is an iterator. Iterators are the fundamental unit of MySQL 8.0's Volcano-model execution engine. Each iterator has a GetNext() method that pulls one row at a time from its child iterators. The tree shape exactly mirrors this call graph.

Key Iterator Types

MySQL 8.0's TREE format uses precise iterator names that map directly to execution strategy. Recognizing these names is the core skill of reading query plans.

Table scan

A full table scan — every row is read. This is the most expensive access method for large tables and the one you most want to eliminate.

-> Table scan on orders  (cost=1240.00 rows=12400)

A table scan at the outer level of a nested loop is catastrophic — it runs once per row from the parent iterator. A table scan on a small lookup table may be perfectly fine.

Index scan / Index lookup

Index-based access appears in several forms:

-- Full index scan (ordered, no filter)
-> Index scan on orders using idx_created_at  (cost=45.00 rows=1240)

-- Index range scan (WHERE clause hits index range)
-> Index range scan on orders using idx_status_created  (cost=12.50 rows=340)
    over ('completed','2024-01-01') <= (status,created_at) <= ('completed','2024-12-31')

-- Single-row lookup (equality on unique index)
-> Single-row index lookup on customers using PRIMARY (id=o.customer_id)  (cost=0.25 rows=1)

Filter

A Filter iterator applies a predicate that could not be pushed into the index. It wraps another iterator and discards rows that do not match.

-> Filter: (o.total_amount > 500)  (cost=245.00 rows=124)
    -> Index range scan on orders using idx_status  (cost=245.00 rows=1240)

A filter that discards 90% of rows (rows=1240 in, rows=124 out) is a signal that the index does not include the filtered column. Adding a composite index on (status, total_amount) could push the filter into the index scan and eliminate the separate Filter iterator.

Nested loop join

The classic join strategy: for each row from the outer (left) side, probe the inner (right) side. Efficient when the inner side has an index lookup that returns few rows.

-> Nested loop inner join  (cost=5.50 rows=12)
    -> Index range scan on orders using idx_status  (cost=2.50 rows=12)
    -> Single-row index lookup on customers using PRIMARY (id=o.customer_id)  (cost=0.25 rows=1)

Hash join

Hash join builds a hash table from one side and probes it with the other. Used when no suitable index exists for the join condition. Introduced in MySQL 8.0.18 as the fallback for non-indexed joins.

-> Inner hash join (oi.product_id = p.id)  (cost=189.00 rows=450)
    -> Table scan on oi  (cost=10.00 rows=450)
    -> Hash
        -> Table scan on p  (cost=5.00 rows=100)

Sort and Temporary Table

-> Sort: orders.created_at DESC  (cost=...)
    -> Table scan on 
        -> Aggregate using temporary table
            -> ...

A sort or temporary table at the top of a large subtree often indicates a missing index for ORDER BY or GROUP BY. If the sort is on an indexed column and the index covers the query, MySQL should use an index scan instead.

Understanding Cost and Row Estimates in the Output

Each node in the TREE output carries a cost annotation: (cost=X rows=Y). These are optimizer estimates, not actual measurements. The cost unit is arbitrary — it represents relative I/O and CPU effort calibrated against MySQL's cost constants.

How to Use Cost Estimates

Cost estimates are most useful for comparison, not absolute evaluation. When you see two alternative plans (e.g., after adding an index), compare the top-level cost. A plan with cost=12.50 vs cost=1240.00 at the root node is a 99x improvement estimate.

The rows estimate shows how many rows the optimizer expects that iterator to produce. Multiplying rows across a nested loop reveals total work: if the outer side estimates 10,000 rows and the inner side estimates 500 rows per outer row, the nested loop does 5,000,000 inner lookups. That number explains slow queries even when every individual lookup hits an index.

When Estimates Are Wrong

Optimizer estimates depend on table statistics. Stale statistics — from bulk loads, large deletes, or rapidly changing data — produce bad estimates. If the plan looks correct but the query is slow, run ANALYZE TABLE orders to refresh statistics, then re-examine the plan. EXPLAIN ANALYZE shows both estimated and actual row counts, making it straightforward to identify estimate errors.

Reading Hash Join Plans

Hash join was MySQL's most significant optimizer addition in 8.0.18. Before it, any join without a usable index on the join condition would degrade into a nested loop full table scan — often catastrophically slow. Hash join gives MySQL a viable strategy for those cases.

Identifying Hash Join in the Tree

EXPLAIN FORMAT=TREE
SELECT p.name, COUNT(r.id) AS review_count, AVG(r.rating) AS avg_rating
FROM products p
JOIN reviews r ON r.product_id = p.id
WHERE r.created_at >= '2024-01-01'
GROUP BY p.id\G

-> Table scan on 
    -> Aggregate using temporary table
        -> Inner hash join (r.product_id = p.id)  (cost=892.50 rows=2250)
            -> Filter: (r.created_at >= '2024-01-01')  (cost=45.00 rows=2250)
                -> Table scan on r  (cost=45.00 rows=4500)
            -> Hash
                -> Table scan on p  (cost=12.50 rows=100)

The Hash node identifies which side builds the hash table — always the right (inner) child. Here, products is hashed because it's smaller. MySQL then scans reviews, filtered by created_at, and probes the hash table for each row.

Optimizing Hash Join Plans

A hash join with a filter above a table scan is often improved by adding an index on the filtered column. With idx_reviews_created_at on reviews(created_at), the plan becomes:

-> Table scan on 
    -> Aggregate using temporary table
        -> Inner hash join (r.product_id = p.id)  (cost=148.50 rows=375)
            -> Index range scan on r using idx_reviews_created_at  (cost=33.75 rows=375)
                over ('2024-01-01') <= (created_at)
            -> Hash
                -> Table scan on p  (cost=12.50 rows=100)

Cost drops from 892.50 to 148.50 — a 6x improvement from one index. If product_id were also added to the index as a covering column, the hash join itself might be replaced by an index-based nested loop.

EXPLAIN ANALYZE — Adding Real Timing Data

EXPLAIN ANALYZE executes the query and annotates each iterator with actual timing: rows returned, loops executed, and wall-clock time. It is the fastest way to confirm whether the optimizer's estimates match reality.

Reading EXPLAIN ANALYZE Output

EXPLAIN ANALYZE
SELECT c.name, COUNT(o.id) AS order_count
FROM customers c
JOIN orders o ON o.customer_id = c.id
WHERE o.status = 'completed'
GROUP BY c.id\G

-> Table scan on   (actual time=3.842..3.912 rows=1200 loops=1)
    -> Aggregate using temporary table  (actual time=3.112..3.842 rows=1200 loops=1)
        -> Nested loop inner join  (cost=3240.50 rows=2400) (actual time=0.082..2.891 rows=2400 loops=1)
            -> Filter: (o.status = 'completed')  (cost=1240.00 rows=2400) (actual time=0.065..1.842 rows=2400 loops=1)
                -> Table scan on o  (cost=1240.00 rows=12400) (actual time=0.051..1.201 rows=12400 loops=1)
            -> Single-row index lookup on customers using PRIMARY (id=o.customer_id)
                (cost=0.25 rows=1) (actual time=0.000..0.000 rows=1 loops=2400)

Key Fields in EXPLAIN ANALYZE

• actual time=X..Y — X is time to first row (ms), Y is time to last row (ms). A large gap between first and last row indicates the iterator does significant work per row.
• rows=N — actual rows returned by this iterator. Compare against the estimated rows in cost=(... rows=N) to spot estimation errors.
• loops=N — how many times this iterator was called. A table scan with loops=2400 means it ran 2,400 times — a classic nested loop full scan problem.

In this example, the Table scan on o reads 12,400 rows but only 2,400 pass the status filter — an 80% discard rate. Adding an index on orders(status) or better, orders(status, customer_id), would eliminate both the table scan and the filter node.

EXPLAIN ANALYZE Caveats

Because EXPLAIN ANALYZE executes the query, use it carefully on DELETE, UPDATE, or INSERT statements — wrap them in a transaction and roll back. For SELECT statements, it's safe to run freely, but note that it warms the buffer pool, so the second run of EXPLAIN ANALYZE will appear faster due to cached pages.

Practical Examples: Before and After Optimization

Example 1: Eliminating a Nested Loop Full Scan

A dashboard query joins orders to order_tags without an index on the join column:

-- Before: no index on order_tags.order_id
EXPLAIN ANALYZE
SELECT o.id, GROUP_CONCAT(t.tag) AS tags
FROM orders o
JOIN order_tags t ON t.order_id = o.id
WHERE o.created_at >= '2024-06-01'
GROUP BY o.id\G

-> Table scan on   (actual time=18241.3..18289.1 rows=4200 loops=1)
    -> Aggregate using temporary table  (actual time=18188.2..18241.3 rows=4200 loops=1)
        -> Nested loop inner join  (actual time=0.14..18102.5 rows=19600 loops=1)
            -> Index range scan on o using idx_created_at  (actual time=0.09..12.3 rows=4200 loops=1)
            -> Filter: (t.order_id = o.id)  (actual time=4.31..4.31 rows=4.7 loops=4200)
                -> Table scan on t  (actual time=4.30..4.30 rows=48200 loops=4200)

The problem is Table scan on t ... loops=4200. The inner side runs 4,200 times, reading 48,200 rows each time — over 202 million row reads. Total query time: 18 seconds.

-- Fix: add index on order_tags(order_id)
ALTER TABLE order_tags ADD INDEX idx_order_id (order_id);

-- After:
EXPLAIN ANALYZE
SELECT o.id, GROUP_CONCAT(t.tag) AS tags
FROM orders o
JOIN order_tags t ON t.order_id = o.id
WHERE o.created_at >= '2024-06-01'
GROUP BY o.id\G

-> Table scan on   (actual time=28.4..28.9 rows=4200 loops=1)
    -> Aggregate using temporary table  (actual time=14.2..28.4 rows=4200 loops=1)
        -> Nested loop inner join  (actual time=0.12..12.1 rows=19600 loops=1)
            -> Index range scan on o using idx_created_at  (actual time=0.09..4.8 rows=4200 loops=1)
            -> Index lookup on t using idx_order_id (order_id=o.id)  (actual time=0.002..0.002 rows=4.7 loops=4200)

Query time: 29ms. The table scan became an index lookup. Loops is still 4,200 — the nested loop structure is unchanged — but each inner lookup now takes 0.002ms instead of 4,310ms.

Example 2: Converting a Correlated Subquery to a Join

-- Before: correlated subquery
EXPLAIN FORMAT=TREE
SELECT p.id, p.name,
  (SELECT AVG(r.rating) FROM reviews r WHERE r.product_id = p.id) AS avg_rating
FROM products p
WHERE p.category_id = 5\G

-> Filter: (p.category_id = 5)  (cost=450.00 rows=200)
    -> Table scan on p  (cost=450.00 rows=2000)
        -> Select #2 (subquery in projection; dependent)
            -> Aggregate: avg(r.rating)
                -> Index lookup on r using idx_product_id (product_id=p.id)  (cost=2.50 rows=25)

The dependent label on Select #2 confirms a correlated subquery — it re-executes for every row in p. With 200 matching products, that's 200 separate aggregation queries.

-- After: rewrite as join with subquery
EXPLAIN FORMAT=TREE
SELECT p.id, p.name, r.avg_rating
FROM products p
JOIN (
  SELECT product_id, AVG(rating) AS avg_rating
  FROM reviews
  GROUP BY product_id
) r ON r.product_id = p.id
WHERE p.category_id = 5\G

-> Nested loop inner join  (cost=38.50 rows=200)
    -> Filter: (p.category_id = 5)  (cost=12.50 rows=200)
        -> Index range scan on p using idx_category_id  (cost=12.50 rows=200)
    -> Index lookup on  using  (product_id=p.id)  (cost=0.00..0.13 rows=1)
        -> Materialize  (cost=450.00 rows=1800)
            -> Table scan on r  (cost=45.00 rows=1800)
                -> Aggregate: avg(r.rating)

The subquery materializes once into a derived table, then each product does a single indexed lookup into that materialized result. Total work is linear rather than O(n) subquery executions.

Working with JusDB on MySQL Query Optimization

JusDB tunes MySQL query performance for engineering teams — analyzing EXPLAIN plans, adding targeted indexes, rewriting slow queries, and monitoring regressions. Our DBAs have optimized queries from 30-second full scans to sub-millisecond index lookups.

Explore JusDB MySQL Management →  |  Talk to a DBA

Related reading:

• MySQL EXPLAIN: Query Optimization Guide
• MySQL Indexes Explained
• MySQL Slow Query Log: Finding and Fixing Slow Queries