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 to 10) this is not a problem. However, when larger 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 enables 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 using two system variables:
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.
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
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
close to the number of tables in the query. At the same
time, if compiled with
equal to 3 or 4, the optimizer may compile in less than a
minute for the same query. If you are unsure of what a
reasonable value is for
this variable can be set to 0 to tell the optimizer to
determine the value automatically.