<div id="docs-body">
 <div class="section">
  <div class="titlepage">
   <div>
    <div>
     <h3 class="title">
      <a name="optimizer-issues">
      </a>
      B.3.5 Optimizer-Related Issues
     </h3>
    </div>
   </div>
  </div>
  <p>
   MySQL uses a cost-based optimizer to determine the best way to
        resolve a query. In many cases, MySQL can calculate the best
        possible query plan, but sometimes MySQL does not have enough
        information about the data at hand and has to make
   <span class="quote">
    “
    <span class="quote">
     educated
    </span>
    ”
   </span>
   guesses about the data.
  </p>
  <p>
   For the cases when MySQL does not do the "right" thing, tools
        that you have available to help MySQL are:
  </p>
  <div class="itemizedlist">
   <ul class="itemizedlist" style="list-style-type: disc; ">
    <li class="listitem">
     <p>
      Use the
      <a class="link" href="explain.html" title="15.8.2 EXPLAIN Statement">
       <code class="literal">
        EXPLAIN
       </code>
      </a>
      statement to
            get information about how MySQL processes a query. To use
            it, just add the keyword
      <a class="link" href="explain.html" title="15.8.2 EXPLAIN Statement">
       <code class="literal">
        EXPLAIN
       </code>
      </a>
      to the front of your
      <a class="link" href="select.html" title="15.2.13 SELECT Statement">
       <code class="literal">
        SELECT
       </code>
      </a>
      statement:
     </p>
     <div class="copytoclipboard-wrapper">
      <pre class="programlisting one-line language-sql"><div class="docs-select-all right" id="sa46744851"><div class="copy-help left">Press ⌘+C to copy</div> <div class="right"><button class="clipboard-btn" title="Copy to Clipboard"><span class="icon-clipboard"></span></button></div></div><code class="language-sql"><span class="token prompt">mysql&gt;</span> <span class="token keyword">EXPLAIN</span> <span class="token keyword">SELECT</span> <span class="token operator">*</span> <span class="token keyword">FROM</span> t1<span class="token punctuation">,</span> t2 <span class="token keyword">WHERE</span> t1<span class="token punctuation">.</span>i <span class="token operator">=</span> t2<span class="token punctuation">.</span>i<span class="token punctuation">;</span></code></pre>
     </div>
     <p>
      <a class="link" href="explain.html" title="15.8.2 EXPLAIN Statement">
       <code class="literal">
        EXPLAIN
       </code>
      </a>
      is discussed in more
            detail in
      <a class="xref" href="explain.html" title="15.8.2 EXPLAIN Statement">
       Section 15.8.2, “EXPLAIN Statement”
      </a>
      .
     </p>
    </li>
    <li class="listitem">
     <p>
      Use
      <code class="literal">
       ANALYZE TABLE
       <em class="replaceable">
        <code>
         tbl_name
        </code>
       </em>
      </code>
      to update the
            key distributions for the scanned table. See
      <a class="xref" href="analyze-table.html" title="15.7.3.1 ANALYZE TABLE Statement">
       Section 15.7.3.1, “ANALYZE TABLE Statement”
      </a>
      .
     </p>
    </li>
    <li class="listitem">
     <p>
      <a class="indexterm" name="idm46045053391168">
      </a>
      Use
      <code class="literal">
       FORCE INDEX
      </code>
      for the scanned table to
            tell MySQL that table scans are very expensive compared to
            using the given index:
     </p>
     <div class="copytoclipboard-wrapper">
      <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa75674840"><div class="copy-help left">Press ⌘+C to copy</div> <div class="right"><button class="clipboard-btn" title="Copy to Clipboard"><span class="icon-clipboard"></span></button></div></div><code class="language-sql"><span class="token keyword">SELECT</span> <span class="token operator">*</span> <span class="token keyword">FROM</span> t1<span class="token punctuation">,</span> t2 <span class="token keyword">FORCE</span> <span class="token keyword">INDEX</span> <span class="token punctuation">(</span>index_for_column<span class="token punctuation">)</span>
<span class="token keyword">WHERE</span> t1<span class="token punctuation">.</span>col_name<span class="token operator">=</span>t2<span class="token punctuation">.</span>col_name<span class="token punctuation">;</span></code></pre>
     </div>
     <p>
      <code class="literal">
       USE INDEX
      </code>
      and
      <code class="literal">
       IGNORE
            INDEX
      </code>
      may also be useful. See
      <a class="xref" href="index-hints.html" title="10.9.4 Index Hints">
       Section 10.9.4, “Index Hints”
      </a>
      .
     </p>
    </li>
    <li class="listitem">
     <p>
      Global and table-level
      <code class="literal">
       STRAIGHT_JOIN
      </code>
      . See
      <a class="xref" href="select.html" title="15.2.13 SELECT Statement">
       Section 15.2.13, “SELECT Statement”
      </a>
      .
     </p>
    </li>
    <li class="listitem">
     <p>
      You can tune global or thread-specific system variables. For
            example, start
      <a class="link" href="mysqld.html" title="6.3.1 mysqld — The MySQL Server">
       <span class="command">
        <strong>
         mysqld
        </strong>
       </span>
      </a>
      with the
      <a class="link" href="server-system-variables.html#sysvar_max_seeks_for_key">
       <code class="option">
        --max-seeks-for-key=1000
       </code>
      </a>
      option or use
      <code class="literal">
       SET max_seeks_for_key=1000
      </code>
      to tell the optimizer to assume that no key scan causes more
            than 1,000 key seeks. See
      <a class="xref" href="server-system-variables.html" title="7.1.8 Server System Variables">
       Section 7.1.8, “Server System Variables”
      </a>
      .
     </p>
    </li>
   </ul>
  </div>
 </div>
 <br/>
</div>
