Using EXPLAIN - High Performance MySQL - page 720

broad here: it can mean a subquery, a UNION result, and so on. You'll see later why this
is so.
There are two important variations on EXPLAIN :
• EXPLAIN EXTENDED appears to behave just like a normal EXPLAIN , but it tells the server
to “reverse compile” the execution plan into a SELECT statement. You can see this
generated statement by running SHOW WARNINGS immediately afterward. The state-
ment comes directly from the execution plan, not from the original SQL statement,
which by this point has been reduced to a data structure. It will not be the same as
the original statement in most cases. You can examine it to see exactly how the
query optimizer has transformed the statement. EXPLAIN EXTENDED is available in
MySQL 5.0 and newer, and it adds an extra filtered column in MySQL 5.1 (more
on that later).
• EXPLAIN PARTITIONS shows the partitions the query will access, if applicable. It is
available only in MySQL 5.1 and newer.
It's a common mistake to think that MySQL doesn't execute a query when you add
EXPLAIN to it. In fact, if the query contains a subquery in the FROM clause, MySQL actually
executes the subquery, places its results into a temporary table, and then finishes op-
timizing the outer query. It has to process all such subqueries before it can optimize
the outer query fully, which it must do for EXPLAIN . 1 This means EXPLAIN can actually
cause a great deal of work for the server if the statement contains expensive subqueries
or views that use the TEMPTABLE algorithm.
Bear in mind that EXPLAIN is an approximation, nothing more. Sometimes it's a good
approximation, but at other times, it can be very far from the truth. Here are some of
its limitations:
• EXPLAIN doesn't tell you anything about how triggers, stored functions, or UDFs
will affect your query.
• It doesn't work for stored procedures, although you can extract the queries man-
ually and EXPLAIN them individually.
• It doesn't tell you about ad hoc optimizations MySQL does during query execution.
• Some of the statistics it shows are estimates and can be very inaccurate.
• It doesn't show you everything there is to know about a query's execution plan.
(The MySQL developers are adding more information when possible.)
• It doesn't distinguish between some things with the same name. For example, it
uses “filesort” for in-memory sorts and for temporary files, and it displays “Using
temporary” for temporary tables on disk and in memory.
1. This limitation will be lifted in MySQL 5.6.