MySQL EXPLAIN FORMAT=TREE – Complete Guide with Real Examples

A human-readable, hierarchical view of the query execution plan — much clearer than traditional EXPLAIN.

MySQL EXPLAIN FORMAT=TREE – Complete Guide with Real Examples

MySQL `EXPLAIN FORMAT=TREE` – Complete Guide with Real Examples

Visualize Query Plans Like a Pro (MySQL 8.0.16+)

What is `EXPLAIN FORMAT=TREE`?

A human-readable, hierarchical view of the query execution plan — much clearer than traditional EXPLAIN.

Why use it?
- See join order
- Understand filter pushdown
- Spot index usage
- Debug performance bottlenecks

Sample Tables (Same as Before)

CREATE TABLE users (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(50),
    email VARCHAR(100),
    country CHAR(2),
    created_at DATETIME,
    INDEX idx_email (email),
    INDEX idx_country_created (country, created_at)
);

CREATE TABLE orders (
    order_id INT PRIMARY KEY AUTO_INCREMENT,
    user_id INT,
    amount DECIMAL(10,2),
    status ENUM('pending','shipped'),
    INDEX idx_user_status (user_id, status)
);

-- Sample Data
INSERT INTO users VALUES
(1, 'Alice', 'a@x.com', 'US', '2025-01-01 10:00:00'),
(2, 'Bob', 'b@x.com', 'UK', '2025-01-02 11:00:00'),
(3, 'Charlie', 'c@x.com', 'US', '2025-01-03 12:00:00');

INSERT INTO orders VALUES
(101, 1, 99.99, 'shipped'),
(102, 1, 149.50, 'pending'),
(103, 2, 79.00, 'shipped');

Example 1: Simple Indexed Lookup

EXPLAIN FORMAT=TREE
SELECT * FROM users WHERE email = 'a@x.com';

Output:

-> Index lookup on users using idx_email (email='a@x.com')  
     (cost=0.35 rows=1)

Breakdown:

Part	Meaning
`Index lookup`	Uses `idx_email`
`email='a@x.com'`	Filter pushed to index
`cost=0.35`	Estimated cost
`rows=1`	Expects 1 row

Perfect plan — index-only lookup

Example 2: Range Scan with Composite Index

EXPLAIN FORMAT=TREE
SELECT * FROM users 
WHERE country = 'US' AND created_at > '2025-01-01';

Output:

-> Index range scan on users using idx_country_created 
     over (country='US' AND created_at > '2025-01-01')  
     (cost=0.70 rows=2)

Breakdown:

Uses composite index (country, created_at)
Both conditions pushed into index
range scan → efficient

Example 3: Full Table Scan (No Index)

EXPLAIN FORMAT=TREE
SELECT * FROM users WHERE name = 'Alice';

Output:

-> Filter: (users.name = 'Alice')  
     -> Table scan on users  
          (cost=0.75 rows=3)

Breakdown:

Table scan → full scan
Filter applied after reading all rows
cost=0.75 for 3 rows → small table, but scales badly

Fix: CREATE INDEX idx_name ON users(name);

Example 4: JOIN with Index Usage

EXPLAIN FORMAT=TREE
SELECT u.name, o.amount 
FROM users u
JOIN orders o ON u.id = o.user_id
WHERE o.status = 'shipped';

Output:

-> Nested loop inner join  
     (cost=1.42 rows=1)
   -> Filter: (o.status = 'shipped')  
        -> Index lookup on o using idx_user_status (status='shipped')  
             (cost=0.70 rows=1)
   -> Single-row index lookup on u using PRIMARY (id=o.user_id)  
        (cost=0.35 rows=1)

Breakdown:

1. Start with orders (driving table)
   └─ Use idx_user_status → find 'shipped'
2. For each order → lookup user by PK
   └─ Fast: eq_ref on PRIMARY

Optimal join order
Index on both sides

Example 5: Covering Index (Index-Only)

EXPLAIN FORMAT=TREE
SELECT user_id, status FROM orders WHERE user_id = 1;

Output:

-> Index lookup on orders using idx_user_status (user_id=1)  
     (cost=0.70 rows=2)

No table access → idx_user_status contains user_id and status
Covering index in action

Example 6: ORDER BY Using Index

EXPLAIN FORMAT=TREE
SELECT * FROM users 
WHERE country = 'US' 
ORDER BY created_at DESC;

With proper index:

CREATE INDEX idx_country_created_desc ON users(country, created_at DESC);

Output:

-> Index scan on users using idx_country_created_desc 
     over (country='US') in descending order  
     (cost=0.70 rows=2)

No filesort
Order from index

Example 7: Filesort (No Index for ORDER BY)

EXPLAIN FORMAT=TREE
SELECT * FROM users ORDER BY name;

Output:

-> Sort by name  
     -> Table scan on users  
          (cost=0.75 rows=3)

Sort by name → filesort (memory/disk sort)
Fix: CREATE INDEX idx_name ON users(name);

Example 8: LEFT JOIN with NULLs

EXPLAIN FORMAT=TREE
SELECT u.name, o.amount 
FROM users u
LEFT JOIN orders o ON u.id = o.user_id;

Output:

-> Nested loop left join  
     (cost=1.78 rows=3)
   -> Table scan on u  
        (cost=0.75 rows=3)
   -> Single-row index lookup on o using PRIMARY (user_id=u.id)  
        (cost=0.35 rows=1)

Starts with users (left table)
For each user, lookup orders
Returns NULL if no match

Example 9: Complex Query with Multiple Joins

EXPLAIN FORMAT=TREE
SELECT u.name, o.amount, p.product_name
FROM users u
JOIN orders o ON u.id = o.user_id
JOIN order_items oi ON o.order_id = oi.order_id
JOIN products p ON oi.product_id = p.id
WHERE u.country = 'US';

(Assume indexes on all FKs)

-> Nested loop inner join  
     (cost=...)
   -> Nested loop inner join  
        -> Nested loop inner join  
             -> Filter: (u.country = 'US')  
                  -> Table scan on u  
             -> Index lookup on o using idx_user_status (user_id=u.id)  
        -> Index lookup on oi using idx_order_id (order_id=o.order_id)  
   -> Single-row index lookup on p using PRIMARY (id=oi.product_id)

Shows join nesting
Helps reorder or add indexes

`FORMAT=TREE` vs Traditional `EXPLAIN`

Feature	`EXPLAIN` (table)	`EXPLAIN FORMAT=TREE`
Readability	Low	High
Join order	Hard to see	Clear nesting
Filter pushdown	Hidden	Explicit
Cost & rows	Per row	Per node
Best for	Scripts	Humans

Key Phrases to Look For

Phrase	Meaning
`Index lookup`	Using index → good
`Index range scan`	Range query → good
`Index scan ... in order`	ORDER BY from index
`Table scan`	Full scan → bad
`Filter:`	Post-scan filter
`Sort by`	Filesort → slow
`Nested loop`	Row-by-row join
`Single-row`	`eq_ref` → best join

Pro Tips

Tip	Command
Always use `FORMAT=TREE` in dev	`EXPLAIN FORMAT=TREE`
Combine with `ANALYZE`	`EXPLAIN ANALYZE FORMAT=TREE`
Paste output in tools	explain.dalibo.com
Use in `pt-visual-explain`	Percona Toolkit

Bonus: `EXPLAIN ANALYZE FORMAT=TREE` (MySQL 8.0.18+)

EXPLAIN ANALYZE FORMAT=TREE
SELECT * FROM users WHERE email = 'a@x.com';

-> Index lookup on users using idx_email (email='a@x.com')  
     (cost=0.35 rows=1) (actual time=0.00012..0.00014 rows=1 loops=1)

Actual time + actual rows → real performance

Practice Queries – Run with `FORMAT=TREE`

1. EXPLAIN FORMAT=TREE SELECT * FROM users WHERE id = 1;
2. EXPLAIN FORMAT=TREE SELECT name FROM users WHERE country = 'US';
3. EXPLAIN FORMAT=TREE SELECT * FROM orders ORDER BY amount DESC;
4. EXPLAIN FORMAT=TREE SELECT u.name, o.amount FROM users u JOIN orders o ON u.id = o.user_id;
5. EXPLAIN FORMAT=TREE SELECT * FROM users WHERE name LIKE 'A%';

Final Cheat Sheet

Goal	Look For in TREE
Index used?	`Index lookup`, `Index range scan`
Full scan?	`Table scan`
Covering index?	No table access
ORDER BY fast?	`in descending order`
JOIN efficient?	`Single-row index lookup`
Filter early?	`Filter:` inside index node

Resources

You now read query plans like a DBA!
Use EXPLAIN FORMAT=TREE → fix slow queries in minutes

Want a printable FORMAT=TREE cheat sheet (PDF)?
Or a VS Code snippet to auto-run it? Just ask!

Last updated: Nov 09, 2025

MySQL EXPLAIN FORMAT=TREE – Complete Guide with Real Examples

A human-readable, hierarchical view of the query execution plan — much clearer than traditional EXPLAIN.