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

• MySQL Docs: EXPLAIN FORMAT=TREE
• explain.dalibo.com – Paste & visualize
• Percona: Visual Explain

---

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!