<div id="docs-body">
 <div class="section">
  <div class="titlepage">
   <div>
    <div>
     <h4 class="title">
      <a name="table-scan-avoidance">
      </a>
      10.2.1.23 Avoiding Full Table Scans
     </h4>
    </div>
   </div>
  </div>
  <a class="indexterm" name="idm46045228443712">
  </a>
  <a class="indexterm" name="idm46045228442256">
  </a>
  <a class="indexterm" name="idm46045228440768">
  </a>
  <p>
   The output from
   <a class="link" href="explain.html" title="15.8.2 EXPLAIN Statement">
    <code class="literal">
     EXPLAIN
    </code>
   </a>
   shows
   <a class="link" href="explain-output.html#jointype_all">
    <code class="literal">
     ALL
    </code>
   </a>
   in the
   <code class="literal">
    type
   </code>
   column when MySQL uses a
   <a class="link" href="glossary.html#glos_full_table_scan" title="full table scan">
    full table scan
   </a>
   to
          resolve a query. This usually happens under the following
          conditions:
  </p>
  <div class="itemizedlist">
   <ul class="itemizedlist" style="list-style-type: disc; ">
    <li class="listitem">
     <p>
      The table is so small that it is faster to perform a table
              scan than to bother with a key lookup. This is common for
              tables with fewer than 10 rows and a short row length.
     </p>
    </li>
    <li class="listitem">
     <p>
      There are no usable restrictions in the
      <code class="literal">
       ON
      </code>
      or
      <code class="literal">
       WHERE
      </code>
      clause
              for indexed columns.
     </p>
    </li>
    <li class="listitem">
     <p>
      You are comparing indexed columns with constant values and
              MySQL has calculated (based on the index tree) that the
              constants cover too large a part of the table and that a
              table scan would be faster. See
      <a class="xref" href="where-optimization.html" title="10.2.1.1 WHERE Clause Optimization">
       Section 10.2.1.1, “WHERE Clause Optimization”
      </a>
      .
     </p>
    </li>
    <li class="listitem">
     <p>
      You are using a key with low cardinality (many rows match
              the key value) through another column. In this case, MySQL
              assumes that by using the key probably requires many key
              lookups and that a table scan would be faster.
     </p>
    </li>
   </ul>
  </div>
  <p>
   For small tables, a table scan often is appropriate and the
          performance impact is negligible. For large tables, try the
          following techniques to avoid having the optimizer incorrectly
          choose a table scan:
  </p>
  <div class="itemizedlist">
   <ul class="itemizedlist" style="list-style-type: disc; ">
    <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>
      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="sa94466196"><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><em class="replaceable">index_for_column</em><span class="token punctuation">)</span>
  <span class="token keyword">WHERE</span> t1<span class="token punctuation">.</span><em class="replaceable">col_name</em><span class="token operator">=</span>t2<span class="token punctuation">.</span><em class="replaceable">col_name</em><span class="token punctuation">;</span></code></pre>
     </div>
     <p>
      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>
      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>
