Tuning Performance of MySQL Query Execution Plan
Everyone has experienced situations when executing long and complex queries takes much time
and thus, makes the process slower and inefficient. In this case, you need to understand why
this happens and what you can do to detect any issues related to the query and thus, tune queries.
We offer an excellent solution to this issue - Query Profiler built into dbForge Studio for MySQL,
a MySQL performance tuning and monitoring tool. This tool allows you to optimize and improve
query performance as well as analyze MySQL queries with the following functionalities:
EXPLAIN plan, which helps visualize query tuning and monitoring, SHOW PROFILE results grid and query statistics.
How to Optimize a Query
Here we would like to show you how it is possible to monitor query performance using Query Profiler on a particular example.
Assume we need to find out who was rewarded in the baseball competition according to the Baseball Magazine All-Star.
To do this, we write the following query:
After that we click the Query Profiling Mode button and then click Execute (or F5) on the SQL toolbar to activate the Query Profiler tool.
As you can see, the profiler document view opens. The query profiling results are
displayed on three left tabs - Profile, Plan and Status - with time and date of query execution.
The SHOW PROFILE results grid provided us with the values required to process our query.
By clicking Plan on the Profiler Document view, we will see the EXPLAIN plan.
It shows what was checked as well as conditions used to execute the query. According to it,
the tool has checked ALL rows using WHERE conditions, which in its turn, took much time.
To simplify the process, we will modify our query by adding indexes to it.
Click the Query Profiling Mode button and execute the query.
According to Profile, values in the SHOW PROFILE results grid have been changed (reduced). To visualize it, we will compare these two queries.
Select the nodes of the required query profiling results in the tree view by holding the CTRL key.
We see that the rows with different values are highlighted with a green color.
This feature helps much as we can monitor the better or worse performance of a particular operation and thus check what process takes much resources.
In addition, you can filter results by values and group them in an ascending or descending order. Alternatively, you may print, export to the most popular formats or send via an email by selecting the corresponding plan and right-clicking Print on the shortcut menu:
Summary
To sum it up, with MySQL performance monitoring tool, users can easily view, detect,
and improve query performance. MySQL query execution plan, known as EXPLAIN plan,
shows how SQL statements are executed. By showing these conditions, MySQL query execution plan
greatly improves MySQL query tuning. Therefore, using both Profile and Plan tabs,
we can easily analyze query performance as well.