<div id="docs-body">
 <div class="section">
  <div class="titlepage">
   <div>
    <div>
     <h4 class="title">
      <a name="where-optimization">
      </a>
      10.2.1.1 WHERE Clause Optimization
     </h4>
    </div>
   </div>
  </div>
  <a class="indexterm" name="idm46045230318512">
  </a>
  <a class="indexterm" name="idm46045230317472">
  </a>
  <p>
   This section discusses optimizations that can be made for
          processing
   <code class="literal">
    WHERE
   </code>
   clauses. The examples use
   <a class="link" href="select.html" title="15.2.13 SELECT Statement">
    <code class="literal">
     SELECT
    </code>
   </a>
   statements, but the same
          optimizations apply for
   <code class="literal">
    WHERE
   </code>
   clauses in
   <a class="link" href="delete.html" title="15.2.2 DELETE Statement">
    <code class="literal">
     DELETE
    </code>
   </a>
   and
   <a class="link" href="update.html" title="15.2.17 UPDATE Statement">
    <code class="literal">
     UPDATE
    </code>
   </a>
   statements.
  </p>
  <div class="note" style="margin-left: 0.5in; margin-right: 0.5in;">
   <div class="admon-title">
    Note
   </div>
   <p>
    Because work on the MySQL optimizer is ongoing, not all of
            the optimizations that MySQL performs are documented here.
   </p>
  </div>
  <p>
   You might be tempted to rewrite your queries to make
          arithmetic operations faster, while sacrificing readability.
          Because MySQL does similar optimizations automatically, you
          can often avoid this work, and leave the query in a more
          understandable and maintainable form. Some of the
          optimizations performed by MySQL follow:
  </p>
  <div class="itemizedlist">
   <ul class="itemizedlist" style="list-style-type: disc; ">
    <li class="listitem">
     <p>
      Removal of unnecessary parentheses:
     </p>
     <div class="copytoclipboard-wrapper">
      <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa26100429"><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 punctuation">(</span><span class="token punctuation">(</span>a <span class="token operator">AND</span> b<span class="token punctuation">)</span> <span class="token operator">AND</span> c <span class="token operator">OR</span> <span class="token punctuation">(</span><span class="token punctuation">(</span><span class="token punctuation">(</span>a <span class="token operator">AND</span> b<span class="token punctuation">)</span> <span class="token operator">AND</span> <span class="token punctuation">(</span>c <span class="token operator">AND</span> d<span class="token punctuation">)</span><span class="token punctuation">)</span><span class="token punctuation">)</span><span class="token punctuation">)</span>
<span class="token prompt">-&gt;</span> <span class="token punctuation">(</span>a <span class="token operator">AND</span> b <span class="token operator">AND</span> c<span class="token punctuation">)</span> <span class="token operator">OR</span> <span class="token punctuation">(</span>a <span class="token operator">AND</span> b <span class="token operator">AND</span> c <span class="token operator">AND</span> d<span class="token punctuation">)</span></code></pre>
     </div>
    </li>
    <li class="listitem">
     <p>
      Constant folding:
     </p>
     <div class="copytoclipboard-wrapper">
      <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa89623698"><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 punctuation">(</span>a<span class="token operator">&lt;</span>b <span class="token operator">AND</span> b<span class="token operator">=</span>c<span class="token punctuation">)</span> <span class="token operator">AND</span> a<span class="token operator">=</span><span class="token number">5</span>
<span class="token prompt">-&gt;</span> b<span class="token operator">&gt;</span><span class="token number">5</span> <span class="token operator">AND</span> b<span class="token operator">=</span>c <span class="token operator">AND</span> a<span class="token operator">=</span><span class="token number">5</span></code></pre>
     </div>
    </li>
    <li class="listitem">
     <p>
      Constant condition removal:
     </p>
     <div class="copytoclipboard-wrapper">
      <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa66815747"><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 punctuation">(</span>b<span class="token operator">&gt;=</span><span class="token number">5</span> <span class="token operator">AND</span> b<span class="token operator">=</span><span class="token number">5</span><span class="token punctuation">)</span> <span class="token operator">OR</span> <span class="token punctuation">(</span>b<span class="token operator">=</span><span class="token number">6</span> <span class="token operator">AND</span> <span class="token number">5</span><span class="token operator">=</span><span class="token number">5</span><span class="token punctuation">)</span> <span class="token operator">OR</span> <span class="token punctuation">(</span>b<span class="token operator">=</span><span class="token number">7</span> <span class="token operator">AND</span> <span class="token number">5</span><span class="token operator">=</span><span class="token number">6</span><span class="token punctuation">)</span>
<span class="token prompt">-&gt;</span> b<span class="token operator">=</span><span class="token number">5</span> <span class="token operator">OR</span> b<span class="token operator">=</span><span class="token number">6</span></code></pre>
     </div>
     <p>
      This takes place during preparation rather than during the
              optimization phase, which helps in simplification of
              joins. See
      <a class="xref" href="outer-join-optimization.html" title="10.2.1.9 Outer Join Optimization">
       Section 10.2.1.9, “Outer Join Optimization”
      </a>
      , for
              further information and examples.
     </p>
    </li>
    <li class="listitem">
     <p>
      Constant expressions used by indexes are evaluated only
              once.
     </p>
    </li>
    <li class="listitem">
     <p>
      Comparisons of columns of numeric types with constant
              values are checked and folded or removed for invalid or
              out-of-rage values:
     </p>
     <div class="copytoclipboard-wrapper">
      <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa37760594"><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 comment" spellcheck="true"># CREATE TABLE t (c TINYINT UNSIGNED NOT NULL);</span>
  <span class="token keyword">SELECT</span> <span class="token operator">*</span> <span class="token keyword">FROM</span> t <span class="token keyword">WHERE</span> c <span class="token operator">&lt;</span> <span class="token number">256</span><span class="token punctuation">;</span>
<span class="token operator">-</span>≫ <span class="token keyword">SELECT</span> <span class="token operator">*</span> <span class="token keyword">FROM</span> t <span class="token keyword">WHERE</span> <span class="token number">1</span><span class="token punctuation">;</span></code></pre>
     </div>
     <p>
      See
      <a class="xref" href="constant-folding-optimization.html" title="10.2.1.14 Constant-Folding Optimization">
       Section 10.2.1.14, “Constant-Folding Optimization”
      </a>
      , for
              more information.
     </p>
    </li>
    <li class="listitem">
     <p>
      <a class="link" href="aggregate-functions.html#function_count">
       <code class="literal">
        COUNT(*)
       </code>
      </a>
      on a single table
              without a
      <code class="literal">
       WHERE
      </code>
      is retrieved directly
              from the table information for
      <code class="literal">
       MyISAM
      </code>
      and
      <code class="literal">
       MEMORY
      </code>
      tables. This is also done
              for any
      <code class="literal">
       NOT NULL
      </code>
      expression when used
              with only one table.
     </p>
    </li>
    <li class="listitem">
     <p>
      Early detection of invalid constant expressions. MySQL
              quickly detects that some
      <a class="link" href="select.html" title="15.2.13 SELECT Statement">
       <code class="literal">
        SELECT
       </code>
      </a>
      statements are
              impossible and returns no rows.
     </p>
    </li>
    <li class="listitem">
     <p>
      <code class="literal">
       HAVING
      </code>
      is merged with
      <code class="literal">
       WHERE
      </code>
      if you do not use
      <code class="literal">
       GROUP
              BY
      </code>
      or aggregate functions
              (
      <a class="link" href="aggregate-functions.html#function_count">
       <code class="literal">
        COUNT()
       </code>
      </a>
      ,
      <a class="link" href="aggregate-functions.html#function_min">
       <code class="literal">
        MIN()
       </code>
      </a>
      , and so on).
     </p>
    </li>
    <li class="listitem">
     <p>
      For each table in a join, a simpler
      <code class="literal">
       WHERE
      </code>
      is constructed to get a fast
      <code class="literal">
       WHERE
      </code>
      evaluation for the table and also
              to skip rows as soon as possible.
     </p>
    </li>
    <li class="listitem">
     <p>
      <a class="indexterm" name="idm46045230281456">
      </a>
      <a class="indexterm" name="idm46045230280384">
      </a>
      All constant tables are read first before any other tables
              in the query. A constant table is any of the following:
     </p>
     <div class="itemizedlist">
      <ul class="itemizedlist" style="list-style-type: circle; ">
       <li class="listitem">
        <p>
         An empty table or a table with one row.
        </p>
       </li>
       <li class="listitem">
        <p>
         A table that is used with a
         <code class="literal">
          WHERE
         </code>
         clause on a
         <code class="literal">
          PRIMARY KEY
         </code>
         or a
         <code class="literal">
          UNIQUE
         </code>
         index, where all index parts
                  are compared to constant expressions and are defined
                  as
         <code class="literal">
          NOT NULL
         </code>
         .
        </p>
       </li>
      </ul>
     </div>
     <p>
      All of the following tables are used as constant tables:
     </p>
     <div class="copytoclipboard-wrapper">
      <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa55723719"><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> t <span class="token keyword">WHERE</span> <em class="replaceable">primary_key</em><span class="token operator">=</span><span class="token number">1</span><span class="token punctuation">;</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><em class="replaceable">primary_key</em><span class="token operator">=</span><span class="token number">1</span> <span class="token operator">AND</span> t2<span class="token punctuation">.</span><em class="replaceable">primary_key</em><span class="token operator">=</span>t1<span class="token punctuation">.</span>id<span class="token punctuation">;</span></code></pre>
     </div>
    </li>
    <li class="listitem">
     <p>
      The best join combination for joining the tables is found
              by trying all possibilities. If all columns in
      <code class="literal">
       ORDER BY
      </code>
      and
      <code class="literal">
       GROUP
              BY
      </code>
      clauses come from the same table, that table
              is preferred first when joining.
     </p>
    </li>
    <li class="listitem">
     <p>
      If there is an
      <code class="literal">
       ORDER BY
      </code>
      clause and a
              different
      <code class="literal">
       GROUP BY
      </code>
      clause, or if the
      <code class="literal">
       ORDER BY
      </code>
      or
      <code class="literal">
       GROUP BY
      </code>
      contains columns from tables other than the first table in
              the join queue, a temporary table is created.
     </p>
    </li>
    <li class="listitem">
     <p>
      If you use the
      <code class="literal">
       SQL_SMALL_RESULT
      </code>
      modifier, MySQL uses an in-memory temporary table.
     </p>
    </li>
    <li class="listitem">
     <p>
      Each table index is queried, and the best index is used
              unless the optimizer believes that it is more efficient to
              use a table scan. At one time, a scan was used based on
              whether the best index spanned more than 30% of the table,
              but a fixed percentage no longer determines the choice
              between using an index or a scan. The optimizer now is
              more complex and bases its estimate on additional factors
              such as table size, number of rows, and I/O block size.
     </p>
    </li>
    <li class="listitem">
     <p>
      In some cases, MySQL can read rows from the index without
              even consulting the data file. If all columns used from
              the index are numeric, only the index tree is used to
              resolve the query.
     </p>
    </li>
    <li class="listitem">
     <p>
      Before each row is output, those that do not match the
      <code class="literal">
       HAVING
      </code>
      clause are skipped.
     </p>
    </li>
   </ul>
  </div>
  <p>
   Some examples of queries that are very fast:
  </p>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa31198760"><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 function">COUNT</span><span class="token punctuation">(</span><span class="token operator">*</span><span class="token punctuation">)</span> <span class="token keyword">FROM</span> <em class="replaceable">tbl_name</em><span class="token punctuation">;</span>

<span class="token keyword">SELECT</span> <span class="token function">MIN</span><span class="token punctuation">(</span><em class="replaceable">key_part1</em><span class="token punctuation">)</span><span class="token punctuation">,</span><span class="token function">MAX</span><span class="token punctuation">(</span><em class="replaceable">key_part1</em><span class="token punctuation">)</span> <span class="token keyword">FROM</span> <em class="replaceable">tbl_name</em><span class="token punctuation">;</span>

<span class="token keyword">SELECT</span> <span class="token function">MAX</span><span class="token punctuation">(</span><em class="replaceable">key_part2</em><span class="token punctuation">)</span> <span class="token keyword">FROM</span> <em class="replaceable">tbl_name</em>
  <span class="token keyword">WHERE</span> <em class="replaceable">key_part1</em><span class="token operator">=</span><em class="replaceable">constant</em><span class="token punctuation">;</span>

<span class="token keyword">SELECT</span> <span class="token punctuation">.</span><span class="token punctuation">.</span><span class="token punctuation">.</span> <span class="token keyword">FROM</span> <em class="replaceable">tbl_name</em>
  <span class="token keyword">ORDER</span> <span class="token keyword">BY</span> <em class="replaceable">key_part1</em><span class="token punctuation">,</span><em class="replaceable">key_part2</em><span class="token punctuation">,</span><span class="token punctuation">.</span><span class="token punctuation">.</span><span class="token punctuation">.</span> <span class="token keyword">LIMIT</span> <span class="token number">10</span><span class="token punctuation">;</span>

<span class="token keyword">SELECT</span> <span class="token punctuation">.</span><span class="token punctuation">.</span><span class="token punctuation">.</span> <span class="token keyword">FROM</span> <em class="replaceable">tbl_name</em>
  <span class="token keyword">ORDER</span> <span class="token keyword">BY</span> <em class="replaceable">key_part1</em> <span class="token keyword">DESC</span><span class="token punctuation">,</span> <em class="replaceable">key_part2</em> <span class="token keyword">DESC</span><span class="token punctuation">,</span> <span class="token punctuation">.</span><span class="token punctuation">.</span><span class="token punctuation">.</span> <span class="token keyword">LIMIT</span> <span class="token number">10</span><span class="token punctuation">;</span></code></pre>
  </div>
  <p>
   MySQL resolves the following queries using only the index
          tree, assuming that the indexed columns are numeric:
  </p>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa77769662"><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> <em class="replaceable">key_part1</em><span class="token punctuation">,</span><em class="replaceable">key_part2</em> <span class="token keyword">FROM</span> <em class="replaceable">tbl_name</em> <span class="token keyword">WHERE</span> <em class="replaceable">key_part1</em><span class="token operator">=</span><em class="replaceable">val</em><span class="token punctuation">;</span>

<span class="token keyword">SELECT</span> <span class="token function">COUNT</span><span class="token punctuation">(</span><span class="token operator">*</span><span class="token punctuation">)</span> <span class="token keyword">FROM</span> <em class="replaceable">tbl_name</em>
  <span class="token keyword">WHERE</span> <em class="replaceable">key_part1</em><span class="token operator">=</span><em class="replaceable">val1</em> <span class="token operator">AND</span> <em class="replaceable">key_part2</em><span class="token operator">=</span><em class="replaceable">val2</em><span class="token punctuation">;</span>

<span class="token keyword">SELECT</span> <span class="token function">MAX</span><span class="token punctuation">(</span><em class="replaceable">key_part2</em><span class="token punctuation">)</span> <span class="token keyword">FROM</span> <em class="replaceable">tbl_name</em> <span class="token keyword">GROUP</span> <span class="token keyword">BY</span> <em class="replaceable">key_part1</em><span class="token punctuation">;</span></code></pre>
  </div>
  <p>
   The following queries use indexing to retrieve the rows in
          sorted order without a separate sorting pass:
  </p>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa68570056"><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 punctuation">.</span><span class="token punctuation">.</span><span class="token punctuation">.</span> <span class="token keyword">FROM</span> <em class="replaceable">tbl_name</em>
  <span class="token keyword">ORDER</span> <span class="token keyword">BY</span> <em class="replaceable">key_part1</em><span class="token punctuation">,</span><em class="replaceable">key_part2</em><span class="token punctuation">,</span><span class="token punctuation">.</span><span class="token punctuation">.</span><span class="token punctuation">.</span> <span class="token punctuation">;</span>

<span class="token keyword">SELECT</span> <span class="token punctuation">.</span><span class="token punctuation">.</span><span class="token punctuation">.</span> <span class="token keyword">FROM</span> <em class="replaceable">tbl_name</em>
  <span class="token keyword">ORDER</span> <span class="token keyword">BY</span> <em class="replaceable">key_part1</em> <span class="token keyword">DESC</span><span class="token punctuation">,</span> <em class="replaceable">key_part2</em> <span class="token keyword">DESC</span><span class="token punctuation">,</span> <span class="token punctuation">.</span><span class="token punctuation">.</span><span class="token punctuation">.</span> <span class="token punctuation">;</span></code></pre>
  </div>
 </div>
 <br/>
</div>
