MySQL: Understanding EXPLAIN
If you have found yourself in the MySQL world, you have probably heard about EXPLAIN. This guide will show you how and when to use it.
Join the DZone community and get the full member experience.Join For Free
Have you ever sought out advice regarding query performance?
EXPLAIN. Want to know what your queries do on a deeper level?
EXPLAIN. Want to know what index is used by your query?
EXPLAIN. After reading this blog post, you should understand the importance of this keyword (if you don't already).
How to Use EXPLAIN
EXPLAIN in its simplest form, append the keyword to the beginning of your query like so:
EXPLAIN SELECT * FROM demo_table WHERE demo_column = 'Demo Value';
A query like the above should produce output similar to the following:
********************* 1. row ********************** id: 1 select_type: SIMPLE table: demo_table partitions: NULL type: const possible_keys: PRIMARY key: PRIMARY key_len: 4 ref: const rows: 1 filtered: 100.00 Extra: NULL
As you can see,
EXPLAIN provides you with quite a lot of data, including the ID of the query and the type of
SELECT query you just ran (
SIMPLE refers to the fact that the query did not use
JOIN operations). You can see which table you just ran the query on, what partitions were used, the type of the query, the indexes that may have been used and were used, the length of the indexes, the number of rows, and whether any percentage of rows were filtered by a condition you specified.
The output above is derived from the
EXPLAIN EXTENDED command-you can append
EXTENDED if you want MySQL to provide you with some further information. The extended information is provided for
UPDATE statements if you are using MySQL 8.0.12 or newer, and only for
SELECT queries in older versions of MySQL or MariaDB. The
EXTENDED command can be used to gain further information about how MySQL executes the query: it can be used to determine, for example, what function is used by MySQL to compare columns to the index to select rows from the table, etc.
What is EXPLAIN?
In the MySQL world,
EXPLAIN is a keyword used to gain information about query execution. In other words, this keyword provides information about how a database executes a certain query — add
EXPLAIN in front of a query that you run, and you will be able to observe:
- The ID of the query.
- The type of your
SELECT(if you are running a
- The table on which your query was running.
- Partitions accessed by your query.
- Types of
JOINs used (if any).
- Indexes from which MySQL could choose.
- Indexes MySQL actually used.
- The length of the index chosen by MySQL.
- The number of rows accessed by the query.
- Columns compared to the index.
- The percentage of rows filtered by a specified condition.
- Any extra information relevant to the query.
There's no doubt that
EXPLAIN provides a lot of information. However, once you grasp its features and understand how to best use it to achieve your performance goals, using it becomes easier.
When to Use EXPLAIN
By now, you should be aware of the power of
EXPLAIN queries in MySQL. However, we have not yet covered when you should use such queries in the first place. In the MySQL space, everything is pretty simple:
EXPLAIN queries can (and should) be used when you are unsure whether your query does what it is supposed to do. So, for example, if you think that you have indexed and partitioned your tables properly, but your queries still refuse to run as fast as you want them to, it might be time to tell them to
EXPLAIN themselves. Get it?
Once you tell your queries to
EXPLAIN themselves, the output you should be keeping an eye on will depend on what you want to optimize, for example:
Fulltext Index + Join
If you want to make sure that your queries are participating in
JOIN operations when using a
FULLTEXT index, keep an eye out for the
select_type column — the value of this column should be
If you have added partitions to your table and want to observe what partitions are used by the query, observe the
partition column. If your MySQL instance is using partitions, in most cases, MySQL deals with all of the queries itself, and you do not have to take any further action, but if you want your queries to use specific partitions, you could use queries like
SELECT * FROM demo_table PARTITION(p1,p2);: a query like so would return all rows from partitions
p2 while excluding rows from all of the other partitions.
Keys, Possible Keys, and Key Lengths
When working with indexes in MySQL, keep an eye on the
key_len columns. The
possible_keys column will tell us what indexes MySQL was able to use, the
key column will tell us what index was chosen and the
key_len column will tell us the length of the chosen key (index). This feature can be handy for designing our indexes, deciding what index to use on a specific workload, dealing with index-related issues (for example, choosing an appropriate length for a covering index), and other index-related hiccups.
Column Data Type
When designing queries, keep an eye out for the
type column. The
type column can be called one of the best fields in the output as far as your query design is concerned — the primary reason is that it displays how MySQL or MariaDB joins the tables:
|system||The table is either empty or has one row|
|const||The value of the column can be treated as a constant (there is one row matching the query).|
|eq_ref||The index is clustered and is being used by the operation (either the index is a
|ref||The indexed column was accessed using an equality operator.|
|fulltext||Operation is using the table's
|index||The entire index is scanned to find a match for the query.|
|all||MySQL scans the entire table to satisfy the query. Perhaps the worst outcome is if you are optimizing query performance in MySQL or MariaDB.|
Columns Compared to an Index
The ref column is also critical, especially if you want to improve your query performance using indexes — this column shows what columns are compared to the index to complete a given request: a value of
const means a constant, while a value of
func means that the value that was used was derived from a function.
Number of Accessed Rows
When designing indexes inside of your database instances, keep an eye on the
rows column too. This column displays how many rows MySQL accessed to complete a given request, which can be very useful when designing indexes. The fewer rows your query can access, the faster your queries will be.
Filtering might also be something worth keeping an eye on. This column indicates an approximate percentage of the rows in the table that are being filtered by a specified condition.
The Extra Column
Keeping an eye out for the
Extra column might not always be necessary, but it's worth keeping in mind that this column can have a bunch of values, including:
||MySQL uses a descending index to complete the query.|
||The queried table was empty.|
||MySQL is scouring the database for any distinct values that might appear in the column.|
||The query has no
||MySQL was able to use a certain index to optimize
EXPLAINcan be used in several different scenarios, but as you can probably tell by now, it's most useful when optimizing the performance of queries or evaluating the results of query optimization.
EXPLAIN statement in MySQL can be used to obtain information about query execution. It is advantageous when designing schemas or indexes and to ensure that our database can use the features provided by MySQL to the greatest extent possible. However, remember that the
EXPLAIN statement is not your only friend in this regard — if you want to optimize the performance of your MySQL instances, SQL clients like Arctype can be of massive assistance, too — feed it your queries, and it will do wonders.
Published at DZone with permission of Everett Berry. See the original article here.
Opinions expressed by DZone contributors are their own.