Databases Reference
In-Depth Information
Figure 6-2. Swim-lane diagram illustrating retrieving rows using a join
MySQL executes every kind of query in essentially the same way. For example, it han-
dles a subquery in the
FROM
clause by executing it first, putting the results into a tem-
porary table,
11
and then treating that table just like an ordinary table (hence the name
“derived table”). MySQL executes
UNION
queries with temporary tables too, and it
rewrites all
RIGHT OUTER JOIN
queries to equivalent
LEFT OUTER JOINs
. In short, current
versions of MySQL coerce every kind of query into this execution plan.
12
It's not possible to execute every legal SQL query this way, however. For example, a
FULL OUTER JOIN
can't be executed with nested loops and backtracking as soon as a
table with no matching rows is found, because it might begin with a table that has no
matching rows. This explains why MySQL doesn't support
FULL OUTER JOIN
. Still other
queries can be executed with nested loops, but perform very badly as a result. We'll
look at some of those later.
The execution plan
MySQL doesn't generate byte-code to execute a query, as many other database prod-
ucts do. Instead, the query execution plan is actually a tree of instructions that the query
execution engine follows to produce the query results. The final plan contains enough
information to reconstruct the original query. If you execute
EXPLAIN EXTENDED
on a
query, followed by
SHOW WARNINGS
, you'll see the reconstructed query.
13
11. There are no indexes on the temporary table, which is something you should keep in mind when writing
complex joins against subqueries in the
FROM
clause. This applies to
UNION
queries, too.
12. There are significant changes in MySQL 5.6 and in MariaDB, which introduce more sophisticated
execution paths.
13. The server generates the output from the execution plan. It thus has the same semantics as the original
query, but not necessarily the same text.
