Databases Reference
In-Depth Information
outer query in this case is the
SELECT
that begins in line 2 and retrieves data from
der_1
.
• The fourth row is listed as a
UNION
, which means it is the second or later
SELECT
in
a
UNION
. Its table is
<derived6>
, which means it's retrieving data from a subquery
in the
FROM
clause and appending to a temporary table for the
UNION
. As before, to
find the
EXPLAIN
rows that show the query plan for this subquery, you must look
forward.
• The fifth row is the
der_2
subquery defined in lines 13, 14, and 15 in the original
SQL, which
EXPLAIN
refers to as
<derived6>
.
• The sixth row is an ordinary subquery in
<derived6>
's
SELECT
list. Its
id
is
7
, which
is important...
• ...because it is greater than
5
, which is the seventh row's
id
. Why is this important?
Because it shows the boundaries of the
<derived6>
subquery. When
EXPLAIN
out-
puts a row whose
SELECT
type is
DERIVED
, it represents the beginning of a “nested
scope.” If a subsequent row's
id
is smaller (in this case,
5
is smaller than
6
), it
means the nested scope has closed. This lets us know that the seventh row is part
of the
SELECT
list that is retrieving data from
<derived6>
—i.e., part of the fourth
row's
SELECT
list (line 11 in the original SQL). This example is fairly easy to un-
derstand without knowing the significance and rules of nested scopes, but some-
times it's not so easy. The other notable thing about this row in the output is that
it is listed as an
UNCACHEABLE SUBQUERY
because of the user variable.
• Finally, the last row is the
UNION RESULT
. It represents the stage of reading the rows
from the
UNION
's temporary table. You can begin at this row and work backward
if you wish; it is returning results from rows whose
id
s are
1
and
4
, which are in
turn references to
<derived3>
and
<derived6>
.
As you can see, the combination of these complicated
SELECT
types can result in
EXPLAIN
output that's pretty difficult to read. Understanding the rules makes it easier,
but there's no substitute for practice.
Reading
EXPLAIN
's output often requires you to jump forward and backward in the list.
For example, look again at the first row in the output. There is no way to know just by
looking at it that it is part of a
UNION
. You'll only see that when you read the last row
of the output.
The type Column
The MySQL manual says this column shows the “join type,” but we think it's more
accurate to say the
access type
—in other words, how MySQL has decided to find rows
in the table. Here are the most important access methods, from worst to best:
