MySQL: Understanding EXPLAIN

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

To use 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 SELECT, DELETE, INSERT, REPLACE, and 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 SELECT)
• 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 fulltext.

Partitions

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 p1 and 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 possible_keys, key, and 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:

Value	Meaning
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 PRIMARY KEY or UNIQUE INDEX with all key columns defined as NOT NULL)
ref	The indexed column was accessed using an equality operator
fulltext	Operation is using the table's fulltext index
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 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.

Filtered Rows

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:

Value	Meaning
Backward index scan	MySQL uses a descending index to complete the query
const row not found	The queried table was empty
Distinct	MySQL is scouring the database for any distinct values that might appear in the column
No tables used	The query has no FROM clause
Using index for group-by	MySQL was able to use a certain index to optimize GROUP BY operations

EXPLAIN can 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.

Summary

The 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.

Lukas is an ethical hacker, a MySQL database administrator, and a frequent conference speaker. Since 2014 Lukas has found and responsibly disclosed security flaws in some of the most visited websites in Lithuania and abroad including advertising, gift-buying, gaming, hosting websites as well as some websites of government institutions. Lukas runs one of the biggest & fastest data breach search engines in the world - BreachDirectory.com and frequently blogs in multiple places educating people about information security and other topics. He also runs his own blog over at lukasvileikis.com