EXPLAIN returns a row of information for each table used in the
SELECT statement. It lists the tables in the output in the order that MySQL would
read them while processing the statement. 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.
EXTENDED keyword is used,
EXPLAIN produces extra information that can be viewed by issuing a
SHOW WARNINGS statement following the
EXPLAIN EXTENDED also displays the
EXPLAIN EXTENDED Output Format".
You cannot use the
PARTITIONS keywords together in the same
EXPLAIN Output Columns
||The table for the output row|
||The matching partitions|
||The join type|
||The possible indexes to choose|
||The index actually chosen|
||The length of the chosen key|
||The columns compared to the index|
||Estimate of rows to be examined|
||Percentage of rows filtered by table condition|
SELECT identifier. This is the sequential number of the
within the query. The value can be
NULL if the row refers to the union
result of other rows. In this case, the
table column shows a value like
<union to indicate that the row refers to the
union of the rows with
id values of
The type of
SELECT, which can be any of those shown in the following table.
||Second or later
||Second or later
||Result of a
||A subquery for which the result cannot be cached and must be re-evaluated for each row of the outer query|
||The second or later select in a
DEPENDENT typically signifies the use of a correlated subquery. See Section 188.8.131.52, "Correlated Subqueries".
DEPENDENT SUBQUERY evaluation differs from
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
Cacheability of subqueries differs from caching of query results in the query cache (which is described in Section 184.108.40.206, "How the Query Cache Operates"). Subquery caching occurs during query execution, whereas the query cache is used to store results only after query execution finishes.
The name of the table to which the row of output refers. This can also be one of the following values:
<union: The row refers to the union of the rows with
id values of
<derived: The row refers to the derived table result for the row with an
id value of
derived table may result, for example, from a subquery in the
<subquery: The row refers to the result of a materialized subquery for the row with an
id value of
N. See Section
220.127.116.11, "Optimizing Subqueries with Subquery Materialization".
The partitions from which records would be matched by the query. This column is displayed only if
PARTITIONS keyword is used. The value is
NULL for nonpartitioned tables. See Section
17.3.5, "Obtaining Information About Partitions".
The join type. For descriptions of the different types, see
EXPLAIN Join Types.
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
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
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 query with
EXPLAIN again. See Section
ALTER TABLE Syntax".
To see what indexes a table has, use
SHOW INDEX FROM .
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
NULL, MySQL found no index to use for executing the query more
To force MySQL to use or ignore an index listed in the
USE INDEX, or
IGNORE INDEX in your query. See Section
18.104.22.168, "Index Hint Syntax".
MyISAM tables, running
ANALYZE TABLE helps the optimizer choose better indexes. For
MyISAM tables, myisamchk --analyze does the same. See Section
ANALYZE TABLE Syntax", and Section
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.
filtered column indicates an estimated percentage of table rows
that will be filtered by the table condition. That is,
rows shows the
estimated number of rows examined and
100 shows the number of rows that will be joined with previous
tables. This column is displayed if you use
This column contains additional information about how MySQL resolves the query. For descriptions of
the different values, see
type column of
EXPLAIN output describes how tables are joined. The following list describes the
join types, 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
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
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
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 process
SELECT * FROM
column;SELECT * FROM
All rows with matching index values are read from this table for each combination of rows from the
ref is used if the join uses only a leftmost prefix of the key or
if the key is not a
PRIMARY KEY or
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
but with the addition that MySQL does an extra search for rows that contain
values. This join type optimization is used most often in resolving subqueries. In the following
examples, MySQL can use a
ref_or_null join to process
SELECT * FROM
This join type indicates that the Index Merge optimization is used. In this case, the
key column in the output row contains a list of indexes used, and
key_len contains a list of the longest key parts for the indexes used.
For more information, see Section 8.13.2, "Index Merge
This type replaces
ref for some
IN subqueries of the
is just an index lookup function that replaces the subquery completely for better efficiency.
This join type is similar to
unique_subquery. It replaces
subqueries, but it works for nonunique indexes in subqueries of the following form:
Only rows that are in a given range are retrieved, using an index to select the rows. The
key column in the output row indicates which index is used. The
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
index join type is the same as
except that the index tree is scanned. This occurs two ways:
If the index is a covering index for the queries and can be used to
satisfy all data required from the table, only the index tree is scanned. In this case, the
Extra column says
Using index. An
index-only scan usually is faster than
ALL because the size of
the index usually is smaller than the table data.
A full table scan is performed using reads from the index to look up
data rows in index order.
Uses index does not appear in the
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
Extra column of
EXPLAIN output 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, look out for
Extra values of
const row not found
For a query such as
SELECT ... FROM , the table was empty.
Deleting all rows
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.
The semi-join FirstMatch join shortcutting strategy is used for
Full scan on NULL key
This occurs for subquery optimization as a fallback strategy when the optimizer cannot use an index-lookup access method.
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 tables
The semi-join LooseScan strategy is used.
n are key part numbers.
No matching min/max row
No row satisfies the condition for a query such as
SELECT MIN(...) FROM ...
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 matching rows after partition pruning
No tables used
The query has no
FROM clause, or has a
EXPLAIN displays this value when there is no
SELECT part. For example, it appears for
INSERT INTO t VALUES(10) because that is equivalent to
INSERT INTO t SELECT 10 FROM DUAL.
MySQL was able to do a
LEFT JOIN 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
LEFT 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
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 in
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 same
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 use a
index_merge access method to retrieve rows. This is not very
fast, but is faster than performing a join with no index at all. The applicability criteria are as
described in Section 8.13.1, "Range Optimization", and Section 8.13.2, "Index Merge Optimization",
with the exception that all column values for the preceding table are known and considered to be
Indexes are numbered beginning with 1, in the same order as shown by
SHOW INDEX for the table. The index map value
N is a bitmask value that indicates which indexes are
candidates. For example, a value of
0x19 (binary 11001) means that
indexes 1, 4, and 5 will be considered.
This indicates how many directory scans the server performs when processing a query for
INFORMATION_SCHEMA tables, as described in Section
INFORMATION_SCHEMA Queries". The value of
N can be 0, 1, or
Select tables optimized away
The query contained only aggregate functions (
MAX()) that were all resolved using an index, or
MyISAM, and no
GROUP BY clause. The optimizer determined that only one row should be
These values indicate file-opening optimizations that apply to queries for
tables, as described in Section 8.2.4,
Skip_open_table: Table files do not need to
be opened. The information has already become available within the query by scanning the
Open_frm_only: Only the table's
.frm file need be opened.
Open_trigger_only: Only the table's
.TRG file need be opened.
Open_full_table: The unoptimized
information lookup. The
.MYI files must be
This indicates temporary table use for the semi-join Duplicate Weedout strategy.
unique row not found
For a query such as
SELECT ... FROM , no rows satisfy the condition for a
UNIQUE index or
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 match the
WHERE clause. The keys then are sorted and
the rows are retrieved in sorted order. See Section
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.
Extra column also says
it means the index is being used to perform lookups of key values. Without
where, the optimizer may be reading the index to avoid reading data rows but not using it
for lookups. For example, if the index is a covering index for the query, the optimizer may scan it
without using it for lookups.
InnoDB tables that have a user-defined clustered index, that index
can be used even when
Using index is absent from the
Extra column. This is the case if
Using index condition
Tables are read by accessing index tuples and testing them first to determine whether to read full table rows. In this way, index information is used to defer ("push down") reading full table rows unless it is necessary. See Section 8.13.4, "Index Condition Pushdown Optimization".
Using index for group-by
Similar to the
Using index table access method,
index for group-by indicates that MySQL found an index that can be used to retrieve all
columns of a
GROUP BY or
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 8.13.14, "
Using join buffer (Block Nested Loop),
Using join buffer (Batched Key Access)
Tables from earlier joins are read in portions into the join buffer, and then their rows are used
from the buffer to perform the join with the current table.
Loop) indicates use of the Block Nested-Loop algorithm and
Key Access) indicates use of the Batched Key Access algorithm. That is, the keys from the
table on the preceding line of the
EXPLAIN output will be buffered, and the matching rows will be
fetched in batches from the table represented by the line in which
Tables are read using the Multi-Range Read optimization strategy. See Section 8.13.11, "Multi-Range Read Optimization".
To resolve the query, MySQL needs to create a temporary table to hold the result. This typically
happens if the query contains
GROUP BY and
BY clauses that list columns differently.
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
Extra value is
Using where and the table join type is
Using where with pushed condition
This item applies to
You can get a good indication of how good a join is by taking the product of the values in the
rows column of 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
SELECT statements to execute and which to abort. See Section
8.11.2, "Tuning Server Parameters".
The following example shows how a multiple-table join can be optimized progressively based on the information
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 distributed.
Initially, before any optimizations have been performed, the
EXPLAIN statement produces the following information:
table type possible_keys key key_len ref rows Extraet ALL PRIMARY NULL NULL NULL 74do ALL PRIMARY NULL NULL NULL 2135et_1 ALL PRIMARY NULL NULL NULL 74tt 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. 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
To fix this disparity between column lengths, use
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
statement again produces this result:
table type possible_keys key key_len ref rows Extratt ALL AssignedPC, NULL NULL NULL 3872 Using ClientID, where ActualPCdo 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
= et_1.EMPLOYID and
tt.ClientID = do.CUSTNMBR comparisons:
ALTER TABLE tt MODIFY AssignedPC VARCHAR(15),->
MODIFY ClientID VARCHAR(15);
After that modification,
EXPLAIN produces the output shown here:
table type possible_keys key key_len ref rows Extraet ALL PRIMARY NULL NULL NULL 74tt ref AssignedPC, ActualPC 15 et.EMPLOYID 52 Using ClientID, where ActualPCet_1 eq_ref PRIMARY PRIMARY 15 tt.AssignedPC 1do 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
tt.ActualPC column 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 Extratt ALL AssignedPC NULL NULL NULL 3872 Using ClientID, where ActualPCet eq_ref PRIMARY PRIMARY 15 tt.ActualPC 1et_1 eq_ref PRIMARY PRIMARY 15 tt.AssignedPC 1do eq_ref PRIMARY PRIMARY 15 tt.ClientID 1
Note that the
rows column in the output
EXPLAIN is an educated guess from the MySQL join optimizer. 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
STRAIGHT_JOIN in your
statement and trying to list the tables in a different order in the
It is possible in some cases to execute statements that modify data when
EXPLAIN SELECT is used with a subquery; for more information, see Section
22.214.171.124, "Subqueries in the