Guide To Mastering The MySQL Query Execution Plan

Guide To Mastering The MySQL Query Execution
Plan
In this PPT, 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.

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.

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_subq
uery
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
index_subque
ry
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.
6. 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.
7. Using index:
This message is purely used for guidance only and it tells you that MySQL used an index to
execute your query.

8. Using index condition:
This message indicates that MySQL has used tables and index tuples to read records
through an index.
9. 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.
10. 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.

Guide To Mastering The MySQL Query Execution Plan

Recommended

More Related Content

What's hot (20)

Similar to Guide To Mastering The MySQL Query Execution Plan (20)

Recently uploaded (20)

Guide To Mastering The MySQL Query Execution Plan