(mysql.info.gz) EXPLAIN
Info Catalog
(mysql.info.gz) Query Speed
(mysql.info.gz) Query Speed
(mysql.info.gz) Estimating performance
7.2.1 `EXPLAIN' Syntax (Get Information About a `SELECT')
---------------------------------------------------------
EXPLAIN TBL_NAME
Or:
EXPLAIN SELECT SELECT_OPTIONS
The `EXPLAIN' statement can be used either as a synonym for `DESCRIBE'
or as a way to obtain information about how MySQL will execute a
`SELECT' statement:
* The `EXPLAIN TBL_NAME' syntax is synonymous with `DESCRIBE
TBL_NAME' or `SHOW COLUMNS FROM TBL_NAME'.
* When you precede a `SELECT' statement with the keyword `EXPLAIN',
MySQL explains how it would process the `SELECT', providing
information about how tables are joined and in which order.
This section provides information about the second use of `EXPLAIN'.
With the help of `EXPLAIN', you can see when you must add indexes to
tables to get a faster `SELECT' that uses indexes to find records.
If you have a problem with incorrect index usage, you should run
`ANALYZE TABLE' to update table statistics such as cardinality of keys,
which can affect the choices the optimizer makes. `ANALYZE
TABLE' ANALYZE TABLE.
You can also see whether the optimizer joins the tables in an optimal
order. To force the optimizer to use a join order corresponding to the
order in which the tables are named in the `SELECT' statement, begin the
statement with `SELECT STRAIGHT_JOIN' rather than just `SELECT'.
`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 single-sweep multi-join method. This means that MySQL
reads a row from the first table, then finds a matching row in the
second table, then in the third table, and so on. When all tables are
processed, it 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 `EXPLAIN' 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: `id'
and `select_type'. You will not see these columns when using servers
older than MySQL 4.1.
Each output row from `EXPLAIN' provides information about one table, and
each row consists of the following columns:
`id'
The `SELECT' identifier. This is the sequential number of the
`SELECT' within the query.
`select_type'
The type of `SELECT', which can be any of the following:
`SIMPLE'
Simple `SELECT' (not using `UNION' or subqueries)
`PRIMARY'
Outermost `SELECT'
`UNION'
Second or later `SELECT' statement in a `UNION'
`DEPENDENT UNION'
Second or later `SELECT' statement in a `UNION', dependent on
outer query
`UNION RESULT'
Result of a `UNION'.
`SUBQUERY'
First `SELECT' in subquery
`DEPENDENT SUBQUERY'
First `SELECT' in subquery, dependent on outer query
`DERIVED'
Derived table `SELECT' (subquery in `FROM' clause)
`table'
The table to which the row of output refers.
`type'
The join type. The different join types are listed here, ordered
from the best type to the worst:
`system'
The table has only one row (= system table). This is a
special case of the `const' join type.
`const'
The table has at most one matching row, which will be 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!
`const' is used when you compare all parts of a `PRIMARY KEY'
or `UNIQUE' index with constant values. In the following
queries, TBL_NAME can be used as a `const' table:
SELECT * FROM TBL_NAME WHERE PRIMARY_KEY=1;
SELECT * FROM TBL_NAME
WHERE PRIMARY_KEY_PART1=1 AND PRIMARY_KEY_PART2=2;
`eq_ref'
One row will be 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 `UNIQUE' index.
`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
process REF_TABLE:
SELECT * FROM REF_TABLE,OTHER_TABLE
WHERE REF_TABLE.KEY_COLUMN=OTHER_TABLE.COLUMN;
SELECT * FROM REF_TABLE,OTHER_TABLE
WHERE REF_TABLE.KEY_COLUMN_PART1=OTHER_TABLE.COLUMN
AND REF_TABLE.KEY_COLUMN_PART2=1;
`ref'
All rows with matching index values will be read from this
table for each combination of rows from the previous tables.
`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.
`ref' can be used for indexed columns that are compared using
the `=' operator.
In the following examples, MySQL can use a `ref' join to
process REF_TABLE:
SELECT * FROM REF_TABLE WHERE KEY_COLUMN=EXPR;
SELECT * FROM REF_TABLE,OTHER_TABLE
WHERE REF_TABLE.KEY_COLUMN=OTHER_TABLE.COLUMN;
SELECT * FROM REF_TABLE,OTHER_TABLE
WHERE REF_TABLE.KEY_COLUMN_PART1=OTHER_TABLE.COLUMN
AND REF_TABLE.KEY_COLUMN_PART2=1;
`ref_or_null'
This join type is like `ref', but with the addition that
MySQL will do an extra search for rows that contain `NULL'
values. This join type optimization is new for MySQL 4.1.1
and is mostly used when resolving subqueries.
In the following examples, MySQL can use a `ref_or_null' join
to process REF_TABLE:
SELECT * FROM REF_TABLE
WHERE KEY_COLUMN=EXPR OR KEY_COLUMN IS NULL;
`IS NULL' optimization IS NULL optimization.
`index_merge'
This join type indicates that the Index Merge optimization is
used. In this case, the `key' column 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
Index Merge optimization.
`unique_subquery'
This type replaces `ref' for some `IN' subqueries of the
following form:
VALUE IN (SELECT PRIMARY_KEY FROM SINGLE_TABLE WHERE SOME_EXPR)
`unique_subquery' is just an index lookup function that
replaces the subquery completely for better efficiency.
`index_subquery'
This join type is similar to `unique_subquery'. It replaces
`IN' subqueries, but it works for non-unique indexes in
subqueries of the following form:
VALUE IN (SELECT KEY_COLUMN FROM SINGLE_TABLE WHERE SOME_EXPR)
`range'
Only rows that are in a given range will be retrieved, using
an index to select the rows. The `key' column indicates
which index is used. The `key_len' contains the longest key
part that was used. The `ref' column will be `NULL' for this
type.
`range' can be used for when a key column is compared to a
constant using any of the `=', `<>', `>', `>=', `<', `<=',
`IS NULL', `<=>', `BETWEEN', or `IN' operators:
SELECT * FROM TBL_NAME
WHERE KEY_COLUMN = 10;
SELECT * FROM TBL_NAME
WHERE KEY_COLUMN BETWEEN 10 and 20;
SELECT * FROM TBL_NAME
WHERE KEY_COLUMN IN (10,20,30);
SELECT * FROM TBL_NAME
WHERE KEY_PART1= 10 AND KEY_PART2 IN (10,20,30);
`index'
This join type is the same as `ALL', except that only the
index tree is scanned. This usually is faster than `ALL',
because 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.
`ALL'
A full table scan will be 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 allow row retrieval from the table
based on constant values or column values from earlier tables.
`possible_keys'
The `possible_keys' column indicates which indexes MySQL could 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 see 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. `ALTER TABLE' ALTER TABLE.
To see what indexes a table has, use `SHOW INDEX FROM TBL_NAME'.
`key'
The `key' column indicates the key (index) that MySQL actually
decided to use. The key is `NULL' if no index was chosen. To force
MySQL to use or ignore an index listed in the `possible_keys'
column, use `FORCE INDEX', `USE INDEX', or `IGNORE INDEX' in your
query. `SELECT' SELECT.
For `MyISAM' and `BDB' tables, running `ANALYZE TABLE' will help
the optimizer choose better indexes. For `MyISAM' tables,
`myisamchk --analyze' will do the same. See `ANALYZE
TABLE' ANALYZE TABLE. and Table maintenance.
`key_len'
The `key_len' column indicates the length of the key that MySQL
decided to use. The length is `NULL' if the `key' column says
`NULL'. Note that the value of `key_len' allows you to determine
how many parts of a multiple-part key MySQL will actually use.
`ref'
The `ref' column shows which columns or constants are used with the
`key' to select rows from the table.
`rows'
The `rows' column indicates the number of rows MySQL believes it
must examine to execute the query.
`Extra'
This column contains additional information about how MySQL will
resolve the query. Here is an explanation of the different text
strings that can appear in this column:
`Distinct'
MySQL will stop searching for more rows for the current row
combination after it has found the first matching row.
`Not exists'
MySQL was able to do a `LEFT JOIN' optimization on the query
and will not examine more rows in this table for the previous
row combination after it finds one row that matches the `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;
Assume that `t2.id' is defined as `NOT NULL'. In this case,
MySQL will scan `t1' and look 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 will 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 `t2', regardless of how many rows
actually match in `t2'.
``range checked for each record (index map: #)''
MySQL found no good index to use, but found that some of
indexes might be used once column values from preceding
tables are known. For each row combination in the preceding
tables, MySQL will check whether it is possible to use a
`range' or `index_merge' access method to retrieve rows. The
applicability criteria are as described in Range
optimization and Index Merge optimization, with the
exception that all column values for the preceding table are
known and considered to be constants.
This is not very fast, but is faster than performing a join
with no index at all.
`Using filesort'
MySQL will need to 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. `ORDER BY' optimization
ORDER BY optimization.
`Using index'
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 temporary'
To resolve the query, MySQL will need to create a temporary
table to hold the result. This typically happens if the
query contains `GROUP BY' and `ORDER BY' clauses that list
columns differently.
`Using where'
A `WHERE' clause will be 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 not `Using where' and the table join type is
`ALL' or `index'.
If you want to make your queries as fast as possible, you
should look out for `Extra' values of `Using filesort' and
`Using temporary'.
`Using sort_union(...)'
`Using union(...)'
`Using intersect(...)'
These indicate how index scans are merged for the
`index_merge' join type. See Index Merge
optimization for more information.
`Using index for group-by'
Similar to the `Using index' way of accessing a 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 will be used in the most
efficient way so that for each group, only a few index
entries will be read. For details, see `GROUP BY'
optimization GROUP BY optimization.
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 product also is used to determine which
multiple-table `SELECT' statements to execute. Server
parameters.
The following example shows how a multiple-table join can be optimized
progressively based on the information provided by `EXPLAIN'.
Suppose that you have the `SELECT' statement shown here and you plan to
examine it using `EXPLAIN':
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:
*Table* *Column* *Column Type*
`tt' `ActualPC' `CHAR(10)'
`tt' `AssignedPC' `CHAR(10)'
`tt' `ClientID' `CHAR(10)'
`et' `EMPLOYID' `CHAR(15)'
`do' `CUSTNMBR' `CHAR(15)'
* The tables have the following indexes:
*Table* *Index*
`tt' `ActualPC'
`tt' `AssignedPC'
`tt' `ClientID'
`et' `EMPLOYID' (primary key)
`do' `CUSTNMBR' (primary key)
* The `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 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 (key map: 35)
Because `type' is `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 will take 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 the same. (For `ISAM' tables, indexes
may not be used at all unless the columns are declared the same.) In
this context, `VARCHAR' and `CHAR' are the same unless they are
declared as different lengths. Because `tt.ActualPC' is declared as
`CHAR(10)' and `et.EMPLOYID' is declared as `CHAR(15)', there is a
length mismatch.
To fix this disparity between column lengths, use `ALTER TABLE' to
lengthen `ActualPC' from 10 characters to 15 characters:
mysql> ALTER TABLE tt MODIFY ActualPC VARCHAR(15);
`tt.ActualPC' and `et.EMPLOYID' are both `VARCHAR(15)'. Executing the
`EXPLAIN' statement again produces this result:
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 (key map: 1)
et_1 ALL PRIMARY NULL NULL NULL 74
range checked for each record (key map: 1)
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 is executed in a couple
of seconds.
A second alteration can be made to eliminate the column length
mismatches for the `tt.AssignedPC = et_1.EMPLOYID' and `tt.ClientID =
do.CUSTNMBR' comparisons:
mysql> ALTER TABLE tt MODIFY AssignedPC VARCHAR(15),
-> MODIFY ClientID VARCHAR(15);
`EXPLAIN' 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
This is almost as good as it can get.
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:
mysql> ANALYZE TABLE tt;
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. If not, you may get better
performance by using `STRAIGHT_JOIN' in your `SELECT' statement and
trying to list the tables in a different order in the `FROM' clause.
Info Catalog
(mysql.info.gz) Query Speed
(mysql.info.gz) Query Speed
(mysql.info.gz) Estimating performance
automatically generated byinfo2html