MySQL Explain: Analyze MySQL Queries with dbForge Studio's EXPLAIN Plan Tool

MySQL EXPLAIN plan, also known as execution plan, helps you understand how MySQL queries are executed and what indexing techniques are better for running queries efficiently. The EXPLAIN plan is a handy statement, especially when you need to optimize MySQL queries.

Everyone has experienced situations when executing long and complex queries takes much time, making the process slower and inefficient. In such cases, you need to understand why this happens and what you can do to tune your queries for minimum impact on database performance.

We offer an excellent solution to this issue — Query Profiler built into dbForge Studio for MySQL. This tool allows you to:

Optimize and improve query performance
Tune queries to return data in the most efficient way
Visualize query tuning and monitoring with MySQL EXPLAIN plan
Analyze MySQL queries using the EXPLAIN plan, SHOW PROFILE results grid,
and query statistics

Analyze and optimize queries with the EXPLAIN plan

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

Execute MySQL query

Suppose we need to select all managers with the last date they made a car sale. You can use the SELECT statement for this.

Write the query and click Execute.

The results will be displayed in the table. You can view, edit and print data, export it to the most popular formats, filter by conditions, and much more.

Use Query Profiler for performance analysis

To get a complete picture of how a statement is executed, you can run it with the enabled Query Profiling mode.

Click Query Profiling Mode and then click Execute. The Query Profiler document view will get opened.

As you can see, the query profiling results as well as time and date of query execution are displayed in the Query Profiler tree. The tree contains three informative tabs: Profile, Plan, and Session statistics.

What is MySQL EXPLAIN?

The EXPLAIN statement provides insights into query performance, such as query ID, type, partitions, JOIN types, and index usage. It allows you to analyze and improve slow-running queries.

EXPLAIN can be used with SELECT, DELETE, INSERT, REPLACE, and UPDATE queries. Always place it at the beginning of the query.

MySQL EXPLAIN vs. EXPLAIN ANALYZE

Though EXPLAIN and EXPLAIN ANALYZE are sometimes used interchangeably, they have a key difference.

EXPLAIN estimates how a query will perform without executing it, while EXPLAIN ANALYZE runs the query and provides both the estimated and actual costs in the execution plan.

This distinction is crucial for INSERT, UPDATE, or DELETE queries, as the EXPLAIN ANALYZE MySQL tool will modify the database.

What issues EXPLAIN can solve

EXPLAIN is helpful when you want to know if a query does what it is supposed to do. First, check the data in each column of the EXPLAIN output:

select_type: trace data in this column if you want to ensure that your queries take part in JOIN operations
partitions: data in this column can be helpful if you added partitions to a table and need to check what partitions a query uses
type: if you design queries, focus on data in this column
possible_keys: data in this column can inform you what indexes MySQL used
key: if you need to know what index MySQL selected, keep an eye on data in the column
key_len: the column displays the length of the selected index
ref: data in the column can be valuable to improve query performance by using indexes
rows: pay attention to data in this column if you design indexes inside database instances
filtered: it shows an approximate percentage of the rows in a table filtered by a particular condition

How to use MySQL EXPLAIN

dbForge Studio for MySQL allows you to get deeper into the query performance process and analyze why it takes a long time to run a query.

The EXPLAIN results grid displays what data is checked, how many rows are processed, and what indexes are used to run the query faster.

Also, the Plan measures time spent on query execution and shows 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.

It is not necessary to choose between MySQL EXPLAIN vs EXPLAIN ANALYZE statements and risk changing the data in a database. The EXPLAIN Plan in the Query Profiler tree allows you to check precisely how queries are executed.

Best practices to optimize a slow query with MySQL EXPLAIN

In this section, we explain how to easily tune a slow-running query with the Query Profiler provided in dbForge Studio for MySQL. The FOREIGN KEY relationship plays the main part in this process as tables must be related to each other.

Let us have a look at the scenario for MySQL EXPLAIN with example: we created external keys for a table we wanted to execute a query for. Then, we ran the query with the Query Profiling mode enabled.

As you can see in the screenshot, there is a huge difference in the execution time: the first result is the time of the executed query with the keys and the second result is the time of the executed query without the keys.

MySQL Query Profiler

Watch the video to see the MySQL Query Profiler in action. Learn how it makes database development a whole lot easier and more efficient. In addition, it is a great optimization tool for tuning MySQL queries and investigating performance issues.

Conclusion

To sum it up, the 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.

Availability in the editions of dbForge Studio for MySQL

Feature

Enterprise

Professional

Standard

Express

Query Profiler

Execution plan tool