Visualizing MySQL Execution Plans with Flame Graphs

In everyday development and database management, SQL performance optimization is unavoidable. A query that runs fast in a test environment may become sluggish in production due to larger data volumes. Developers and DBAs often rely on MySQL's EXPLAIN tool to analyze execution plans, but the tabular output can be hard to interpret, especially for complex SQL.

Flame Graph: A Visual Tool for Performance Analysis

A Flame Graph visualizes performance data by showing the time consumed by each function (or method) and their call relationships. It was first introduced by Brendan Gregg and is widely used for CPU and memory profiling.

Features of Flame Graph:

Horizontal axis: Represents the number of samples (total time) for a function; wider blocks indicate more time spent.

Vertical axis: Represents call‑stack depth, from bottom (caller) to top (callee).

Each rectangle block: Represents a function; its width shows the total time of the function and its children.

Color: Usually has no special meaning, only to differentiate blocks.

Uses:

Quickly locate performance bottlenecks and identify the most time‑consuming functions.

Understand program call relationships and hot paths.

Inspired by Tanel Poder, who visualized Oracle execution plans with Flame Graphs ( Visualizing SQL Plan Execution Time With FlameGraphs ), the question arises: can we also visualize MySQL execution plans with Flame Graphs?

Flame Graph Visualizing MySQL Execution Plan

Before MySQL 8.0, the EXPLAIN tool only output estimated execution paths and row counts, lacking real timing data. The tabular format makes it difficult to recognize execution order for multi‑level nested queries, forcing developers to rely on experience for optimization.

EXPLAIN SELECT
    p.category,
    p.name as product_name,
    p.price,
    (
    SELECT COUNT(DISTINCT o.customer_id)
    FROM orders o
    WHERE o.product_id = p.product_id
    AND o.status = 'completed'
    ) as unique_buyers,
    EXISTS (
    SELECT 1
    FROM orders o2
    WHERE o2.product_id = p.product_id
    AND o2.order_date >= DATE_SUB(NOW(), INTERVAL 30 DAY)
    ) as has_recent_orders
FROM products p
WHERE p.stock < 100
AND p.price > (
    SELECT AVG(price) * 1.5
    FROM products
    WHERE category = p.category)
ORDER BY p.category, unique_buyers DESC;
+----+--------------------+----------+------------+------+------------------------+------------+---------+-------------------+------+----------+----------------------------------------------+
| id | select_type        | table | partitions | type | possible_keys          | key        | key_len | ref | rows | filtered | Extra                                        |
+----+--------------------+----------+------------+------+------------------------+------------+---------+-------------------+------+----------+----------------------------------------------+
|  1 | PRIMARY | p | NULL | ALL | NULL | NULL | NULL | NULL | 100 | 33.33 | Using where; Using temporary; Using filesort |
|  4 | DEPENDENT SUBQUERY | products | NULL | ALL | NULL | NULL | NULL | NULL | 100 | 10.00 | Using where |
|  3 | DEPENDENT SUBQUERY | o2 | NULL | ref | product_id | product_id | 5 | test.p.product_id | 37 | 33.33 | Using where |
|  2 | DEPENDENT SUBQUERY | o | NULL | ref | customer_id,product_id | product_id | 5 | test.p.product_id | 37 | 10.00 | Using where |
+----+--------------------+----------+------------+------+------------------------+------------+---------+-------------------+------+----------+----------------------------------------------+

MySQL 8.0 introduced EXPLAIN ANALYZE , which, unlike EXPLAIN , shows actual execution metrics such as real time, rows processed, and loop counts for each step, making Flame Graph visualization feasible.

***row***
EXPLAIN: - > Sort: p.category, unique_buyers DESC (actual time=2.23... rows=4 loops=1)
- > Stream results (cost=10.2 rows=33.3) (actual time=1.07... rows=4 loops=1)
- > Filter: ((p.stock < 100) and (p.price > (select #4))) (cost=10.2 rows=33.3) (actual time=0.832... rows=4 loops=1)
- > Table scan on p (cost=10.2 rows=100) (actual time=0.0718... rows=100 loops=1)
- > Select #4 (subquery in condition; dependent)
- > Aggregate: avg(products.price) (cost=2.25 rows=1) (actual time=0.0823... rows=1 loops=15)
- > Filter: (products.category = p.category) (cost=1.25 rows=10) (actual time=0.0497... rows=27.1 loops=15)
- > Table scan on products (cost=1.25 rows=100) (actual time=0.0484... rows=100 loops=15)
- > Select #2 (subquery in projection; dependent)
- > Aggregate: count(distinct o.customer_id) (cost=9.75 rows=1) (actual time=0.079... rows=1 loops=4)
- > Filter: (o.`status` = 'completed') (cost=9.39 rows=3.61) (actual time=0.0568... rows=16.8 loops=4)
- > Index lookup on o using product_id (product_id = p.product_id) (cost=9.39 rows=36.1) (actual time=0.0558... rows=37 loops=4)
- > Select #3 (subquery in projection; dependent)
- > Limit: 1 row(s) (cost=10.2 rows=1) (actual time=0.0499... rows=0 loops=4)
- > Filter: (o2.order_date >= (now() - interval 30 day)) (cost=10.2 rows=12) (actual time=0.0497... rows=0 loops=4)
- > Index lookup on o2 using product_id (product_id = p.product_id) (cost=10.2 rows=36.1) (actual time=0.0367... rows=37 loops=4)

After that I tried visualizing it with a Flame Graph:

Visualized Execution Plan:

Clear identification of execution cost The Flame Graph shows the width of each block proportionally to the time spent, allowing you to see the most expensive operations at a glance without comparing rows of a table.

Clear display of nested structure Multi‑level joins or sub‑queries are represented by the vertical hierarchy of the Flame Graph, making complex SQL logic easy to understand.

MySQL Explain Flame Graph

I have deployed an online service called MySQL Explain Flame Graph . Developers and DBAs only need to paste the output of EXPLAIN ANALYZE into the tool, or call the API, to generate a Flame Graph instantly.

Command‑line example:

mysql -BNEe 'explain analyze sql_statement' | curl --data-binary @- http://sqlfg.dbcopilot.online/api/sqlflamegraph > explain.svg

Conclusion

MySQL 8.0's EXPLAIN ANALYZE provides richer execution‑plan information, and using Flame Graphs makes this data visual and intuitive, enabling developers and DBAs to pinpoint performance bottlenecks quickly and optimize SQL more efficiently.

Give it a try, and if it helps you, please like ❤️ and share 😄.

References

[1] Visualizing SQL Plan Execution Time With FlameGraphs: https://tanelpoder.com/posts/visualizing-sql-plan-execution-time-with-flamegraphs/ [2] MySQL Explain Flame Graph: http://sqlfg.dbcopilot.online/