In this guide, we will go in-depth into the world of MySQL query execution plan. We will break it down into its fundamental concepts and learn how it works and how to make use of it in our SQL optimization processes.
Let’s begin!
The Purpose of MySQL query execution plan
MySQL query optimizer is an in-built function of the database which automatically runs when you execute a query. Its job is to design an optimal MySQL query execution plan for every single query that is executed. The MySQL explain plan allows you to view the plan by using the EXPLAIN keyword as a prefix to your request.
What Is MySQL Explain plan ?
EXPLAIN ANALYZE is a profiling tool for your queries that will show you where MySQL spends time on your query and why. It will plan the query, instrument it and execute it while counting rows and measuring time spent at various points in the execution plan. When execution finishes, EXPLAIN ANALYZE will print the plan and the measurements instead of the query result.
The EXPLAIN keyword is an extremely powerful tool to understand and optimize MySQL queries. They offer explanations and insights as to why your queries are slow or performing poorly. However, we have seen DBAs and developers rarely use it. Since you are it’s it’s a sign that you want to make your queries faster. So, let’s get into how we can interpret the results the EXPLAIN statement gives us.
The Right Way to Interpret the EXPLAIN results
In our daily life, we generally tend to inquire about the cost of goods before we actually purchase them. Similarly, in the MySQL explain plan realm, the EXPLAIN tool helps to fetch the running cost of a query before it’s actually executed.
The EXPLAIN tool in MySQL describes how the DML will be executed and that includes the table structure as well. It’s key to note here that since MySQL 5.7, the DML (Select, Update, Delete, Insert, and Replace) commands are allowed in EXPLAIN—Thus, we will not just mention SELECT in our explanations.
Let’s look at the EXPLAIN’s syntax:
{EXPLAIN | DESCRIBE | DESC} tbl_name [col_name | wild] {EXPLAIN | DESCRIBE | DESC} [explain_type] {explainable_stmt | FOR CONNECTION connection_id} explain_type: { | FORMAT = format_name } format_name: { TRADITIONAL | JSON
Achieving High Performance through Data Indexing
Let’s begin by analyzing the output of a simple query that uses the EXPLAIN keyword and then work our way towards more complicated ones. Before we proceed, it’s key to ensure that you have the SELECT privilege to use the EXPLAIN tool and the SHOW VIEW privilege for working with views.
Here’s an example:
Since we have used EXPLAIN in the query above, we are able to see the tables where indexes are missing. This allows you to make the necessary adjustments and optimize your queries. Bear in mind that you may need to run EXPLAIN to get your query to an optimal level.
Expectations vs. Reality
As we work with so many tbl_example, we often see some patterns in the concerns they bring to us. Here’s one of the most common questions we get asked:
Why doesn’t my query use the indexes that we have created?
There is no single answer for why the MySQL optimizer doesn’t use an index. However, one of the main reasons is that the statistics are not up to date.
The good news is that you can refresh the statistics that MySQL optimizer uses by running the following command:
ANALYZE TABLE [table_name];
For example, here’s how you can run it on the tbl_example table:
ANALYZE TABLE tbl_example;
The image below describes the output of the ANALYZE command on the tbl_example table:
A word of caution: If you are dealing with tables that have millions of rows, the ANALYZE command can lock the table for a short duration. Thus, we recommend you execute this command during low database load times.
Here’s a view of the result columns for executing the EXPLAIN PLAN command on the latest release of MySQL is 8.x :
Let’s dig into each of the rows you see in the table above:
1. id (JSON name: select_id)
The id field is a sequential reference used within the query by MySQL. Observing the EXPLAIN command’s output that has multiple lines will reveal that the output has sequential numbering for the rows.
2. select_type (JSON name: none)
The select type field provides the most information compared to others. It contains references about the DML statements like SELECT and it also shows how it will be interpreted by MySQL.
Here are the various values that the select_type options provide:
3. table (JSON name: table_name)
This field represents the table’s name that the EXPLAIN plan uses.
4. partitions (JSON name: partitions)
If the table that is used by the query has partitions, then this field elaborates on the partition that the query will use.
5. type (JSON name: access_type)
The type field explains the type of join that is being used. The type field can represent various types of joins that are possible as described in the following table:
| Column 1 | Column 2 |
| system | Applies to system tables and contains a record. |
| const | This type represents a value that doesn’t change. It’s fast because the record of this type will be read only once. Here’s an example: SELECT * FROM tbl_example WHERE ID=1; |
| eq_ref | The usage of this type is among the best choice apart from the const type. The reason for it is that the eq_ref will be used in all index possibilities and a record is read among the combinations of the previous tables. Here is an example: SELECT * FROM tbl_example, invoices WHERE tbl_example.ID=invoices.clientID; |
| ref | All the records that are found in a table are matched in the index.For optimal performance, the ref type needs to be used with an indexed column. Here is an example: SELECT * FROM tbl_example WHERE ID=1; |
| fulltext | This type is used specifically to perform full text searches on an index. |
| ref_or_null | This type is similar to the ref type but with the exception that MySQL will perform an additional step here to detect rows with NULL values. Here is an example: SELECT * FROM tbl_example WHERE ID=1 OR last_billing IS NULL; |
| Column 1 | Column 2 |
| index_merge | This type is indicative of Merge Optimization being used. Since this type is used by the indexes, the key_len type will hold a larger list of values. |
| unique_subquery | When subqueries are used, this type replaces the eq_ref type with some values of the IN function. Here is an example: 10 IN (SELECT ID FROM tbl_example WHERE ID < 100000) |
| index_subquery | This type is generally used when there are non-unique values. It is quite similar to the unique_subquery type but it will replace the IN function. |
| range | This type will be used in an extended comparison between two values. The EXPLAIN plan command will show the indexes that are being used. Here is an example: SELECT * FROM tbl_example WHERE ID BETWEEN 1000 and 2000; |
| index | The index type is the same as a range with the exception that the full scan is performed at the index and not at the table level. If all the criteria meet with the index, then there will be an additional column with more explanations. |
| all | Though this type exists, it is not recommended because it indicates that MySQL will do a full scan of the table and it will not use any indexes. |
The Extra column of the EXPLAIN tool’s Result
The extra column of the EXPLAIN command’s result contains key information on how MySQL will support any given query. Thus, the information provided by this column makes it very important for our optimization process
A Pro Optimization Tip: When you are performing optimizations and trying to make your query run faster, check the information in the EXTRA column. See if you can find messages such as “Using Filesort and Using Temporary Table”. We will cover how to deal with scenarios where these messages appear later,
Next, let’s look at the most important messages you’ll find in the EXPLAIN view:
- Full scan on NULL key: You get this message when MySQL isn’t able to access an index for a subquery.
- Impossible HAVING: This message indicates that the HAVING clause isn’t able to select any records.
- Impossible WHERE: this message indicates that the WHERE clause cannot find records.
- Not exists: As a rule of thumb, MySQL is capable of optimizing the LEFT JOIN but it cannot evaluate the previous tables. It can only find a single record. Here is an example:
SELECT * FROM tbl_example LEFT JOIN tbl_example_history ON tbl_example.id=tbl_example_history.id WHERE t2.id IS NULL;
Now, let’s consider that the tbl_example_history.id is defined as NOT NULL.
In this case, MySQL scans the tbl_example table and looks for rows in tbl_example_history using values from the tbl_example.id column. If MySQL finds a matching line in tbl_example_history, it knows that tbl_example_history.id can never be NULL and does not scan the rest of the rows in the tbl_example_history table for the same ID value.
- Using filesort: MySQL does additional work to find the records in the requested ranking. The rank the records, MySQL browses all the records relative to the join. It will then store the search key and pointer records that are found. Finally, the records that are found will be sorted according to the requested order.
- Using index: This message is purely used for guidance only and it tells you that MySQL used an index to execute your query.
- Using index condition: This message indicates that MySQL has used tables and index tuples to read records through an index.
- Using join buffer: When MySQL uses joins that were previously executed and stores in memory, this message is used. The memory that is used to store the join details is called join_buffer. This is not a desirable message to have because if you do not have enough memory, MySQL will use the disk to compensate for the execution.
- Using temporary: This message is displayed when either a Group By clause or a Sort By clause has been used. In such scenarios, MySQL will store your data in a temporary table to work with the records. There is another reason why MySQL might have used the temporary table and that is because there was a shortage of memory. If there was a memory shortage, then the RAM requirements of the MySQL server need to be revisited.
Summary
Through this guide, you have now learned how to effectively read the EXPLAIN plan tool and how to interpret the MySQL explain plan view. You have also got a deeper understanding of the various options and messages that can be shown to you every time you run the EXPLAIN on a query.