EXPLAIN returns a row of
information for each table used in the
SELECT statement. The tables are
listed in the output in the order that MySQL would read them
while processing the query. MySQL resolves all joins using a
nested-loop join method. This means that MySQL reads a row from
the first table, and then finds a matching row in the second
table, the third table, and so on. When all tables are
processed, MySQL outputs the selected columns and backtracks
through the table list until a table is found for which there
are more matching rows. The next row is read from this table and
the process continues with the next table.
In MySQL version 4.1, the
output format was changed to work better with constructs such as
UNION statements, subqueries, and
derived tables. Most notable is the addition of two new columns:
select_type. You do
not see these columns when using servers older than MySQL 4.1.
EXPLAIN syntax also was augmented
to permit the
EXTENDED keyword. When this
keyword is used,
extra information that can be viewed by issuing a
SHOW WARNINGS statement following
EXPLAIN statement. This
information displays how the optimizer qualifies table and
column names in the
statement, what the
like after the application of rewriting and optimization rules,
and possibly other notes about the optimization process.
Each output row from
provides information about one table, and each row contains the
The type of
SELECT, which can
be any of those shown in the following table.
|Second or later |
|Second or later |
|Result of a |
|Derived table |
|A subquery for which the result cannot be cached and must be re-evaluated for each row of the outer query|
DEPENDENT typically signifies the use of
a correlated subquery. See
Section 18.104.22.168, “Correlated Subqueries”.
DEPENDENT SUBQUERY evaluation differs
UNCACHEABLE SUBQUERY evaluation. For
DEPENDENT SUBQUERY, the subquery is
re-evaluated only once for each set of different values of
the variables from its outer context. For
UNCACHEABLE SUBQUERY, the subquery is
re-evaluated for each row of the outer context. Cacheability
of subqueries is subject to the restrictions detailed in
Section 22.214.171.124, “How the Query Cache Operates”. For example,
referring to user variables makes a subquery uncacheable.
The table to which the row of output refers.
The join type. The different join types are listed here, ordered from the best type to the worst:
The table has only one row (= system table). This is a
special case of the
const join type.
The table has at most one matching row, which is read at
the start of the query. Because there is only one row,
values from the column in this row can be regarded as
constants by the rest of the optimizer.
const tables are very
fast because they are read only once.
SELECT * FROM
primary_key=1; SELECT * FROM
One row is read from this table for each combination of
rows from the previous tables. Other than the
const types, this is
the best possible join type. It is used when all parts
of an index are used by the join and the index is a
PRIMARY KEY or
eq_ref can be used
for indexed columns that are compared using the
= operator. The comparison value can
be a constant or an expression that uses columns from
tables that are read before this table. In the following
examples, MySQL can use an
eq_ref join to
SELECT * FROM
column; SELECT * FROM
All rows with matching index values are read from this
table for each combination of rows from the previous
ref is used
if the join uses only a leftmost prefix of the key or if
the key is not a
PRIMARY KEY or
UNIQUE index (in other words, if the
join cannot select a single row based on the key value).
If the key that is used matches only a few rows, this is
a good join type.
SELECT * FROM
expr; SELECT * FROM
column; SELECT * FROM
The join is performed using a
This join type is like
ref, but with the
addition that MySQL does an extra search for rows that
NULL values. This join type
optimization was added for MySQL 4.1.1 and is used
mostly when resolving subqueries. In the following
examples, MySQL can use a
ref_or_null join to
SELECT * FROM
This type replaces
ref for some
IN subqueries of the following form:
just an index lookup function that replaces the subquery
completely for better efficiency.
This join type is similar to
IN subqueries, but it works
for nonunique indexes in subqueries of the following
Only rows that are in a given range are retrieved, using
an index to select the rows. The
column in the output row indicates which index is used.
key_len contains the longest key
part that was used. The
ref column is
NULL for this type.
SELECT * FROM
key_column= 10; SELECT * FROM
key_columnBETWEEN 10 and 20; SELECT * FROM
key_columnIN (10,20,30); SELECT * FROM
key_part1= 10 AND
This join type is the same as
ALL, except that
only the index tree is scanned. This usually is faster
the index file usually is smaller than the data file.
MySQL can use this join type when the query uses only columns that are part of a single index.
A full table scan is done for each combination of rows
from the previous tables. This is normally not good if
the table is the first table not marked
const, and usually
very bad in all other cases.
Normally, you can avoid
ALL by adding
indexes that enable row retrieval from the table based
on constant values or column values from earlier tables.
possible_keys column indicates which
indexes MySQL can choose from use to find the rows in this
table. Note that this column is totally independent of the
order of the tables as displayed in the output from
EXPLAIN. That means that some
of the keys in
possible_keys might not be
usable in practice with the generated table order.
If this column is
NULL, there are no
relevant indexes. In this case, you may be able to improve
the performance of your query by examining the
WHERE clause to check whether it refers
to some column or columns that would be suitable for
indexing. If so, create an appropriate index and check the
EXPLAIN again. See
Section 12.1.2, “
ALTER TABLE Syntax”.
To see what indexes a table has, use
key column indicates the key (index)
that MySQL actually decided to use. If MySQL decides to use
one of the
possible_keys indexes to look
up rows, that index is listed as the key value.
It is possible that
key will name an
index that is not present in the
possible_keys value. This can happen if
none of the
possible_keys indexes are
suitable for looking up rows, but all the columns selected
by the query are columns of some other index. That is, the
named index covers the selected columns, so although it is
not used to determine which rows to retrieve, an index scan
is more efficient than a data row scan.
InnoDB, a secondary index might cover
the selected columns even if the query also selects the
primary key because
InnoDB stores the
primary key value with each secondary index. If
found no index to use for executing the query more
To force MySQL to use or ignore an index listed in the
possible_keys column, use
USE INDEX, or
IGNORE INDEX in your query. See
Section 126.96.36.199, “Index Hint Syntax”.
helps the optimizer choose better indexes. For
MyISAM tables, myisamchk
--analyze does the same. See
Section 188.8.131.52, “
ANALYZE TABLE Syntax”, and
Section 6.6, “
MyISAM Table Maintenance and Crash Recovery”.
key_len column indicates the length
of the key that MySQL decided to use. The length is
NULL if the
NULL. Note that the value of
key_len enables you to determine how many
parts of a multiple-part key MySQL actually uses.
ref column shows which columns or
constants are compared to the index named in the
key column to select rows from the table.
rows column indicates the number of
rows MySQL believes it must examine to execute the query.
InnoDB tables, this number
is an estimate, and may not always be exact.
This column contains additional information about how MySQL
resolves the query. The following list explains the values
that can appear in this column. If you want to make your
queries as fast as possible, you should look out for
Extra values of
const row not found
For a query such as
SELECT ... FROM
, the table
MySQL is looking for distinct values, so it stops searching for more rows for the current row combination after it has found the first matching row.
HAVING clause is always false and
cannot select any rows.
WHERE clause is always false and
cannot select any rows.
Impossible WHERE noticed after reading const
No matching min/max row
No row satisfies the condition for a query such as
SELECT MIN(...) FROM ... WHERE
no matching row in const table
For a query with a join, there was an empty table or a table with no rows satisfying a unique index condition.
No tables used
The query has no
FROM clause, or has
FROM DUAL clause.
MySQL was able to do a
optimization on the query and does not examine more rows
in this table for the previous row combination after it
finds one row that matches the
JOIN criteria. Here is an example of the type
of query that can be optimized this way:
SELECT * FROM t1 LEFT JOIN t2 ON t1.id=t2.id WHERE t2.id IS NULL;
t2.id is defined as
NOT NULL. In this case, MySQL scans
t1 and looks up the rows in
t2 using the values of
t1.id. If MySQL finds a matching row
t2, it knows that
t2.id can never be
NULL, and does not scan through the
rest of the rows in
t2 that have the
id value. In other words, for
each row in
t1, MySQL needs to do
only a single lookup in
regardless of how many rows actually match in
Range checked for each record (index map:
MySQL found no good index to use, but found that some of
indexes might be used after column values from preceding
tables are known. For each row combination in the
preceding tables, MySQL checks whether it is possible to
method to retrieve rows. The applicability criteria are
as described in Section 184.108.40.206, “Range Optimization”,
with the exception that all column values for the
preceding table are known and considered to be
This is not very fast, but is faster than performing a join with no index at all.
Indexes are numbered beginning with 1, in the same order
as shown by
for the table. The index map value
N is a bitmask value that
indicates which indexes are candidates. For example, a
0x19 (binary 11001) means
that indexes 1, 4, and 5 will be considered.
Select tables optimized away
The query contained only aggregate functions
MAX()) that were all
resolved using an index, or
MyISAM, and no
BY clause. The optimizer determined that only
one row should be returned.
unique row not found
For a query such as
SELECT ... FROM
, no rows
satisfy the condition for a
PRIMARY KEY on the table.
MySQL must do an extra pass to find out how to retrieve
the rows in sorted order. The sort is done by going
through all rows according to the join type and storing
the sort key and pointer to the row for all rows that
WHERE clause. The keys then
are sorted and the rows are retrieved in sorted order.
See Section 220.127.116.11, “
ORDER BY Optimization”.
The column information is retrieved from the table using only information in the index tree without having to do an additional seek to read the actual row. This strategy can be used when the query uses only columns that are part of a single index.
Using index for group-by
Similar to the
Using index table
Using index for
group-by indicates that MySQL found an index
that can be used to retrieve all columns of a
GROUP BY or
DISTINCT query without any extra disk
access to the actual table. Additionally, the index is
used in the most efficient way so that for each group,
only a few index entries are read. For details, see
Section 18.104.22.168, “
GROUP BY Optimization”.
To resolve the query, MySQL needs to create a temporary
table to hold the result. This typically happens if the
GROUP BY and
ORDER BY clauses that list columns
WHERE clause is used to restrict
which rows to match against the next table or send to
the client. Unless you specifically intend to fetch or
examine all rows from the table, you may have something
wrong in your query if the
value is not
Using where and the
table join type is
index. Even if you
are using an index for all parts of a
WHERE clause, you may see
Using where if the column can be
You can get a good indication of how good a join is by taking
the product of the values in the
EXPLAIN output. This
should tell you roughly how many rows MySQL must examine to
execute the query. If you restrict queries with the
max_join_size system variable,
this row product also is used to determine which multiple-table
SELECT statements to execute and
which to abort. See Section 7.8.2, “Tuning Server Parameters”.
The following example shows how a multiple-table join can be
optimized progressively based on the information provided by
EXPLAIN SELECT tt.TicketNumber, tt.TimeIn, tt.ProjectReference, tt.EstimatedShipDate, tt.ActualShipDate, tt.ClientID, tt.ServiceCodes, tt.RepetitiveID, tt.CurrentProcess, tt.CurrentDPPerson, tt.RecordVolume, tt.DPPrinted, et.COUNTRY, et_1.COUNTRY, do.CUSTNAME FROM tt, et, et AS et_1, do WHERE tt.SubmitTime IS NULL AND tt.ActualPC = et.EMPLOYID AND tt.AssignedPC = et_1.EMPLOYID AND tt.ClientID = do.CUSTNMBR;
For this example, make the following assumptions:
The columns being compared have been declared as follows.
The tables have the following indexes.
tt.ActualPC values are not evenly
Initially, before any optimizations have been performed, the
EXPLAIN statement produces the
table type possible_keys key key_len ref rows Extra et ALL PRIMARY NULL NULL NULL 74 do ALL PRIMARY NULL NULL NULL 2135 et_1 ALL PRIMARY NULL NULL NULL 74 tt ALL AssignedPC, NULL NULL NULL 3872 ClientID, ActualPC Range checked for each record (index map: 0x23)
ALL for each table, this
output indicates that MySQL is generating a Cartesian product of
all the tables; that is, every combination of rows. This takes
quite a long time, because the product of the number of rows in
each table must be examined. For the case at hand, this product
is 74 × 2135 × 74 × 3872 = 45,268,558,720
rows. If the tables were bigger, you can only imagine how long
it would take.
One problem here is that MySQL can use indexes on columns more
efficiently if they are declared as the same type and size. (For
ISAM tables, indexes may not be used at all
unless the columns are declared the same.) In this context,
CHAR are considered the same if
they are declared as the same size.
tt.ActualPC is declared as
CHAR(15), so there is a length mismatch.
To fix this disparity between column lengths, use
ALTER TABLE to lengthen
ActualPC from 10 characters to 15 characters:
ALTER TABLE tt MODIFY ActualPC VARCHAR(15);
et.EMPLOYID are both
VARCHAR(15). Executing the
EXPLAIN statement again produces
table type possible_keys key key_len ref rows Extra tt ALL AssignedPC, NULL NULL NULL 3872 Using ClientID, where ActualPC do ALL PRIMARY NULL NULL NULL 2135 Range checked for each record (index map: 0x1) et_1 ALL PRIMARY NULL NULL NULL 74 Range checked for each record (index map: 0x1) et eq_ref PRIMARY PRIMARY 15 tt.ActualPC 1
This is not perfect, but is much better: The product of the
rows values is less by a factor of 74. This
version executes in a couple of seconds.
A second alteration can be made to eliminate the column length
mismatches for the
ALTER TABLE tt MODIFY AssignedPC VARCHAR(15),->
MODIFY ClientID VARCHAR(15);
After that modification,
produces the output shown here:
table type possible_keys key key_len ref rows Extra et ALL PRIMARY NULL NULL NULL 74 tt ref AssignedPC, ActualPC 15 et.EMPLOYID 52 Using ClientID, where ActualPC et_1 eq_ref PRIMARY PRIMARY 15 tt.AssignedPC 1 do eq_ref PRIMARY PRIMARY 15 tt.ClientID 1
At this point, the query is optimized almost as well as
possible. The remaining problem is that, by default, MySQL
assumes that values in the
are evenly distributed, and that is not the case for the
tt table. Fortunately, it is easy to tell
MySQL to analyze the key distribution:
ANALYZE TABLE tt;
With the additional index information, the join is perfect and
EXPLAIN produces this result:
table type possible_keys key key_len ref rows Extra tt ALL AssignedPC NULL NULL NULL 3872 Using ClientID, where ActualPC et eq_ref PRIMARY PRIMARY 15 tt.ActualPC 1 et_1 eq_ref PRIMARY PRIMARY 15 tt.AssignedPC 1 do eq_ref PRIMARY PRIMARY 15 tt.ClientID 1
Note that the
rows column in the output from
EXPLAIN is an educated guess from
the MySQL join optimizer. You should check whether the numbers
are even close to the truth by comparing the
rows product with the actual number of rows
that the query returns. If the numbers are quite different, you
might get better performance by using
STRAIGHT_JOIN in your
SELECT statement and trying to
list the tables in a different order in the