Databases Reference
In-Depth Information
• MySQL doesn't consider other queries that are running concurrently, which can
affect how quickly the query runs.
• MySQL doesn't always do cost-based optimization. Sometimes it just follows the
rules, such as “if there's a full-text
MATCH()
clause, use a
FULLTEXT
index if one
exists.” It will do this even when it would be faster to use a different index and a
non
-FULLTEXT
query with a
WHERE
clause.
• The optimizer doesn't take into account the cost of operations not under its con-
trol, such as executing stored functions or user-defined functions.
• As we'll see later, the optimizer can't always estimate every possible execution plan,
so it might miss an optimal plan.
MySQL's query optimizer is a highly complex piece of software, and it uses many op-
timizations to transform the query into an execution plan. There are two basic types
of optimizations, which we call
static
and
dynamic. Static optimizations
can be per-
formed simply by inspecting the parse tree. For example, the optimizer can transform
the
WHERE
clause into an equivalent form by applying algebraic rules. Static optimiza-
tions are independent of values, such as the value of a constant in a
WHERE
clause. They
can be performed once and will always be valid, even when the query is reexecuted
with different values. You can think of these as “compile-time optimizations.”
In contrast,
dynamic optimizations
are based on context and can depend on many fac-
tors, such as which value is in a
WHERE
clause or how many rows are in an index. They
must be reevaluated each time the query is executed. You can think of these as “runtime
optimizations.”
The difference is important when executing prepared statements or stored procedures.
MySQL can do static optimizations once, but it must reevaluate dynamic optimizations
every time it executes a query. MySQL sometimes even reoptimizes the query as it
executes it.
8
Here are some types of optimizations MySQL knows how to do:
Reordering joins
Tables don't always have to be joined in the order you specify in the query. De-
termining the best join order is an important optimization; we explain it in depth
later in this chapter.
Converting
OUTER JOIN
s to
INNER JOIN
s
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.
8. For example, the range check query plan reevaluates indexes for each row in a
JOIN
. You can see this
query plan by looking for “range checked for each record” in the
Extra
column in
EXPLAIN
. This query
plan also increments the
Select_full_range_join
server variable.
