7.5.3. Controlling Query Optimizer Performance
The task of the query optimizer is to find an optimal plan for
executing an SQL query. Because the difference in performance
between “good” and “bad” plans can be
orders of magnitude (that is, seconds versus hours or even
days), most query optimizers, including that of MySQL, perform a
more or less exhaustive search for an optimal plan among all
possible query evaluation plans. For join queries, the number of
possible plans investigated by the MySQL optimizer grows
exponentially with the number of tables referenced in a query.
For small numbers of tables (typically less than 7–10)
this is not a problem. However, when bigger queries are
submitted, the time spent in query optimization may easily
become the major bottleneck in the server's performance.
A more flexible method for query optimization allows the user to
control how exhaustive the optimizer is in its search for an
optimal query evaluation plan. The general idea is that the
fewer plans that are investigated by the optimizer, the less
time it spends in compiling a query. On the other hand, because
the optimizer skips some plans, it may miss finding an optimal
plan.
The behavior of the optimizer with respect to the number of
plans it evaluates can be controlled via two system variables:
The optimizer_prune_level variable tells
the optimizer to skip certain plans based on estimates of
the number of rows accessed for each table. Our experience
shows that this kind of “educated guess” rarely
misses optimal plans, and may dramatically reduce query
compilation times. That is why this option is on
(optimizer_prune_level=1) by default.
However, if you believe that the optimizer missed a better
query plan, this option can be switched off
(optimizer_prune_level=0) with the risk
that query compilation may take much longer. Note that, even
with the use of this heuristic, the optimizer still explores
a roughly exponential number of plans.
The optimizer_search_depth variable tells
how far into the “future” of each incomplete
plan the optimizer should look to evaluate whether it should
be expanded further. Smaller values of
optimizer_search_depth may result in
orders of magnitude smaller query compilation times. For
example, queries with 12, 13, or more tables may easily
require hours and even days to compile if
optimizer_search_depth is close to the
number of tables in the query. At the same time, if compiled
with optimizer_search_depth equal to 3 or
4, the compiler may compile in less than a minute for the
same query. If you are unsure of what a reasonable value is
for optimizer_search_depth, this variable
can be set to 0 to tell the optimizer to determine the value
automatically.
