<div id="docs-body">
 <div class="section">
  <div class="titlepage">
   <div>
    <div>
     <h3 class="title">
      <a name="controlling-query-plan-evaluation">
      </a>
      10.9.1 Controlling Query Plan Evaluation
     </h3>
    </div>
   </div>
  </div>
  <a class="indexterm" name="idm46045224538752">
  </a>
  <p>
   The task of the query optimizer is to find an optimal plan for
        executing an SQL query. Because the difference in performance
        between
   <span class="quote">
    “
    <span class="quote">
     good
    </span>
    ”
   </span>
   and
   <span class="quote">
    “
    <span class="quote">
     bad
    </span>
    ”
   </span>
   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.
  </p>
  <p>
   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.
  </p>
  <p>
   The behavior of the optimizer with respect to the number of
        plans it evaluates can be controlled using two system variables:
  </p>
  <div class="itemizedlist">
   <ul class="itemizedlist" style="list-style-type: disc; ">
    <li class="listitem">
     <p>
      The
      <a class="link" href="server-system-variables.html#sysvar_optimizer_prune_level">
       <code class="literal">
        optimizer_prune_level
       </code>
      </a>
      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
      <span class="quote">
       “
       <span class="quote">
        educated
            guess
       </span>
       ”
      </span>
      rarely misses optimal plans, and may
            dramatically reduce query compilation times. That is why
            this option is on
            (
      <code class="literal">
       optimizer_prune_level=1
      </code>
      ) by default.
            However, if you believe that the optimizer missed a better
            query plan, this option can be switched off
            (
      <code class="literal">
       optimizer_prune_level=0
      </code>
      ) 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.
     </p>
    </li>
    <li class="listitem">
     <p>
      The
      <a class="link" href="server-system-variables.html#sysvar_optimizer_search_depth">
       <code class="literal">
        optimizer_search_depth
       </code>
      </a>
      variable tells how far into the
      <span class="quote">
       “
       <span class="quote">
        future
       </span>
       ”
      </span>
      of
            each incomplete plan the optimizer should look to evaluate
            whether it should be expanded further. Smaller values of
      <a class="link" href="server-system-variables.html#sysvar_optimizer_search_depth">
       <code class="literal">
        optimizer_search_depth
       </code>
      </a>
      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
      <a class="link" href="server-system-variables.html#sysvar_optimizer_search_depth">
       <code class="literal">
        optimizer_search_depth
       </code>
      </a>
      is
            close to the number of tables in the query. At the same
            time, if compiled with
      <a class="link" href="server-system-variables.html#sysvar_optimizer_search_depth">
       <code class="literal">
        optimizer_search_depth
       </code>
      </a>
      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
      <a class="link" href="server-system-variables.html#sysvar_optimizer_search_depth">
       <code class="literal">
        optimizer_search_depth
       </code>
      </a>
      ,
            this variable can be set to 0 to tell the optimizer to
            determine the value automatically.
     </p>
    </li>
   </ul>
  </div>
 </div>
 <br/>
</div>
