Explain: MySQL query optimization

There is a tool integrated into MySQL which makes it possible to obtain very interesting information, and which will give you very good leads for optimization when building your queries and your indexes. This is EXPLAIN.

Using EXPLAIN

Its use could not be simpler. Just add it in front of a MySQL query.
For example :

EXPLAIN SELECT nom, prenom FROM utilisateurs WHERE nom = 'Dupont';

You can also get more information using the EXTENDED parameter (by running the EXPLAIN EXTENDED command).

You will then have a result like this, which will tell you the execution plan of the query:

Results interpretation

Let’s take a closer look at the result of EXPLAIN.

id : this column gives a different id to each SELECT (in the case of subqueries). In the example below, all the rows have 1 in id because they are part of a single SELECT clause.
select_type : the select_type depends on your query and any nesting of queries and sub-queries, or union between several SELECTs. You may have as value for example SIMPLE for a simple SELECT, PRIMARY for the first SELECT of a nesting, DERIVED for derived SELECTs, UNION…
table : the table or the alias concerned by the line.
type : as join type, one of the most important values returned by EXPLAIN. It can take the following values, from the best possible value to the worst:
- system : the table has only one row
- const : the table returns at most one row that is indexed. The table is then read only once, and the value is considered as a constant.
- eq_ref : the PRIMARY or UNIQUE NOT NULL type index is used completely by the join. For each combination of the preceding tables, only one row is read from this table.
- ref : all table rows with matching index values are read for each combination of the previous tables. The difference with eq_ref is therefore that the join cannot select a single row.
- fulltext : the join is based on a fulltext index.
- ref_or_null : identical to ref with the addition of a search for null values.
- index_merge : the join uses the “Index Merge” optimization, ie to respond to the query, it will merge several existing indexes on the table. At this point, a question might arise as to whether a multi-column index could not be set. You can see this article on the Percona site which deals with the subject, as well as the official doc on merging indexes .
- unique_subquery : equivalent to eq_ref for certain subqueries on PRIMARY KEYs or unique indexes used in an IN.
- index_subquery : equivalent to unique_subquery but non-unique sub-indexes.
- range : recovery of lines corresponding to a range of data, in particular with operators such as BETWEEN, >, <, >=, <= …
- index : equivalent to ALL (see below), but with the difference that the index tree is scanned completely instead of the table (which is generally faster, since the index is mostly smaller than Table).
- all : the whole table is scanned, this generally indicates that the indexes or the query must be reviewed.
possible_keys : indicates the indexes that MySQL believes may be relevant to answer the query. If you have “NULL” in this column, you can take a closer look to see if creating an index would be necessary.
key : key that MySQL has decided to use, which can also be another index than the one or those listed in the possible ones. This return value, together with the previous one, is very important to determine if your indexes are good and relevant, and if an index creation could be interesting.
key_len : length of the retained index.
ref : indicates the field that is compared to “key”.
rows : estimate of the number of rows that MySQL thinks it should examine (this may be different from the actual number).
filtered (only with EXTENDED): gives an estimate of the percentage of rows that will be filtered by the condition(s).
extra : additional information that EXPLAIN returns. You have the exhaustive list of possible values in the official documentation . Note some interesting values all the same, such as ” using temporary ” which indicates that the query will have to create a temporary table, and ” using filesort ” which indicates that an additional pass will be necessary to sort the results. If you have a ” using index “, it’s rather good news, it means that you have a covering index and that MySQL will be able to respond without access to the table.

Use cases and unused index

Compared to the analysis of the results of EXPLAIN, you have seen that this will allow you to see if your indexes are well used, and therefore if they are relevant. This will also allow you to more easily optimize your queries.
For example, I was thus able to realize on a base from which I had recovered the maintenance of certain concerns. One of the queries was taking a long time to run, while the indexes seemed relevant. Using EXPLAIN, I could see that a table’s index principal was not being used, while it was being used as a join between the two largest tables in the query. By digging into the question, I could see that the fields were not equivalent between the two tables (the two varchar did not have the same size, and one of them oddly had a different encoding). After modifying the size and the encoding, the index is used and the execution time is divided by 10.

A good idea is to couple this analysis to the queries returned to you by MySQL’s Slow Queries Log .