Tuning Performance using MySQL Query Explain Plan

The MySQL Explain plan helps you understand how MySQL queries are executed, how JOINs are performed, and what indexing techniques are better to use to run queries more efficiently.

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
  • tune the queries to return the data in the most efficient way
  • visualize query tuning and monitoring with the MySQL Explain plan
  • analyze MySQL queries using EXPLAIN plan, SHOW PROFILE results grid, and query statistics

Analyze and Optimize Queries with Explain Plan

We are going to show you on a particular example how to monitor and analyze MySQL query performance using Query Profiler. Also, we will consider how to optimize MySQL query performance using the MySQL EXPLAIN plan.

Executing MySQL Query

Assume we need to find out who was rewarded in the baseball competition according to the Baseball Magazine All-Star.

To do this, in the Start Page > SQL Development tab, we will click Query Profiler and write the SELECT query with the WHERE condition in the text editor.

To execute the query, we will click or press F5. The results will be displayed on the table grid. There you can view, edit and print data, export it to the most popular formats, filter by conditions, and much more.

Executing the SELECT query with the WHERE condition in the Query Profiling Mode

Working with Query Profiler

After that, to activate the Query Profiler tool, on the SQL toolbar, click Query Profiling Mode and then Execute (or F5). This will open the Query Profiler document view.

As you can see, the query profiling results along with time and date of query execution are displayed in the Query Profiler tree with three tabs - Profile, Plan, and Session statistics. The Profile results grid provides information about values required to process the query including what operations have been performed, their duration, and the progress (in percentage).

The query profiling results along with time and date of query execution are displayed in the Query Profiling Mode

Analyzing the MySQL Explain Plan

If you want to get deeper into query performance process and analyze why it takes long time to run the query or how the queries are executed, you need to switch to the EXPLAIN plan in the Query Profiler tree.

The EXPLAIN results grid outputs what data is checked, how many rows are processed, what indexes are used to run the query faster. Also, the plan measures time spent on query execution and showed the conditions used to run the query. For example, the TYPE field of the EXPLAIN plan can indicate how MySQL tables are joined or in which order they are accessed and processed. According to the plan in our example, the tool has checked ALL rows using WHERE conditions, which in turn, took much time.

How to use the EXPLAIN plan in mysql with the help of Query Profiler built into dbForge Studio for MySQL

Adding Indexes to the Query

To execute the statement faster and, thus, to optimize the process, we will modify our query by adding indexes to tables to filter out the rows in the plan.

On the SQL toolbar, click Query Profiling Mode and execute the query. As you can see, in the Profile tab, values in the SHOW PROFILE results grid have been changed and, if more precisely, reduced. To visualize it and check the performance, we will compare these two queries.

Modify MySQL query by adding indexes to tables to filter out the rows in the plan in Query Profiling Mode

Comparing Queries Differences

In the Query Profiler tree, holding the CTRL key, select the nodes of the required query profiling results to compare the differences in query executions. We see that the rows with different values are highlighted with a green color.

This feature helps significantly 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.

Compare the differences in query execution with MySQL performance tuning tool

Conclusion

To sum it up, MySQL performance monitoring tool and MySQL Query Explain Plan is better to use when you want to significantly increase query performance and optimize queries based on the output provided by the MySQL Explain plan, identify any bottlenecks that may affect database performance, and get the information to see which queries run slow.

dbForge Studio for MySQL

The most intelligent MySQL management tool

Download

Availability in the editions of dbForge Studio for MySQL

Feature

Enterprise
Professional
Standard
Express
Execution plan tool
Yes
Yes
Yes
None