Databases Reference
In-Depth Information
the
FROM
clause), the temporary table that results from this query is shown as the last
row in the results, not the first.
So far this is all very straightforward, but mixtures of these three categories of state-
ments can cause the output to become more complicated, as we'll see a bit later.
The select_type Column
This column shows whether the row is a simple or complex
SELECT
(and if it's the latter,
which of the three complex types it is). The value
SIMPLE
means the query contains no
subqueries or
UNION
s. If the query has any such complex subparts, the outermost part
is labeled
PRIMARY
, and other parts are labeled as follows:
SUBQUERY
A
SELECT
that is contained in a subquery in the
SELECT
list (in other words, not in
the
FROM
clause) is labeled as
SUBQUERY
.
DERIVED
The value
DERIVED
is used for a
SELECT
that is contained in a subquery in the
FROM
clause, which MySQL executes recursively and places into a temporary table. The
server refers to this as a “derived table” internally, because the temporary table is
derived from the subquery.
UNION
The second and subsequent
SELECT
s in a
UNION
are labeled as
UNION
. The first
SELECT
is labeled as though it is executed as part of the outer query. This is why the
previous example showed the first
SELECT
in the
UNION
as
PRIMARY
. If the
UNION
were
contained in a subquery in the
FROM
clause, its first
SELECT
would be labeled as
DERIVED
.
UNION RESULT
The
SELECT
used to retrieve results from the
UNION
's anonymous temporary table is
labeled as
UNION RESULT
.
In addition to these values, a
SUBQUERY
and a
UNION
can be labeled as
DEPENDENT
and
UNCACHEABLE
.
DEPENDENT
means the
SELECT
depends on data that is found in an outer
query;
UNCACHEABLE
means something in the
SELECT
prevents the results from being
cached with an
Item_cache
. (
Item_cache
is undocumented; it is not the same thing as
the query cache, although it can be defeated by some of the same types of constructs,
such as the
RAND()
function.)
The table Column
This column shows which table the row is accessing. In most cases, it's straightforward:
it's the table, or its alias if the SQL specifies one.
