MySQL: Understanding EXPLAIN

Tags: guide, mysql
Published: 2021-07-29 20:05:54

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 `JOIN`s 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](https://arctype.com) 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