MySQL Execution Plans in Tree Format

MySQL Tree‑Format Execution Plan

MySQL provides a tree‑style execution plan via EXPLAIN FORMAT=TREE , similar to Oracle's output, presenting results in a hierarchical, indented layout that highlights the main operations, their costs, and row estimates.

Why is it useful?

The tree format offers a more intuitive view of query optimization effects compared with the classic tabular format or JSON, allowing developers to quickly spot expensive steps and understand the execution flow.

Demonstration with eight SQL statements

Below are the SQL statements, their tree‑format plans, and brief interpretations.

SQL 1

Full table scan on t1 with cost and rows displayed.

mysql:ytt>desc format=tree table t1\G
*************************** 1. row ***************************
EXPLAIN: -> Table scan on t1 (cost=265841.68 rows=2552706)
1 row in set (0.00 sec)

SQL 2

Full table scan on t1 with LIMIT 10 ; cost identical to the unlimited scan.

mysql:ytt>desc format=tree table t1 limit 10\G
*************************** 1. row ***************************
EXPLAIN: -> Limit: 10 row(s) (cost=265841.68 rows=10)
    -> Table scan on t1 (cost=265841.68 rows=2552706)
1 row in set (0.00 sec)

SQL 3

Simple equality filter using index idx_r1 ; cost ratio 1:7 compared with full scan, rows ratio 1:52.

mysql:ytt>desc format=tree select * from t1 a where r1 = 10\G
*************************** 1. row ***************************
EXPLAIN: -> Index lookup on a using idx_r1 (r1=10) (cost=36558.05 rows=48448)
1 row in set (0.00 sec)

SQL 4

Aggregate count with a subquery; default plan materializes the subquery, leading to high cost and row counts.

mysql:ytt>desc format=tree select count(*) from t1 a where r1 in (select r1 from t1 b)\G
*************************** 1. row ***************************
EXPLAIN: -> Aggregate: count (cost=1303263126712.80 rows=6516307922436)
    -> Nested loop inner join (cost=651632334469.20 rows=6516307922436)
        -> Filter: (a.r1 is not null) (cost=265872.60 rows=2552706)
            -> Index scan on a using idx_r1 (cost=265872.60 rows=2552706)
        -> Single‑row index lookup on
(cost=...)
            -> Materialize with deduplication (cost=521143.20 rows=2552706)
                -> Filter: (b.r1 is not null) (cost=265872.60 rows=2552706)
                    -> Index scan on b using idx_r1 (cost=265872.60 rows=2552706)
1 row in set (0.00 sec)

SQL 5

Deeply nested subqueries (four levels); plan shows a massive cost and a nested‑loop hierarchy, illustrating poor performance without query rewrite.

mysql:ytt>desc format=tree select * from t1 a where r1 in (select r1 from t1 b where r1 in (select r1 from t1 c where r1 in (select r1 from t1 d where r1 in (select r1 from t1 e))))\G
... (tree plan with extremely high cost and rows) ...
1 row in set (0.00 sec)

SQL 6

Left outer join between t1 and t2 ; plan shows a nested‑loop left join, with a full scan on t1 and an index lookup on t2 .

mysql:ytt>desc format=tree select * from t1 left join t2 using (r1)\G
*************************** 1. row ***************************
EXPLAIN: -> Nested loop left join (cost=1159286.21 rows=2552706)
    -> Table scan on t1 (cost=265839.11 rows=2552706)
    -> Index lookup on t2 using idx_r1 (r1=t1.r1) (cost=0.25 rows=1)
1 row in set (0.00 sec)

SQL 7

Hash join example where neither table has an index on the join column r3 ; MySQL builds a hash table on the smaller table t2 and probes it with t1 .

mysql:ytt>desc format=tree select a.* from t1 a, t2 b where a.r3 = b.r3\G
*************************** 1. row ***************************
EXPLAIN: -> Inner hash join (a.r3 = b.r3) (cost=2595860374.91 rows=2595846770)
    -> Table scan on a (cost=3.75 rows=2552706)
    -> Hash
        -> Table scan on b (cost=1025.15 rows=10169)
1 row in set (0.00 sec)

SQL 8

Index‑merge plan using intersect of idx_r1 and idx_r2 ; a composite index idx_u1(r1,r2) yields a much lower cost.

-- Index‑merge version
mysql:ytt>desc format=tree select * from t1 where r1=10 and r2=10\G
EXPLAIN: -> Filter: ((t1.r2 = 10) and (t1.r1 = 10)) (cost=1635.47 rows=1854)
    -> Index range scan on t1 using intersect(idx_r1,idx_r2) (cost=1635.47 rows=1854)
1 row in set (0.00 sec)

-- Composite index version
mysql:ytt>desc format=tree select * from t1 where r1=10 and r2=10\G
EXPLAIN: -> Index lookup on t1 using idx_u1 (r1=10, r2=10) (cost=490.62 rows=544)
1 row in set (0.00 sec)

The article concludes that the tree format provides a clear, hierarchical view of execution plans, making performance analysis more straightforward.