Using EXPLAIN to See How Queries Are Executed

The EXPLAIN command tells MySQL to explain to you how a query will be executed. As a very simple example, we might type this:

 explain select e.name, d.name from employee e, department d where e.departmentID = d.departmentID; 

As you can see, we have simply prefixed an unremarkable query with the word EXPLAIN . This will not actually execute the query, but will instead return some information about how MySQL plans to execute the query. You should see some results similar to the following:

[View full width]

 
 [View full width] 
 +----+-------------+-------+--------+---------------+---------+---------+-----------------+------+-------+  id  select_type  table  type    possible_keys  key      key_len  ref  rows  Extra  +----+-------------+-------+--------+---------------+---------+---------+-----------------+------+-------+  1  SIMPLE       e      ALL     NULL           NULL        NULL  NULL  5           1  SIMPLE       d      eq_ref  PRIMARY        PRIMARY        4  e.departmentID  1         +----+-------------+-------+--------+---------------+---------+---------+-----------------+------+-------+  2 rows in set (0.00 sec) 

What does this all mean? You can see at a glance that there is one row in the result set per table in the query. (You can see which table is being discussed in the index column.) The order of the rows shows the order in which the tables will be joined.

The columns in this table are as listed here:

• id This is a sequence number. If there is more than one SELECT in a query ”for example, if you are using a subquery ”each SELECT is numbered.

• select_type This is the type of SELECT statement being performed. Most of the time this will be SIMPLE as in the preceding example, meaning that it's a plain vanilla SELECT . If you are using subqueries, the outer query will be marked PRIMARY and the inner queries will be marked SUBSELECT or DEPENDENT SUBSELECT for correlated subqueries.

• table This is the table this row is about.

• type This is one of the most important columns for optimization. It tells you how the table is being joined to the other tables in the query.

• possible_keys This tells you which indexes could have been used in the query. It will be NULL if there are no relevant indexes.

• key This tells you which index was selected for use in the query. It will be NULL if no index was selected.

• key_len This is the length of the index MySQL decided to use.

• ref This is the value being compared to the key to decide whether to select rows.

• rows This is an estimate of the number of rows from this table MySQL will read to generate the results of the query. You can work out how many rows will be read overall by multiplying the rows' values together. This gives a basic benchmark for how fast the query will run.

• Extra Additional information may be given in this column. For example, the comment Using index means that MySQL can retrieve the result of the query completely from an index without reading data from the table.

So in this case, what is the output telling us?

The join type ALL for the employee table tells us that all rows will be scanned in this table. This is obviously going to be slow if the table has a lot of data in it. In fact, the join type ALL is the worst possible result. You will get this typically if a table has no useful index. The obvious optimization here is to add an index. We'll look at this in a moment.

The row for department has join type eq_ref , which means that a single row will be read from the department table for each row in the employee table. This is one of the best types. The only better values for type are system and const , meaning that the table has only one matching row and can effectively be treated as a constant. This part of the join we are pretty happy with.

These are the other possible values for type:

• ref All rows with matching index values will be read from the table. This is the next best option after eq_ref and represents situations in which you are dealing with non-unique keys.

• range This is not as good as eq_ref or even ref . It means that all rows in a particular range will be read from the table.

• index This is better than ALL , but worse than the other types mentioned previously. Seeing index means that the complete index will be scanned. This is preferable to scanning the complete table, but is far from ideal.

Next, let's look at the values of the possible_keys and key columns. The department table has one option ” PRIMARY , the primary key, which is the one used. The employee table has the value NULL in both of these columns, meaning that there's no key to use and therefore no key will be used. Again, this is a pretty strong hint that we should be adding another index!

Based on this information and assuming that we're going to execute this query reasonably frequently and therefore would like it to be faster, we'll create another index as follows :

 create index ename_did on employee(name, departmentID); 

If we then rerun EXPLAIN , we get the following output:

[View full width]

 
 [View full width] 
 +----+-------------+-------+--------+---------------+-----------+---------+-----------------+------+-------------+  id  select_type  table  type    possible_keys  key        key_len  ref  rows  Extra        +----+-------------+-------+--------+---------------+-----------+---------+-----------------+------+-------------+  1  SIMPLE       e      index   NULL           ename_did       85  NULL  5  Using index    1  SIMPLE       d      eq_ref  PRIMARY        PRIMARY          4  e.departmentID  1               +----+-------------+-------+--------+---------------+-----------+---------+-----------------+------+-------------+  2 rows in set (0.00 sec) 

You can see that there are some changes. The type for employee is now index because we now have an appropriate index to refer to. The new index is listed as a possible key, but it is not actually being used. Under Extra you will see that only the index for this table is used, rather than the table itself. This should be slightly faster.

The most basic use of EXPLAIN is to see where you can make better use of indexes to speed up your queries, but you might discover other approaches to speed up a query.