Skip to main content

Plan Visualization for MySQL

· 4 min read
PawSQL Team
PawSQL Team
Optimize your SQL Queries by Clicks!

Copyright © 2024 PawSQL

Overview​

The execution plan is an internal execution strategy generated by the database when executing SQL queries. It describes how the SQL is executed step-by-step internally in the database and is an important basis for diagnosing SQL performance issues and tuning. As the complexity of database applications increases, SQL execution plans become more and more complicated along with the SQL. Relying on plaintext execution plans makes it very challenging to grasp the overall execution plan, locate key paths and bottlenecks, bringing great difficulties to DBAs' performance tuning work.

The visual execution plan tool intuitively presents the execution plan in a flowchart, clearly showing the plan structure. It uses visual elements to highlight key operations and paths, while associating SQL statement source code for integrated analysis. This can greatly improve the efficiency for DBAs to understand and analyze execution plans, quickly locate performance bottlenecks, and perform precise optimization on SQL or indexes.

Commercial databases usually develop their own execution plan visualization tools to help database users understand how SQL is executed and quickly locate SQL performance issues. However, open source databases, despite their huge user base, have very few execution plan visualization tools available. In particular, there are currently no suitable MySQL execution plan visualization tools in the market.

PawSQL Plan Visualizer (PPV)​

PawSQL's execution plan visualization tool PawSQL Plan Visualizer (PawEV) is developed based on the open source components pev/pev2. Its main functions include:

  1. Support for multiple databases, including MySQL/PostgreSQL/openGauss/Oracle, etc.

  2. Support for multiple input formats, such as MySQL's json/tree/analyze formats, Oracle's form formats

  3. Interactive analysis based on highlighting by execution time, cost, row count

  4. Metrics statistics based on database objects and operators

PPV's Support for MySQL​

Supported Versions and Formats​

MySQL VersionSupported Input FormatsDisplayed Information
>=5.6, <8.0.16format=jsonEstimated cost/row count
>=8.0.16, <8.0.18format=json/format=treeEstimated cost/row count
>=8.0.18format=json/format=tree/analyzeActual execution time/Actual row count/Estimated cost/Estimated row count

Creating Visualized Plans​

By submitting the text output of MySQL execution plans to PawSQL Explain Visualizer, PawSQL clearly displays the execution plans in a tree structure.

The icons in the upper right corner of the operators provide hints on some issues, such as longer execution time, higher estimated costs, significant deviations between estimated and actual number of rows executed, etc. With these hints, DBAs can easily locate problematic modules for detailed troubleshooting.

img

Interactive Analysis​

  • Click the summary information in the header to quickly locate nodes with the longest execution time or highest cost.

  • After expanding each operator, it will show its specific execution time, number of rows, and other information.

  • Clicking the time, cost, row count will display the percentage of each node in the form of a progress bar.

img

Statistics Based on Objects and Operators​

The right side of the page contains statistical analysis based on objects (tables/indexes) and operators in the execution plan, including execution time, estimated costs, etc.

img

Integration with Optimization Features​

For optimization tasks with performance validation enabled, PawSQL will obtain the execution plans before and after optimization, and visually compare them using PEV.

img

About PawSQL​

PawSQL focuses on automating and intelligizing database performance optimization, supporting MySQL, PostgreSQL, openGauss, Oracle etc. The SQL optimization products provided include:

  • PawSQL Cloud, online automated SQL optimization tool, supports SQL review, intelligent query rewriting, cost-based index recommendation, suitable for database administrators and data application developers.

  • PawSQL Advisor, IntelliJ plugin, suitable for data application developers, can be installed by searching for "PawSQL Advisor" in IDEA/DataGrip application market.

  • PawSQL Engine, the backend optimization engine of PawSQL products, can be independently installed and deployed in docker image, and provides SQL optimization services through http/json interfaces.

Contact Us​

Website: https://docs.pawsql.com

Email: service@pawsql.com

Twitter: https://twitter.com/pawsql

Scan to follow PawSQL on Wechat PawSQL