Databases Reference
In-Depth Information
how many rows it will find. Because the query optimizer has a known quantity (the
value in the
WHERE
clause) to use in the lookup, this table's
ref
type is
const
.
In the second step, MySQL treats the
film_id
column from the row found in the
first step as a known quantity. It can do this because the optimizer knows that by
the time the query reaches the second step, it will know all the values from the first
step. Notice that the
film_actor
table's
ref
type is
const
, just as the
film
table's was.
Another way you'll see constant conditions applied is by propagating a value's
constant-ness from one place to another if there is a
WHERE
,
USING
, or
ON
clause that
restricts the values to being equal. In this example, the optimizer knows that the
USING
clause forces
film_id
to have the same value everywhere in the query—it
must be equal to the constant value given in the
WHERE
clause.
Covering indexes
MySQL can sometimes use an index to avoid reading row data, when the index
contains all the columns the query needs. We discussed covering indexes at length
in the previous chapter.
Subquery optimization
MySQL can convert some types of subqueries into more efficient alternative forms,
reducing them to index lookups instead of separate queries.
Early termination
MySQL can stop processing a query (or a step in a query) as soon as it fulfills the
query or step. The obvious case is a
LIMIT
clause, but there are several other kinds
of early termination. For instance, if MySQL detects an impossible condition, it
can abort the entire query. You can see this in the following example:
mysql>
EXPLAIN SELECT film.film_id FROM sakila.film WHERE film_id = −1;
+----+...+-----------------------------------------------------+
| id |...| Extra |
+----+...+-----------------------------------------------------+
| 1 |...| Impossible WHERE noticed after reading const tables |
+----+...+-----------------------------------------------------+
This query stopped during the optimization step, but MySQL can also terminate
execution early in some other cases. The server can use this optimization when the
query execution engine recognizes the need to retrieve distinct values, or to stop
when a value doesn't exist. For example, the following query finds all movies
without any actors:
9
mysql>
SELECT film.film_id
->
FROM sakila.film
->
LEFT OUTER JOIN sakila.film_actor USING(film_id)
->
WHERE film_actor.film_id IS NULL;
9. We agree, a movie without actors is strange, but the Sakila sample database lists no actors for
SLACKER
LIAISONS
, which it describes as “A Fast-Paced Tale of a Shark And a Student who must Meet a Crocodile
in Ancient China.”
