When you precede a statement with the keyword
EXPLAIN, MySQL displays information from the optimizer about the query
execution plan. That is, MySQL explains how it would process the statement, including information about
how tables are joined and in which order. See Section 8.8.2,
EXPLAIN Output Format".
EXPLAIN PARTITIONS is useful only when examining queries involving
partitioned tables. For details, see Section
17.3.5, "Obtaining Information About Partitions".
EXPLAIN is synonymous
SHOW COLUMNS FROM . For
SHOW COLUMNS, see Section 13.8.1,
DESCRIBE Syntax", and Section
SHOW COLUMNS Syntax".
With the help of
EXPLAIN, you can see where you should add indexes to tables so that the statement
executes faster by using 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
SELECT statement, begin the statement with
STRAIGHT_JOIN rather than just
SELECT. (See Section 13.2.9, "
The optimizer trace may sometimes provide information complementary to that of
EXPLAIN. However, the optimizer trace format and content are subject to change
between versions". For details, see
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 220.127.116.11, "
ANALYZE TABLE Syntax".