The EXPLAIN statement can be used
either as a way to obtain information about how MySQL executes a
SELECT statement or as a synonym
for DESCRIBE:
When you precede a
SELECTstatement with the keywordEXPLAIN, MySQL displays information from the optimizer about the query execution plan. That is, MySQL explains how it would process theSELECT, including information about how tables are joined and in which order.EXPLAIN EXTENDEDcan be used to provide additional information.For information on how to use
EXPLAINandEXPLAIN EXTENDEDto obtain query execution plan information, see Section 7.8.1, “Optimizing Queries withEXPLAIN”.EXPLAIN PARTITIONSis useful only when examining queries involving partitioned tables. For details, see Section 18.3.4, “Obtaining Information About Partitions”.EXPLAINis synonymous withtbl_nameDESCRIBEortbl_nameSHOW COLUMNS FROM.tbl_nameFor a description of the
DESCRIBEandSHOW COLUMNSstatements, see Section 12.8.1, “DESCRIBESyntax”, and Section 12.4.5.6, “SHOW COLUMNSSyntax”.
With the help of EXPLAIN, you can
see where you should add indexes to tables to get a faster
SELECT that uses indexes to find
rows. You can also use EXPLAIN to
check whether the optimizer joins the tables in an optimal
order. To give a hint to 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. (See
Section 12.2.9, “SELECT Syntax”.)
If you have a problem with indexes not being used when you
believe that they should be, run ANALYZE
TABLE to update table statistics such as cardinality
of keys, that can affect the choices the optimizer makes. See
Section 12.4.2.1, “ANALYZE TABLE Syntax”.