Reordering joins
Tables don’t always have to be joined in the order you specify in the query.
Determining the best join order is an important optimization; we explain it in
depth in “The join optimizer” on page 173.
Converting OUTER JOINs to INNER JOINs
An OUTER JOIN doesn’t necessarily have to be executed as an OUTER JOIN. Some
factors, such as the WHERE clause and table schema, can actually cause an OUTER
JOIN to be equivalent to an INNER JOIN. MySQL can recognize this and rewrite the
join, which makes it eligible for reordering.
Applying algebraic equivalence rules
MySQL applies algebraic transformations to simplify and canonicalize expres-
sions. It can also fold and reduce constants, eliminating impossible constraints
and constant conditions. For example, the term (5=5 AND a>5) will reduce to just
a>5. Similarly, (a5 AND b=c AND a=5. These rules are
very useful for writing conditional queries, which we discuss later in the chapter.
COUNT( ), MIN( ), and MAX( ) optimizations
Indexes and column nullability can often help MySQL optimize away these
expressions. For example, to find the minimum value of a column that’s left-
most in a B-Tree index, MySQL can just request the first row in the index. It can
even do this in the query optimization stage, and treat the value as a constant for
the rest of the query. Similarly, to find the maximum value in a B-Tree index, the
server reads the last row. If the server uses this optimization, you’ll see “Select
tables optimized away” in the EXPLAIN plan. This literally means the optimizer
has removed the table from the query plan and replaced it with a constant.
Likewise, COUNT(*) queries without a WHERE clause can often be optimized away
on some storage engines (such as MyISAM, which keeps an exact count of rows
in the table at all times). See “Optimizing COUNT( ) Queries” on page 188, later
in this chapter, for details.
Evaluating and reducing constant expressions
When MySQL detects that an expression can be reduced to a constant, it will do
so during optimization. For example, a user-defined variable can be converted to
a constant if it’s not changed in the query. Arithmetic expressions are another
example.
Perhaps surprisingly, even something you might consider to be a query can be
reduced to a constant during the optimization phase. One example is a MIN( ) on
an index. This can even be extended to a constant lookup on a primary key or
unique index. If a WHERE clause applies a constant condition to such an index, the
optimizer knows MySQL can look up the value at the beginning of the query. It
will then treat the value as a constant in the rest of the query. Here’s an example:
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
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 condi-
tion, it can abort the entire query.
Equality propagation
MySQL recognizes when a query holds two columns as equal—for example, in a
JOIN condition—and propagates WHERE clauses across equivalent columns. For
instance, in the following query:
IN( ) list comparisons
In many database servers, IN( ) is just a synonym for multiple OR clauses, because
the two are logically equivalent. Not so in MySQL, which sorts the values in the
IN( ) list and uses a fast binary search to see whether a value is in the list. This is
O(log n) in the size of the list, whereas an equivalent series of OR clauses is O(n)
in the size of the list (i.e., much slower for large lists).
