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.
MySQL 5.0.1 introduces a more flexible method for query optimization that 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, then this option can be switched off
(optimizer_prune_level
=0) with the risk
that query compilation may take much longer. Notice 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 in order 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.
Ésta es una traducción del manual de referencia de MySQL, que puede encontrarse en dev.mysql.com. El manual de referencia original de MySQL está escrito en inglés, y esta traducción no necesariamente está tan actualizada como la versión original. Para cualquier sugerencia sobre la traducción y para señalar errores de cualquier tipo, no dude en dirigirse a mysql-es@vespito.com.