<div id="docs-body">
 <div class="section">
  <div class="titlepage">
   <div>
    <div>
     <h4 class="title">
      <a name="range-optimization">
      </a>
      10.2.1.2 Range Optimization
     </h4>
    </div>
   </div>
  </div>
  <p>
   The
   <a class="link" href="explain-output.html#jointype_range">
    <code class="literal">
     range
    </code>
   </a>
   access method
          uses a single index to retrieve a subset of table rows that
          are contained within one or several index value intervals. It
          can be used for a single-part or multiple-part index. The
          following sections describe conditions under which the
          optimizer uses range access.
  </p>
  <div class="itemizedlist">
   <ul class="itemizedlist" style="list-style-type: disc; ">
    <li class="listitem">
     <p>
      <a class="xref" href="range-optimization.html#range-access-single-part" title="Range Access Method for Single-Part Indexes">
       Range Access Method for Single-Part Indexes
      </a>
     </p>
    </li>
    <li class="listitem">
     <p>
      <a class="xref" href="range-optimization.html#range-access-multi-part" title="Range Access Method for Multiple-Part Indexes">
       Range Access Method for Multiple-Part Indexes
      </a>
     </p>
    </li>
    <li class="listitem">
     <p>
      <a class="xref" href="range-optimization.html#equality-range-optimization" title="Equality Range Optimization of Many-Valued Comparisons">
       Equality Range Optimization of Many-Valued Comparisons
      </a>
     </p>
    </li>
    <li class="listitem">
     <p>
      <a class="xref" href="range-optimization.html#range-access-skip-scan" title="Skip Scan Range Access Method">
       Skip Scan Range Access Method
      </a>
     </p>
    </li>
    <li class="listitem">
     <p>
      <a class="xref" href="range-optimization.html#row-constructor-range-optimization" title="Range Optimization of Row Constructor Expressions">
       Range Optimization of Row Constructor Expressions
      </a>
     </p>
    </li>
    <li class="listitem">
     <p>
      <a class="xref" href="range-optimization.html#range-optimization-memory-use" title="Limiting Memory Use for Range Optimization">
       Limiting Memory Use for Range Optimization
      </a>
     </p>
    </li>
   </ul>
  </div>
  <div class="simplesect">
   <div class="titlepage">
    <div>
     <div class="simple">
      <h5 class="title">
       <a name="range-access-single-part">
       </a>
       Range Access Method for Single-Part Indexes
      </h5>
     </div>
    </div>
   </div>
   <p>
    For a single-part index, index value intervals can be
            conveniently represented by corresponding conditions in the
    <code class="literal">
     WHERE
    </code>
    clause, denoted as
    <span class="firstterm">
     range conditions
    </span>
    rather than
    <span class="quote">
     “
     <span class="quote">
      intervals.
     </span>
     ”
    </span>
   </p>
   <p>
    The definition of a range condition for a single-part index
            is as follows:
   </p>
   <div class="itemizedlist">
    <ul class="itemizedlist" style="list-style-type: disc; ">
     <li class="listitem">
      <p>
       For both
       <code class="literal">
        BTREE
       </code>
       and
       <code class="literal">
        HASH
       </code>
       indexes, comparison of a key
                part with a constant value is a range condition when
                using the
       <a class="link" href="comparison-operators.html#operator_equal">
        <code class="literal">
         =
        </code>
       </a>
       ,
       <a class="link" href="comparison-operators.html#operator_equal-to">
        <code class="literal">
         &lt;=&gt;
        </code>
       </a>
       ,
       <a class="link" href="comparison-operators.html#operator_in">
        <code class="literal">
         IN()
        </code>
       </a>
       ,
       <a class="link" href="comparison-operators.html#operator_is-null">
        <code class="literal">
         IS
                NULL
        </code>
       </a>
       , or
       <a class="link" href="comparison-operators.html#operator_is-not-null">
        <code class="literal">
         IS NOT
                NULL
        </code>
       </a>
       operators.
      </p>
     </li>
     <li class="listitem">
      <p>
       Additionally, for
       <code class="literal">
        BTREE
       </code>
       indexes,
                comparison of a key part with a constant value is a
                range condition when using the
       <a class="link" href="comparison-operators.html#operator_greater-than">
        <code class="literal">
         &gt;
        </code>
       </a>
       ,
       <a class="link" href="comparison-operators.html#operator_less-than">
        <code class="literal">
         &lt;
        </code>
       </a>
       ,
       <a class="link" href="comparison-operators.html#operator_greater-than-or-equal">
        <code class="literal">
         &gt;=
        </code>
       </a>
       ,
       <a class="link" href="comparison-operators.html#operator_less-than-or-equal">
        <code class="literal">
         &lt;=
        </code>
       </a>
       ,
       <a class="link" href="comparison-operators.html#operator_between">
        <code class="literal">
         BETWEEN
        </code>
       </a>
       ,
       <a class="link" href="comparison-operators.html#operator_not-equal">
        <code class="literal">
         !=
        </code>
       </a>
       ,
                or
       <a class="link" href="comparison-operators.html#operator_not-equal">
        <code class="literal">
         &lt;&gt;
        </code>
       </a>
       operators, or
       <a class="link" href="string-comparison-functions.html#operator_like">
        <code class="literal">
         LIKE
        </code>
       </a>
       comparisons if the argument to
       <a class="link" href="string-comparison-functions.html#operator_like">
        <code class="literal">
         LIKE
        </code>
       </a>
       is a constant string
                that does not start with a wildcard character.
      </p>
     </li>
     <li class="listitem">
      <p>
       For all index types, multiple range conditions combined
                with
       <a class="link" href="logical-operators.html#operator_or">
        <code class="literal">
         OR
        </code>
       </a>
       or
       <a class="link" href="logical-operators.html#operator_and">
        <code class="literal">
         AND
        </code>
       </a>
       form a range condition.
      </p>
     </li>
    </ul>
   </div>
   <p>
    <span class="quote">
     “
     <span class="quote">
      Constant value
     </span>
     ”
    </span>
    in the preceding descriptions
            means one of the following:
   </p>
   <div class="itemizedlist">
    <ul class="itemizedlist" style="list-style-type: disc; ">
     <li class="listitem">
      <p>
       A constant from the query string
      </p>
     </li>
     <li class="listitem">
      <p>
       A column of a
       <a class="link" href="explain-output.html#jointype_const">
        <code class="literal">
         const
        </code>
       </a>
       or
       <a class="link" href="explain-output.html#jointype_system">
        <code class="literal">
         system
        </code>
       </a>
       table from
                the same join
      </p>
     </li>
     <li class="listitem">
      <p>
       The result of an uncorrelated subquery
      </p>
     </li>
     <li class="listitem">
      <p>
       Any expression composed entirely from subexpressions of
                the preceding types
      </p>
     </li>
    </ul>
   </div>
   <p>
    Here are some examples of queries with range conditions in
            the
    <code class="literal">
     WHERE
    </code>
    clause:
   </p>
   <div class="copytoclipboard-wrapper">
    <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa49950879"><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 keyword">WHERE</span> <em class="replaceable">key_col</em> <span class="token operator">&gt;</span> <span class="token number">1</span>
  <span class="token operator">AND</span> <em class="replaceable">key_col</em> <span class="token operator">&lt;</span> <span class="token number">10</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 keyword">WHERE</span> <em class="replaceable">key_col</em> <span class="token operator">=</span> <span class="token number">1</span>
  <span class="token operator">OR</span> <em class="replaceable">key_col</em> <span class="token keyword">IN</span> <span class="token punctuation">(</span><span class="token number">15</span><span class="token punctuation">,</span><span class="token number">18</span><span class="token punctuation">,</span><span class="token number">20</span><span class="token punctuation">)</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 keyword">WHERE</span> <em class="replaceable">key_col</em> <span class="token operator">LIKE</span> <span class="token string">'ab%'</span>
  <span class="token operator">OR</span> <em class="replaceable">key_col</em> <span class="token operator">BETWEEN</span> <span class="token string">'bar'</span> <span class="token operator">AND</span> <span class="token string">'foo'</span><span class="token punctuation">;</span></code></pre>
   </div>
   <p>
    Some nonconstant values may be converted to constants during
            the optimizer constant propagation phase.
   </p>
   <p>
    MySQL tries to extract range conditions from the
    <code class="literal">
     WHERE
    </code>
    clause for each of the possible
            indexes. During the extraction process, conditions that
            cannot be used for constructing the range condition are
            dropped, conditions that produce overlapping ranges are
            combined, and conditions that produce empty ranges are
            removed.
   </p>
   <p>
    Consider the following statement, where
    <code class="literal">
     key1
    </code>
    is an indexed column and
    <code class="literal">
     nonkey
    </code>
    is not indexed:
   </p>
   <div class="copytoclipboard-wrapper">
    <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa88180431"><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 keyword">WHERE</span>
  <span class="token punctuation">(</span>key1 <span class="token operator">&lt;</span> <span class="token string">'abc'</span> <span class="token operator">AND</span> <span class="token punctuation">(</span>key1 <span class="token operator">LIKE</span> <span class="token string">'abcde%'</span> <span class="token operator">OR</span> key1 <span class="token operator">LIKE</span> <span class="token string">'%b'</span><span class="token punctuation">)</span><span class="token punctuation">)</span> <span class="token operator">OR</span>
  <span class="token punctuation">(</span>key1 <span class="token operator">&lt;</span> <span class="token string">'bar'</span> <span class="token operator">AND</span> nonkey <span class="token operator">=</span> <span class="token number">4</span><span class="token punctuation">)</span> <span class="token operator">OR</span>
  <span class="token punctuation">(</span>key1 <span class="token operator">&lt;</span> <span class="token string">'uux'</span> <span class="token operator">AND</span> key1 <span class="token operator">&gt;</span> <span class="token string">'z'</span><span class="token punctuation">)</span><span class="token punctuation">;</span></code></pre>
   </div>
   <p>
    The extraction process for key
    <code class="literal">
     key1
    </code>
    is as
            follows:
   </p>
   <div class="orderedlist">
    <ol class="orderedlist" type="1">
     <li class="listitem">
      <p>
       Start with original
       <code class="literal">
        WHERE
       </code>
       clause:
      </p>
      <div class="copytoclipboard-wrapper">
       <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa29192661"><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>key1 <span class="token operator">&lt;</span> <span class="token string">'abc'</span> <span class="token operator">AND</span> <span class="token punctuation">(</span>key1 <span class="token operator">LIKE</span> <span class="token string">'abcde%'</span> <span class="token operator">OR</span> key1 <span class="token operator">LIKE</span> <span class="token string">'%b'</span><span class="token punctuation">)</span><span class="token punctuation">)</span> <span class="token operator">OR</span>
<span class="token punctuation">(</span>key1 <span class="token operator">&lt;</span> <span class="token string">'bar'</span> <span class="token operator">AND</span> nonkey <span class="token operator">=</span> <span class="token number">4</span><span class="token punctuation">)</span> <span class="token operator">OR</span>
<span class="token punctuation">(</span>key1 <span class="token operator">&lt;</span> <span class="token string">'uux'</span> <span class="token operator">AND</span> key1 <span class="token operator">&gt;</span> <span class="token string">'z'</span><span class="token punctuation">)</span></code></pre>
      </div>
     </li>
     <li class="listitem">
      <p>
       Remove
       <code class="literal">
        nonkey = 4
       </code>
       and
       <code class="literal">
        key1
                LIKE '%b'
       </code>
       because they cannot be used for a
                range scan. The correct way to remove them is to replace
                them with
       <code class="literal">
        TRUE
       </code>
       , so that we do not
                miss any matching rows when doing the range scan.
                Replacing them with
       <code class="literal">
        TRUE
       </code>
       yields:
      </p>
      <div class="copytoclipboard-wrapper">
       <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa14577920"><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>key1 <span class="token operator">&lt;</span> <span class="token string">'abc'</span> <span class="token operator">AND</span> <span class="token punctuation">(</span>key1 <span class="token operator">LIKE</span> <span class="token string">'abcde%'</span> <span class="token operator">OR</span> <span class="token boolean">TRUE</span><span class="token punctuation">)</span><span class="token punctuation">)</span> <span class="token operator">OR</span>
<span class="token punctuation">(</span>key1 <span class="token operator">&lt;</span> <span class="token string">'bar'</span> <span class="token operator">AND</span> <span class="token boolean">TRUE</span><span class="token punctuation">)</span> <span class="token operator">OR</span>
<span class="token punctuation">(</span>key1 <span class="token operator">&lt;</span> <span class="token string">'uux'</span> <span class="token operator">AND</span> key1 <span class="token operator">&gt;</span> <span class="token string">'z'</span><span class="token punctuation">)</span></code></pre>
      </div>
     </li>
     <li class="listitem">
      <p>
       Collapse conditions that are always true or false:
      </p>
      <div class="itemizedlist">
       <ul class="itemizedlist" style="list-style-type: disc; ">
        <li class="listitem">
         <p>
          <code class="literal">
           (key1 LIKE 'abcde%' OR TRUE)
          </code>
          is
                    always true
         </p>
        </li>
        <li class="listitem">
         <p>
          <code class="literal">
           (key1 &lt; 'uux' AND key1 &gt;
                    'z')
          </code>
          is always false
         </p>
        </li>
       </ul>
      </div>
      <p>
       Replacing these conditions with constants yields:
      </p>
      <div class="copytoclipboard-wrapper">
       <pre class="programlisting one-line language-clike"><div class="docs-select-all right" id="sa87062046"><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-clike"><span class="token punctuation">(</span>key1 <span class="token operator">&lt;</span> <span class="token string">'abc'</span> AND TRUE<span class="token punctuation">)</span> OR <span class="token punctuation">(</span>key1 <span class="token operator">&lt;</span> <span class="token string">'bar'</span> AND TRUE<span class="token punctuation">)</span> OR <span class="token punctuation">(</span>FALSE<span class="token punctuation">)</span></code></pre>
      </div>
      <p>
       Removing unnecessary
       <code class="literal">
        TRUE
       </code>
       and
       <code class="literal">
        FALSE
       </code>
       constants yields:
      </p>
      <div class="copytoclipboard-wrapper">
       <pre class="programlisting one-line language-clike"><div class="docs-select-all right" id="sa58441168"><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-clike"><span class="token punctuation">(</span>key1 <span class="token operator">&lt;</span> <span class="token string">'abc'</span><span class="token punctuation">)</span> OR <span class="token punctuation">(</span>key1 <span class="token operator">&lt;</span> <span class="token string">'bar'</span><span class="token punctuation">)</span></code></pre>
      </div>
     </li>
     <li class="listitem">
      <p>
       Combining overlapping intervals into one yields the
                final condition to be used for the range scan:
      </p>
      <div class="copytoclipboard-wrapper">
       <pre class="programlisting one-line language-clike"><div class="docs-select-all right" id="sa31110657"><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-clike"><span class="token punctuation">(</span>key1 <span class="token operator">&lt;</span> <span class="token string">'bar'</span><span class="token punctuation">)</span></code></pre>
      </div>
     </li>
    </ol>
   </div>
   <p>
    In general (and as demonstrated by the preceding example),
            the condition used for a range scan is less restrictive than
            the
    <code class="literal">
     WHERE
    </code>
    clause. MySQL performs an
            additional check to filter out rows that satisfy the range
            condition but not the full
    <code class="literal">
     WHERE
    </code>
    clause.
   </p>
   <p>
    The range condition extraction algorithm can handle nested
    <a class="link" href="logical-operators.html#operator_and">
     <code class="literal">
      AND
     </code>
    </a>
    /
    <a class="link" href="logical-operators.html#operator_or">
     <code class="literal">
      OR
     </code>
    </a>
    constructs of arbitrary depth, and its output does not
            depend on the order in which conditions appear in
    <code class="literal">
     WHERE
    </code>
    clause.
   </p>
   <p>
    MySQL does not support merging multiple ranges for the
    <a class="link" href="explain-output.html#jointype_range">
     <code class="literal">
      range
     </code>
    </a>
    access method for
            spatial indexes. To work around this limitation, you can use
            a
    <a class="link" href="union.html" title="15.2.18 UNION Clause">
     <code class="literal">
      UNION
     </code>
    </a>
    with identical
    <a class="link" href="select.html" title="15.2.13 SELECT Statement">
     <code class="literal">
      SELECT
     </code>
    </a>
    statements, except
            that you put each spatial predicate in a different
    <a class="link" href="select.html" title="15.2.13 SELECT Statement">
     <code class="literal">
      SELECT
     </code>
    </a>
    .
   </p>
  </div>
  <div class="simplesect">
   <div class="titlepage">
    <div>
     <div class="simple">
      <h5 class="title">
       <a name="range-access-multi-part">
       </a>
       Range Access Method for Multiple-Part Indexes
      </h5>
     </div>
    </div>
   </div>
   <p>
    Range conditions on a multiple-part index are an extension
            of range conditions for a single-part index. A range
            condition on a multiple-part index restricts index rows to
            lie within one or several key tuple intervals. Key tuple
            intervals are defined over a set of key tuples, using
            ordering from the index.
   </p>
   <p>
    For example, consider a multiple-part index defined as
    <code class="literal">
     key1(
     <em class="replaceable">
      <code>
       key_part1
      </code>
     </em>
     ,
     <em class="replaceable">
      <code>
       key_part2
      </code>
     </em>
     ,
     <em class="replaceable">
      <code>
       key_part3
      </code>
     </em>
     )
    </code>
    , and the
            following set of key tuples listed in key order:
   </p>
   <div class="copytoclipboard-wrapper">
    <pre class="programlisting language-clike"><div class="docs-select-all right" id="sa16897392"><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-clike"><em class="replaceable">key_part1</em>  <em class="replaceable">key_part2</em>  <em class="replaceable">key_part3</em>
  NULL       <span class="token number">1</span>          <span class="token string">'abc'</span>
  NULL       <span class="token number">1</span>          <span class="token string">'xyz'</span>
  NULL       <span class="token number">2</span>          <span class="token string">'foo'</span>
   <span class="token number">1</span>         <span class="token number">1</span>          <span class="token string">'abc'</span>
   <span class="token number">1</span>         <span class="token number">1</span>          <span class="token string">'xyz'</span>
   <span class="token number">1</span>         <span class="token number">2</span>          <span class="token string">'abc'</span>
   <span class="token number">2</span>         <span class="token number">1</span>          <span class="token string">'aaa'</span></code></pre>
   </div>
   <p>
    The condition
    <code class="literal">
     <em class="replaceable">
      <code>
       key_part1
      </code>
     </em>
     = 1
    </code>
    defines this interval:
   </p>
   <div class="copytoclipboard-wrapper">
    <pre class="programlisting one-line language-clike"><div class="docs-select-all right" id="sa24629121"><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-clike"><span class="token punctuation">(</span><span class="token number">1</span><span class="token punctuation">,</span><span class="token operator">-</span>inf<span class="token punctuation">,</span><span class="token operator">-</span>inf<span class="token punctuation">)</span> <span class="token operator">&lt;=</span> <span class="token punctuation">(</span><em class="replaceable">key_part1</em><span class="token punctuation">,</span><em class="replaceable">key_part2</em><span class="token punctuation">,</span><em class="replaceable">key_part3</em><span class="token punctuation">)</span> <span class="token operator">&lt;</span> <span class="token punctuation">(</span><span class="token number">1</span><span class="token punctuation">,</span><span class="token operator">+</span>inf<span class="token punctuation">,</span><span class="token operator">+</span>inf<span class="token punctuation">)</span></code></pre>
   </div>
   <p>
    The interval covers the 4th, 5th, and 6th tuples in the
            preceding data set and can be used by the range access
            method.
   </p>
   <p>
    By contrast, the condition
    <code class="literal">
     <em class="replaceable">
      <code>
       key_part3
      </code>
     </em>
     =
            'abc'
    </code>
    does not define a single interval and cannot
            be used by the range access method.
   </p>
   <p>
    The following descriptions indicate how range conditions
            work for multiple-part indexes in greater detail.
   </p>
   <div class="itemizedlist">
    <ul class="itemizedlist" style="list-style-type: disc; ">
     <li class="listitem">
      <p>
       For
       <code class="literal">
        HASH
       </code>
       indexes, each interval
                containing identical values can be used. This means that
                the interval can be produced only for conditions in the
                following form:
      </p>
      <div class="copytoclipboard-wrapper">
       <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa62957069"><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">    <em class="replaceable">key_part1</em> <em class="replaceable">cmp</em> <em class="replaceable">const1</em>
<span class="token operator">AND</span> <em class="replaceable">key_part2</em> <em class="replaceable">cmp</em> <em class="replaceable">const2</em>
<span class="token operator">AND</span> <span class="token punctuation">.</span><span class="token punctuation">.</span><span class="token punctuation">.</span>
<span class="token operator">AND</span> <em class="replaceable">key_partN</em> <em class="replaceable">cmp</em> <em class="replaceable">constN</em><span class="token punctuation">;</span></code></pre>
      </div>
      <p>
       Here,
       <em class="replaceable">
        <code>
         const1
        </code>
       </em>
       ,
       <em class="replaceable">
        <code>
         const2
        </code>
       </em>
       , … are
                constants,
       <em class="replaceable">
        <code>
         cmp
        </code>
       </em>
       is one of the
       <a class="link" href="comparison-operators.html#operator_equal">
        <code class="literal">
         =
        </code>
       </a>
       ,
       <a class="link" href="comparison-operators.html#operator_equal-to">
        <code class="literal">
         &lt;=&gt;
        </code>
       </a>
       ,
                or
       <a class="link" href="comparison-operators.html#operator_is-null">
        <code class="literal">
         IS NULL
        </code>
       </a>
       comparison
                operators, and the conditions cover all index parts.
                (That is, there are
       <em class="replaceable">
        <code>
         N
        </code>
       </em>
       conditions, one for each part of an
       <em class="replaceable">
        <code>
         N
        </code>
       </em>
       -part index.) For example,
                the following is a range condition for a three-part
       <code class="literal">
        HASH
       </code>
       index:
      </p>
      <div class="copytoclipboard-wrapper">
       <pre class="programlisting one-line language-sql"><div class="docs-select-all right" id="sa10582168"><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"><em class="replaceable">key_part1</em> <span class="token operator">=</span> <span class="token number">1</span> <span class="token operator">AND</span> <em class="replaceable">key_part2</em> <span class="token operator">IS</span> <span class="token boolean">NULL</span> <span class="token operator">AND</span> <em class="replaceable">key_part3</em> <span class="token operator">=</span> <span class="token string">'foo'</span></code></pre>
      </div>
      <p>
       For the definition of what is considered to be a
                constant, see
       <a class="xref" href="range-optimization.html#range-access-single-part" title="Range Access Method for Single-Part Indexes">
        Range Access Method for Single-Part Indexes
       </a>
       .
      </p>
     </li>
     <li class="listitem">
      <p>
       For a
       <code class="literal">
        BTREE
       </code>
       index, an interval might
                be usable for conditions combined with
       <a class="link" href="logical-operators.html#operator_and">
        <code class="literal">
         AND
        </code>
       </a>
       , where each condition
                compares a key part with a constant value using
       <a class="link" href="comparison-operators.html#operator_equal">
        <code class="literal">
         =
        </code>
       </a>
       ,
       <a class="link" href="comparison-operators.html#operator_equal-to">
        <code class="literal">
         &lt;=&gt;
        </code>
       </a>
       ,
       <a class="link" href="comparison-operators.html#operator_is-null">
        <code class="literal">
         IS NULL
        </code>
       </a>
       ,
       <a class="link" href="comparison-operators.html#operator_greater-than">
        <code class="literal">
         &gt;
        </code>
       </a>
       ,
       <a class="link" href="comparison-operators.html#operator_less-than">
        <code class="literal">
         &lt;
        </code>
       </a>
       ,
       <a class="link" href="comparison-operators.html#operator_greater-than-or-equal">
        <code class="literal">
         &gt;=
        </code>
       </a>
       ,
       <a class="link" href="comparison-operators.html#operator_less-than-or-equal">
        <code class="literal">
         &lt;=
        </code>
       </a>
       ,
       <a class="link" href="comparison-operators.html#operator_not-equal">
        <code class="literal">
         !=
        </code>
       </a>
       ,
       <a class="link" href="comparison-operators.html#operator_not-equal">
        <code class="literal">
         &lt;&gt;
        </code>
       </a>
       ,
       <a class="link" href="comparison-operators.html#operator_between">
        <code class="literal">
         BETWEEN
        </code>
       </a>
       , or
       <a class="link" href="string-comparison-functions.html#operator_like">
        <code class="literal">
         LIKE
                '
         <em class="replaceable">
          <code>
           pattern
          </code>
         </em>
         '
        </code>
       </a>
       (where
       <code class="literal">
        '
        <em class="replaceable">
         <code>
          pattern
         </code>
        </em>
        '
       </code>
       does not start with a wildcard). An interval can be used
                as long as it is possible to determine a single key
                tuple containing all rows that match the condition (or
                two intervals if
       <a class="link" href="comparison-operators.html#operator_not-equal">
        <code class="literal">
         &lt;&gt;
        </code>
       </a>
       or
       <a class="link" href="comparison-operators.html#operator_not-equal">
        <code class="literal">
         !=
        </code>
       </a>
       is used).
      </p>
      <p>
       The optimizer attempts to use additional key parts to
                determine the interval as long as the comparison
                operator is
       <a class="link" href="comparison-operators.html#operator_equal">
        <code class="literal">
         =
        </code>
       </a>
       ,
       <a class="link" href="comparison-operators.html#operator_equal-to">
        <code class="literal">
         &lt;=&gt;
        </code>
       </a>
       ,
                or
       <a class="link" href="comparison-operators.html#operator_is-null">
        <code class="literal">
         IS NULL
        </code>
       </a>
       . If the operator
                is
       <a class="link" href="comparison-operators.html#operator_greater-than">
        <code class="literal">
         &gt;
        </code>
       </a>
       ,
       <a class="link" href="comparison-operators.html#operator_less-than">
        <code class="literal">
         &lt;
        </code>
       </a>
       ,
       <a class="link" href="comparison-operators.html#operator_greater-than-or-equal">
        <code class="literal">
         &gt;=
        </code>
       </a>
       ,
       <a class="link" href="comparison-operators.html#operator_less-than-or-equal">
        <code class="literal">
         &lt;=
        </code>
       </a>
       ,
       <a class="link" href="comparison-operators.html#operator_not-equal">
        <code class="literal">
         !=
        </code>
       </a>
       ,
       <a class="link" href="comparison-operators.html#operator_not-equal">
        <code class="literal">
         &lt;&gt;
        </code>
       </a>
       ,
       <a class="link" href="comparison-operators.html#operator_between">
        <code class="literal">
         BETWEEN
        </code>
       </a>
       , or
       <a class="link" href="string-comparison-functions.html#operator_like">
        <code class="literal">
         LIKE
        </code>
       </a>
       , the
                optimizer uses it but considers no more key parts. For
                the following expression, the optimizer uses
       <a class="link" href="comparison-operators.html#operator_equal">
        <code class="literal">
         =
        </code>
       </a>
       from
                the first comparison. It also uses
       <a class="link" href="comparison-operators.html#operator_greater-than-or-equal">
        <code class="literal">
         &gt;=
        </code>
       </a>
       from the second comparison but considers no further key
                parts and does not use the third comparison for interval
                construction:
      </p>
      <div class="copytoclipboard-wrapper">
       <pre class="programlisting one-line language-sql"><div class="docs-select-all right" id="sa91398184"><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"><em class="replaceable">key_part1</em> <span class="token operator">=</span> <span class="token string">'foo'</span> <span class="token operator">AND</span> <em class="replaceable">key_part2</em> <span class="token operator">&gt;=</span> <span class="token number">10</span> <span class="token operator">AND</span> <em class="replaceable">key_part3</em> <span class="token operator">&gt;</span> <span class="token number">10</span></code></pre>
      </div>
      <p>
       The single interval is:
      </p>
      <div class="copytoclipboard-wrapper">
       <pre class="programlisting one-line language-sql"><div class="docs-select-all right" id="sa86354950"><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 string">'foo'</span><span class="token punctuation">,</span><span class="token number">10</span><span class="token punctuation">,</span><span class="token operator">-</span>inf<span class="token punctuation">)</span> <span class="token operator">&lt;</span> <span class="token punctuation">(</span><em class="replaceable">key_part1</em><span class="token punctuation">,</span><em class="replaceable">key_part2</em><span class="token punctuation">,</span><em class="replaceable">key_part3</em><span class="token punctuation">)</span> <span class="token operator">&lt;</span> <span class="token punctuation">(</span><span class="token string">'foo'</span><span class="token punctuation">,</span><span class="token operator">+</span>inf<span class="token punctuation">,</span><span class="token operator">+</span>inf<span class="token punctuation">)</span></code></pre>
      </div>
      <p>
       It is possible that the created interval contains more
                rows than the initial condition. For example, the
                preceding interval includes the value
       <code class="literal">
        ('foo',
                11, 0)
       </code>
       , which does not satisfy the original
                condition.
      </p>
     </li>
     <li class="listitem">
      <p>
       If conditions that cover sets of rows contained within
                intervals are combined with
       <a class="link" href="logical-operators.html#operator_or">
        <code class="literal">
         OR
        </code>
       </a>
       , they form a condition
                that covers a set of rows contained within the union of
                their intervals. If the conditions are combined with
       <a class="link" href="logical-operators.html#operator_and">
        <code class="literal">
         AND
        </code>
       </a>
       , they form a condition
                that covers a set of rows contained within the
                intersection of their intervals. For example, for this
                condition on a two-part index:
      </p>
      <div class="copytoclipboard-wrapper">
       <pre class="programlisting one-line language-sql"><div class="docs-select-all right" id="sa78629113"><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><em class="replaceable">key_part1</em> <span class="token operator">=</span> <span class="token number">1</span> <span class="token operator">AND</span> <em class="replaceable">key_part2</em> <span class="token operator">&lt;</span> <span class="token number">2</span><span class="token punctuation">)</span> <span class="token operator">OR</span> <span class="token punctuation">(</span><em class="replaceable">key_part1</em> <span class="token operator">&gt;</span> <span class="token number">5</span><span class="token punctuation">)</span></code></pre>
      </div>
      <p>
       The intervals are:
      </p>
      <div class="copytoclipboard-wrapper">
       <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa65961418"><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 number">1</span><span class="token punctuation">,</span><span class="token operator">-</span>inf<span class="token punctuation">)</span> <span class="token operator">&lt;</span> <span class="token punctuation">(</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 operator">&lt;</span> <span class="token punctuation">(</span><span class="token number">1</span><span class="token punctuation">,</span><span class="token number">2</span><span class="token punctuation">)</span>
<span class="token punctuation">(</span><span class="token number">5</span><span class="token punctuation">,</span><span class="token operator">-</span>inf<span class="token punctuation">)</span> <span class="token operator">&lt;</span> <span class="token punctuation">(</span><em class="replaceable">key_part1</em><span class="token punctuation">,</span><em class="replaceable">key_part2</em><span class="token punctuation">)</span></code></pre>
      </div>
      <p>
       In this example, the interval on the first line uses one
                key part for the left bound and two key parts for the
                right bound. The interval on the second line uses only
                one key part. The
       <code class="literal">
        key_len
       </code>
       column in
                the
       <a class="link" href="explain.html" title="15.8.2 EXPLAIN Statement">
        <code class="literal">
         EXPLAIN
        </code>
       </a>
       output
                indicates the maximum length of the key prefix used.
      </p>
      <p>
       In some cases,
       <code class="literal">
        key_len
       </code>
       may indicate
                that a key part was used, but that might be not what you
                would expect. Suppose that
       <em class="replaceable">
        <code>
         key_part1
        </code>
       </em>
       and
       <em class="replaceable">
        <code>
         key_part2
        </code>
       </em>
       can be
       <code class="literal">
        NULL
       </code>
       . Then the
       <code class="literal">
        key_len
       </code>
       column displays two key part
                lengths for the following condition:
      </p>
      <div class="copytoclipboard-wrapper">
       <pre class="programlisting one-line language-sql"><div class="docs-select-all right" id="sa40809430"><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"><em class="replaceable">key_part1</em> <span class="token operator">&gt;=</span> <span class="token number">1</span> <span class="token operator">AND</span> <em class="replaceable">key_part2</em> <span class="token operator">&lt;</span> <span class="token number">2</span></code></pre>
      </div>
      <p>
       But, in fact, the condition is converted to this:
      </p>
      <div class="copytoclipboard-wrapper">
       <pre class="programlisting one-line language-sql"><div class="docs-select-all right" id="sa73614341"><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"><em class="replaceable">key_part1</em> <span class="token operator">&gt;=</span> <span class="token number">1</span> <span class="token operator">AND</span> <em class="replaceable">key_part2</em> <span class="token operator">IS</span> <span class="token operator">NOT</span> <span class="token boolean">NULL</span></code></pre>
      </div>
     </li>
    </ul>
   </div>
   <p>
    For a description of how optimizations are performed to
            combine or eliminate intervals for range conditions on a
            single-part index, see
    <a class="xref" href="range-optimization.html#range-access-single-part" title="Range Access Method for Single-Part Indexes">
     Range Access Method for Single-Part Indexes
    </a>
    . Analogous steps
            are performed for range conditions on multiple-part indexes.
   </p>
  </div>
  <div class="simplesect">
   <div class="titlepage">
    <div>
     <div class="simple">
      <h5 class="title">
       <a name="equality-range-optimization">
       </a>
       Equality Range Optimization of Many-Valued Comparisons
      </h5>
     </div>
    </div>
   </div>
   <p>
    Consider these expressions, where
    <em class="replaceable">
     <code>
      col_name
     </code>
    </em>
    is an indexed column:
   </p>
   <div class="copytoclipboard-wrapper">
    <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa21407270"><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"><em class="replaceable">col_name</em> <span class="token keyword">IN</span><span class="token punctuation">(</span><em class="replaceable">val1</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> <em class="replaceable">valN</em><span class="token punctuation">)</span>
<em class="replaceable">col_name</em> <span class="token operator">=</span> <em class="replaceable">val1</em> <span class="token operator">OR</span> <span class="token punctuation">.</span><span class="token punctuation">.</span><span class="token punctuation">.</span> <span class="token operator">OR</span> <em class="replaceable">col_name</em> <span class="token operator">=</span> <em class="replaceable">valN</em></code></pre>
   </div>
   <p>
    Each expression is true if
    <em class="replaceable">
     <code>
      col_name
     </code>
    </em>
    is equal to any of
            several values. These comparisons are equality range
            comparisons (where the
    <span class="quote">
     “
     <span class="quote">
      range
     </span>
     ”
    </span>
    is a single
            value). The optimizer estimates the cost of reading
            qualifying rows for equality range comparisons as follows:
   </p>
   <div class="itemizedlist">
    <ul class="itemizedlist" style="list-style-type: disc; ">
     <li class="listitem">
      <p>
       If there is a unique index on
       <em class="replaceable">
        <code>
         col_name
        </code>
       </em>
       , the row estimate
                for each range is 1 because at most one row can have the
                given value.
      </p>
     </li>
     <li class="listitem">
      <p>
       Otherwise, any index on
       <em class="replaceable">
        <code>
         col_name
        </code>
       </em>
       is nonunique and the
                optimizer can estimate the row count for each range
                using dives into the index or index statistics.
      </p>
     </li>
    </ul>
   </div>
   <a class="indexterm" name="idm46045230043600">
   </a>
   <p>
    With index dives, the optimizer makes a dive at each end of
            a range and uses the number of rows in the range as the
            estimate. For example, the expression
    <code class="literal">
     <em class="replaceable">
      <code>
       col_name
      </code>
     </em>
     IN (10, 20,
            30)
    </code>
    has three equality ranges and the optimizer
            makes two dives per range to generate a row estimate. Each
            pair of dives yields an estimate of the number of rows that
            have the given value.
   </p>
   <p>
    Index dives provide accurate row estimates, but as the
            number of comparison values in the expression increases, the
            optimizer takes longer to generate a row estimate. Use of
            index statistics is less accurate than index dives but
            permits faster row estimation for large value lists.
   </p>
   <p>
    The
    <a class="link" href="server-system-variables.html#sysvar_eq_range_index_dive_limit">
     <code class="literal">
      eq_range_index_dive_limit
     </code>
    </a>
    system variable enables you to configure the number of
            values at which the optimizer switches from one row
            estimation strategy to the other. To permit use of index
            dives for comparisons of up to
    <em class="replaceable">
     <code>
      N
     </code>
    </em>
    equality ranges, set
    <a class="link" href="server-system-variables.html#sysvar_eq_range_index_dive_limit">
     <code class="literal">
      eq_range_index_dive_limit
     </code>
    </a>
    to
    <em class="replaceable">
     <code>
      N
     </code>
    </em>
    + 1. To disable use of
            statistics and always use index dives regardless of
    <em class="replaceable">
     <code>
      N
     </code>
    </em>
    , set
    <a class="link" href="server-system-variables.html#sysvar_eq_range_index_dive_limit">
     <code class="literal">
      eq_range_index_dive_limit
     </code>
    </a>
    to 0.
   </p>
   <p>
    To update table index statistics for best estimates, use
    <a class="link" href="analyze-table.html" title="15.7.3.1 ANALYZE TABLE Statement">
     <code class="literal">
      ANALYZE TABLE
     </code>
    </a>
    .
   </p>
   <p>
    Prior to MySQL 8.4, there is no way of skipping
            the use of index dives to estimate index usefulness, except
            by using the
    <a class="link" href="server-system-variables.html#sysvar_eq_range_index_dive_limit">
     <code class="literal">
      eq_range_index_dive_limit
     </code>
    </a>
    system variable. In MySQL 8.4, index dive
            skipping is possible for queries that satisfy all these
            conditions:
   </p>
   <div class="itemizedlist">
    <ul class="itemizedlist" style="list-style-type: disc; ">
     <li class="listitem">
      <p>
       The query is for a single table, not a join on multiple
                tables.
      </p>
     </li>
     <li class="listitem">
      <p>
       A single-index
       <code class="literal">
        FORCE INDEX
       </code>
       index hint
                is present. The idea is that if index use is forced,
                there is nothing to be gained from the additional
                overhead of performing dives into the index.
      </p>
     </li>
     <li class="listitem">
      <p>
       The index is nonunique and not a
       <code class="literal">
        FULLTEXT
       </code>
       index.
      </p>
     </li>
     <li class="listitem">
      <p>
       No subquery is present.
      </p>
     </li>
     <li class="listitem">
      <p>
       No
       <code class="literal">
        DISTINCT
       </code>
       ,
       <code class="literal">
        GROUP
                BY
       </code>
       , or
       <code class="literal">
        ORDER BY
       </code>
       clause is
                present.
      </p>
     </li>
    </ul>
   </div>
   <p>
    For
    <a class="link" href="explain.html" title="15.8.2 EXPLAIN Statement">
     <code class="literal">
      EXPLAIN FOR
            CONNECTION
     </code>
    </a>
    , the output changes as follows if index
            dives are skipped:
   </p>
   <div class="itemizedlist">
    <ul class="itemizedlist" style="list-style-type: disc; ">
     <li class="listitem">
      <p>
       For traditional output, the
       <code class="literal">
        rows
       </code>
       and
       <code class="literal">
        filtered
       </code>
       values are
       <code class="literal">
        NULL
       </code>
       .
      </p>
     </li>
     <li class="listitem">
      <p>
       For JSON output,
       <code class="literal">
        rows_examined_per_scan
       </code>
       and
       <code class="literal">
        rows_produced_per_join
       </code>
       do not appear,
       <code class="literal">
        skip_index_dive_due_to_force
       </code>
       is
       <code class="literal">
        true
       </code>
       , and cost calculations are not
                accurate.
      </p>
     </li>
    </ul>
   </div>
   <p>
    Without
    <code class="literal">
     FOR CONNECTION
    </code>
    ,
    <a class="link" href="explain.html" title="15.8.2 EXPLAIN Statement">
     <code class="literal">
      EXPLAIN
     </code>
    </a>
    output does not
            change when index dives are skipped.
   </p>
   <p>
    After execution of a query for which index dives are
            skipped, the corresponding row in the Information Schema
    <a class="link" href="information-schema-optimizer-trace-table.html" title="28.3.19 The INFORMATION_SCHEMA OPTIMIZER_TRACE Table">
     <code class="literal">
      OPTIMIZER_TRACE
     </code>
    </a>
    table contains
            an
    <code class="literal">
     index_dives_for_range_access
    </code>
    value of
    <code class="literal">
     skipped_due_to_force_index
    </code>
    .
   </p>
  </div>
  <div class="simplesect">
   <div class="titlepage">
    <div>
     <div class="simple">
      <h5 class="title">
       <a name="range-access-skip-scan">
       </a>
       Skip Scan Range Access Method
      </h5>
     </div>
    </div>
   </div>
   <p>
    Consider the following scenario:
   </p>
   <div class="copytoclipboard-wrapper">
    <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa44510508"><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">CREATE</span> <span class="token keyword">TABLE</span> t1 <span class="token punctuation">(</span>f1 <span class="token datatype">INT</span> <span class="token operator">NOT</span> <span class="token boolean">NULL</span><span class="token punctuation">,</span> f2 <span class="token datatype">INT</span> <span class="token operator">NOT</span> <span class="token boolean">NULL</span><span class="token punctuation">,</span> <span class="token keyword">PRIMARY</span> <span class="token keyword">KEY</span><span class="token punctuation">(</span>f1<span class="token punctuation">,</span> f2<span class="token punctuation">)</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
<span class="token keyword">INSERT</span> <span class="token keyword">INTO</span> t1 <span class="token keyword">VALUES</span>
  <span class="token punctuation">(</span><span class="token number">1</span><span class="token punctuation">,</span><span class="token number">1</span><span class="token punctuation">)</span><span class="token punctuation">,</span> <span class="token punctuation">(</span><span class="token number">1</span><span class="token punctuation">,</span><span class="token number">2</span><span class="token punctuation">)</span><span class="token punctuation">,</span> <span class="token punctuation">(</span><span class="token number">1</span><span class="token punctuation">,</span><span class="token number">3</span><span class="token punctuation">)</span><span class="token punctuation">,</span> <span class="token punctuation">(</span><span class="token number">1</span><span class="token punctuation">,</span><span class="token number">4</span><span class="token punctuation">)</span><span class="token punctuation">,</span> <span class="token punctuation">(</span><span class="token number">1</span><span class="token punctuation">,</span><span class="token number">5</span><span class="token punctuation">)</span><span class="token punctuation">,</span>
  <span class="token punctuation">(</span><span class="token number">2</span><span class="token punctuation">,</span><span class="token number">1</span><span class="token punctuation">)</span><span class="token punctuation">,</span> <span class="token punctuation">(</span><span class="token number">2</span><span class="token punctuation">,</span><span class="token number">2</span><span class="token punctuation">)</span><span class="token punctuation">,</span> <span class="token punctuation">(</span><span class="token number">2</span><span class="token punctuation">,</span><span class="token number">3</span><span class="token punctuation">)</span><span class="token punctuation">,</span> <span class="token punctuation">(</span><span class="token number">2</span><span class="token punctuation">,</span><span class="token number">4</span><span class="token punctuation">)</span><span class="token punctuation">,</span> <span class="token punctuation">(</span><span class="token number">2</span><span class="token punctuation">,</span><span class="token number">5</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
<span class="token keyword">INSERT</span> <span class="token keyword">INTO</span> t1 <span class="token keyword">SELECT</span> f1<span class="token punctuation">,</span> f2 <span class="token operator">+</span> <span class="token number">5</span> <span class="token keyword">FROM</span> t1<span class="token punctuation">;</span>
<span class="token keyword">INSERT</span> <span class="token keyword">INTO</span> t1 <span class="token keyword">SELECT</span> f1<span class="token punctuation">,</span> f2 <span class="token operator">+</span> <span class="token number">10</span> <span class="token keyword">FROM</span> t1<span class="token punctuation">;</span>
<span class="token keyword">INSERT</span> <span class="token keyword">INTO</span> t1 <span class="token keyword">SELECT</span> f1<span class="token punctuation">,</span> f2 <span class="token operator">+</span> <span class="token number">20</span> <span class="token keyword">FROM</span> t1<span class="token punctuation">;</span>
<span class="token keyword">INSERT</span> <span class="token keyword">INTO</span> t1 <span class="token keyword">SELECT</span> f1<span class="token punctuation">,</span> f2 <span class="token operator">+</span> <span class="token number">40</span> <span class="token keyword">FROM</span> t1<span class="token punctuation">;</span>
<span class="token keyword">ANALYZE</span> <span class="token keyword">TABLE</span> t1<span class="token punctuation">;</span>

<span class="token keyword">EXPLAIN</span> <span class="token keyword">SELECT</span> f1<span class="token punctuation">,</span> f2 <span class="token keyword">FROM</span> t1 <span class="token keyword">WHERE</span> f2 <span class="token operator">&gt;</span> <span class="token number">40</span><span class="token punctuation">;</span></code></pre>
   </div>
   <p>
    To execute this query, MySQL can choose an index scan to
            fetch all rows (the index includes all columns to be
            selected), then apply the
    <code class="literal">
     f2 &gt; 40
    </code>
    condition from the
    <code class="literal">
     WHERE
    </code>
    clause to
            produce the final result set.
   </p>
   <p>
    A range scan is more efficient than a full index scan, but
            cannot be used in this case because there is no condition on
    <code class="literal">
     f1
    </code>
    , the first index column. The optimizer
            can perform multiple range scans, one for each value of
    <code class="literal">
     f1
    </code>
    , using a method called Skip Scan that
            is similar to Loose Index Scan (see
    <a class="xref" href="group-by-optimization.html" title="10.2.1.17 GROUP BY Optimization">
     Section 10.2.1.17, “GROUP BY Optimization”
    </a>
    ):
   </p>
   <div class="orderedlist">
    <ol class="orderedlist" type="1">
     <li class="listitem">
      <p>
       Skip between distinct values of the first index part,
       <code class="literal">
        f1
       </code>
       (the index prefix).
      </p>
     </li>
     <li class="listitem">
      <p>
       Perform a subrange scan on each distinct prefix value
                for the
       <code class="literal">
        f2 &gt; 40
       </code>
       condition on the
                remaining index part.
      </p>
     </li>
    </ol>
   </div>
   <p>
    For the data set shown earlier, the algorithm operates like
            this:
   </p>
   <div class="orderedlist">
    <ol class="orderedlist" type="1">
     <li class="listitem">
      <p>
       Get the first distinct value of the first key part
                (
       <code class="literal">
        f1 = 1
       </code>
       ).
      </p>
     </li>
     <li class="listitem">
      <p>
       Construct the range based on the first and second key
                parts (
       <code class="literal">
        f1 = 1 AND f2 &gt; 40
       </code>
       ).
      </p>
     </li>
     <li class="listitem">
      <p>
       Perform a range scan.
      </p>
     </li>
     <li class="listitem">
      <p>
       Get the next distinct value of the first key part
                (
       <code class="literal">
        f1 = 2
       </code>
       ).
      </p>
     </li>
     <li class="listitem">
      <p>
       Construct the range based on the first and second key
                parts (
       <code class="literal">
        f1 = 2 AND f2 &gt; 40
       </code>
       ).
      </p>
     </li>
     <li class="listitem">
      <p>
       Perform a range scan.
      </p>
     </li>
    </ol>
   </div>
   <p>
    Using this strategy decreases the number of accessed rows
            because MySQL skips the rows that do not qualify for each
            constructed range. This Skip Scan access method is
            applicable under the following conditions:
   </p>
   <div class="itemizedlist">
    <ul class="itemizedlist" style="list-style-type: disc; ">
     <li class="listitem">
      <p>
       Table T has at least one compound index with key parts
                of the form ([A_1, ..., A_
       <em class="replaceable">
        <code>
         k
        </code>
       </em>
       ,]
                B_1, ..., B_
       <em class="replaceable">
        <code>
         m
        </code>
       </em>
       , C [, D_1, ...,
                D_
       <em class="replaceable">
        <code>
         n
        </code>
       </em>
       ]). Key parts A and D may
                be empty, but B and C must be nonempty.
      </p>
     </li>
     <li class="listitem">
      <p>
       The query references only one table.
      </p>
     </li>
     <li class="listitem">
      <p>
       The query does not use
       <code class="literal">
        GROUP BY
       </code>
       or
       <code class="literal">
        DISTINCT
       </code>
       .
      </p>
     </li>
     <li class="listitem">
      <p>
       The query references only columns in the index.
      </p>
     </li>
     <li class="listitem">
      <p>
       The predicates on A_1, ...,
                A_
       <em class="replaceable">
        <code>
         k
        </code>
       </em>
       must be equality
                predicates and they must be constants. This includes the
       <a class="link" href="comparison-operators.html#operator_in">
        <code class="literal">
         IN()
        </code>
       </a>
       operator.
      </p>
     </li>
     <li class="listitem">
      <p>
       The query must be a conjunctive query; that is, an
       <code class="literal">
        AND
       </code>
       of
       <code class="literal">
        OR
       </code>
       conditions:
       <code class="literal">
        (
        <em class="replaceable">
         <code>
          cond1
         </code>
        </em>
        (
        <em class="replaceable">
         <code>
          key_part1
         </code>
        </em>
        )
                OR
        <em class="replaceable">
         <code>
          cond2
         </code>
        </em>
        (
        <em class="replaceable">
         <code>
          key_part1
         </code>
        </em>
        ))
                AND
                (
        <em class="replaceable">
         <code>
          cond1
         </code>
        </em>
        (
        <em class="replaceable">
         <code>
          key_part2
         </code>
        </em>
        )
                OR ...) AND ...
       </code>
      </p>
     </li>
     <li class="listitem">
      <p>
       There must be a range condition on C.
      </p>
     </li>
     <li class="listitem">
      <p>
       Conditions on D columns are permitted. Conditions on D
                must be in conjunction with the range condition on C.
      </p>
     </li>
    </ul>
   </div>
   <p>
    Use of Skip Scan is indicated in
    <code class="literal">
     EXPLAIN
    </code>
    output as follows:
   </p>
   <div class="itemizedlist">
    <ul class="itemizedlist" style="list-style-type: disc; ">
     <li class="listitem">
      <p>
       <code class="literal">
        Using index for skip scan
       </code>
       in the
       <code class="literal">
        Extra
       </code>
       column indicates that the loose
                index Skip Scan access method is used.
      </p>
     </li>
     <li class="listitem">
      <p>
       If the index can be used for Skip Scan, the index should
                be visible in the
       <code class="literal">
        possible_keys
       </code>
       column.
      </p>
     </li>
    </ul>
   </div>
   <p>
    Use of Skip Scan is indicated in optimizer trace output by a
    <code class="literal">
     "skip scan"
    </code>
    element of this form:
   </p>
   <div class="copytoclipboard-wrapper">
    <pre class="programlisting language-json"><div class="docs-select-all right" id="sa69029272"><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-json"><span class="token property">"skip_scan_range"</span><span class="token operator">:</span> <span class="token punctuation">{</span>
  <span class="token property">"type"</span><span class="token operator">:</span> <span class="token string">"skip_scan"</span><span class="token punctuation">,</span>
  <span class="token property">"index"</span><span class="token operator">:</span> <em class="replaceable">index_used_for_skip_scan</em><span class="token punctuation">,</span>
  <span class="token property">"key_parts_used_for_access"</span><span class="token operator">:</span> <span class="token punctuation">[</span><em class="replaceable">key_parts_used_for_access</em><span class="token punctuation">]</span><span class="token punctuation">,</span>
  <span class="token property">"range"</span><span class="token operator">:</span> <span class="token punctuation">[</span><em class="replaceable">range</em><span class="token punctuation">]</span>
<span class="token punctuation">}</span></code></pre>
   </div>
   <p>
    You may also see a
    <code class="literal">
     "best_skip_scan_summary"
    </code>
    element. If Skip
            Scan is chosen as the best range access variant, a
    <code class="literal">
     "chosen_range_access_summary"
    </code>
    is written.
            If Skip Scan is chosen as the overall best access method, a
    <code class="literal">
     "best_access_path"
    </code>
    element is present.
   </p>
   <p>
    Use of Skip Scan is subject to the value of the
    <a class="link" href="switchable-optimizations.html#optflag_skip-scan">
     <code class="literal">
      skip_scan
     </code>
    </a>
    flag of the
    <a class="link" href="server-system-variables.html#sysvar_optimizer_switch">
     <code class="literal">
      optimizer_switch
     </code>
    </a>
    system
            variable. See
    <a class="xref" href="switchable-optimizations.html" title="10.9.2 Switchable Optimizations">
     Section 10.9.2, “Switchable Optimizations”
    </a>
    . By
            default, this flag is
    <code class="literal">
     on
    </code>
    . To disable it,
            set
    <a class="link" href="switchable-optimizations.html#optflag_skip-scan">
     <code class="literal">
      skip_scan
     </code>
    </a>
    to
    <code class="literal">
     off
    </code>
    .
   </p>
   <p>
    In addition to using the
    <a class="link" href="server-system-variables.html#sysvar_optimizer_switch">
     <code class="literal">
      optimizer_switch
     </code>
    </a>
    system
            variable to control optimizer use of Skip Scan session-wide,
            MySQL supports optimizer hints to influence the optimizer on
            a per-statement basis. See
    <a class="xref" href="optimizer-hints.html" title="10.9.3 Optimizer Hints">
     Section 10.9.3, “Optimizer Hints”
    </a>
    .
   </p>
  </div>
  <div class="simplesect">
   <div class="titlepage">
    <div>
     <div class="simple">
      <h5 class="title">
       <a name="row-constructor-range-optimization">
       </a>
       Range Optimization of Row Constructor Expressions
      </h5>
     </div>
    </div>
   </div>
   <p>
    The optimizer is able to apply the range scan access method
            to queries of this form:
   </p>
   <div class="copytoclipboard-wrapper">
    <pre class="programlisting one-line language-sql"><div class="docs-select-all right" id="sa82555772"><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> t1 <span class="token keyword">WHERE</span> <span class="token punctuation">(</span> col_1<span class="token punctuation">,</span> col_2 <span class="token punctuation">)</span> <span class="token keyword">IN</span> <span class="token punctuation">(</span><span class="token punctuation">(</span> <span class="token string">'a'</span><span class="token punctuation">,</span> <span class="token string">'b'</span> <span class="token punctuation">)</span><span class="token punctuation">,</span> <span class="token punctuation">(</span> <span class="token string">'c'</span><span class="token punctuation">,</span> <span class="token string">'d'</span> <span class="token punctuation">)</span><span class="token punctuation">)</span><span class="token punctuation">;</span></code></pre>
   </div>
   <p>
    Previously, for range scans to be used, it was necessary to
            write the query as:
   </p>
   <div class="copytoclipboard-wrapper">
    <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa11619344"><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> t1 <span class="token keyword">WHERE</span> <span class="token punctuation">(</span> col_1 <span class="token operator">=</span> <span class="token string">'a'</span> <span class="token operator">AND</span> col_2 <span class="token operator">=</span> <span class="token string">'b'</span> <span class="token punctuation">)</span>
<span class="token operator">OR</span> <span class="token punctuation">(</span> col_1 <span class="token operator">=</span> <span class="token string">'c'</span> <span class="token operator">AND</span> col_2 <span class="token operator">=</span> <span class="token string">'d'</span> <span class="token punctuation">)</span><span class="token punctuation">;</span></code></pre>
   </div>
   <p>
    For the optimizer to use a range scan, queries must satisfy
            these conditions:
   </p>
   <div class="itemizedlist">
    <ul class="itemizedlist" style="list-style-type: disc; ">
     <li class="listitem">
      <p>
       Only
       <a class="link" href="comparison-operators.html#operator_in">
        <code class="literal">
         IN()
        </code>
       </a>
       predicates are
                used, not
       <a class="link" href="comparison-operators.html#operator_not-in">
        <code class="literal">
         NOT IN()
        </code>
       </a>
       .
      </p>
     </li>
     <li class="listitem">
      <p>
       On the left side of the
       <a class="link" href="comparison-operators.html#operator_in">
        <code class="literal">
         IN()
        </code>
       </a>
       predicate, the row
                constructor contains only column references.
      </p>
     </li>
     <li class="listitem">
      <p>
       On the right side of the
       <a class="link" href="comparison-operators.html#operator_in">
        <code class="literal">
         IN()
        </code>
       </a>
       predicate, row
                constructors contain only runtime constants, which are
                either literals or local column references that are
                bound to constants during execution.
      </p>
     </li>
     <li class="listitem">
      <p>
       On the right side of the
       <a class="link" href="comparison-operators.html#operator_in">
        <code class="literal">
         IN()
        </code>
       </a>
       predicate, there is
                more than one row constructor.
      </p>
     </li>
    </ul>
   </div>
   <p>
    For more information about the optimizer and row
            constructors, see
    <a class="xref" href="row-constructor-optimization.html" title="10.2.1.22 Row Constructor Expression Optimization">
     Section 10.2.1.22, “Row Constructor Expression Optimization”
    </a>
   </p>
  </div>
  <div class="simplesect">
   <div class="titlepage">
    <div>
     <div class="simple">
      <h5 class="title">
       <a name="range-optimization-memory-use">
       </a>
       Limiting Memory Use for Range Optimization
      </h5>
     </div>
    </div>
   </div>
   <p>
    To control the memory available to the range optimizer, use
            the
    <a class="link" href="server-system-variables.html#sysvar_range_optimizer_max_mem_size">
     <code class="literal">
      range_optimizer_max_mem_size
     </code>
    </a>
    system variable:
   </p>
   <div class="itemizedlist">
    <ul class="itemizedlist" style="list-style-type: disc; ">
     <li class="listitem">
      <p>
       A value of 0 means
       <span class="quote">
        “
        <span class="quote">
         no limit.
        </span>
        ”
       </span>
      </p>
     </li>
     <li class="listitem">
      <p>
       With a value greater than 0, the optimizer tracks the
                memory consumed when considering the range access
                method. If the specified limit is about to be exceeded,
                the range access method is abandoned and other methods,
                including a full table scan, are considered instead.
                This could be less optimal. If this happens, the
                following warning occurs (where
       <em class="replaceable">
        <code>
         N
        </code>
       </em>
       is the current
       <a class="link" href="server-system-variables.html#sysvar_range_optimizer_max_mem_size">
        <code class="literal">
         range_optimizer_max_mem_size
        </code>
       </a>
       value):
      </p>
      <div class="copytoclipboard-wrapper">
       <pre class="programlisting language-none"><div class="docs-select-all right" id="sa93875803"><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-none">Warning    3170    Memory capacity of <em class="replaceable">N</em> bytes for
                   'range_optimizer_max_mem_size' exceeded. Range
                   optimization was not done for this query.</code></pre>
      </div>
     </li>
     <li class="listitem">
      <p>
       For
       <a class="link" href="update.html" title="15.2.17 UPDATE Statement">
        <code class="literal">
         UPDATE
        </code>
       </a>
       and
       <a class="link" href="delete.html" title="15.2.2 DELETE Statement">
        <code class="literal">
         DELETE
        </code>
       </a>
       statements, if the
                optimizer falls back to a full table scan and the
       <a class="link" href="server-system-variables.html#sysvar_sql_safe_updates">
        <code class="literal">
         sql_safe_updates
        </code>
       </a>
       system
                variable is enabled, an error occurs rather than a
                warning because, in effect, no key is used to determine
                which rows to modify. For more information, see
       <a class="xref" href="mysql-tips.html#safe-updates" title="Using Safe-Updates Mode (--safe-updates)">
        Using Safe-Updates Mode (--safe-updates)
       </a>
       .
      </p>
     </li>
    </ul>
   </div>
   <p>
    For individual queries that exceed the available range
            optimization memory and for which the optimizer falls back
            to less optimal plans, increasing the
    <a class="link" href="server-system-variables.html#sysvar_range_optimizer_max_mem_size">
     <code class="literal">
      range_optimizer_max_mem_size
     </code>
    </a>
    value may improve performance.
   </p>
   <p>
    To estimate the amount of memory needed to process a range
            expression, use these guidelines:
   </p>
   <div class="itemizedlist">
    <ul class="itemizedlist" style="list-style-type: disc; ">
     <li class="listitem">
      <p>
       For a simple query such as the following, where there is
                one candidate key for the range access method, each
                predicate combined with
       <a class="link" href="logical-operators.html#operator_or">
        <code class="literal">
         OR
        </code>
       </a>
       uses approximately 230 bytes:
      </p>
      <div class="copytoclipboard-wrapper">
       <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa89476646"><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> t
<span class="token keyword">WHERE</span> a<span class="token operator">=</span><span class="token number">1</span> <span class="token operator">OR</span> a<span class="token operator">=</span><span class="token number">2</span> <span class="token operator">OR</span> a<span class="token operator">=</span><span class="token number">3</span> <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">=</span><em class="replaceable">N</em><span class="token punctuation">;</span></code></pre>
      </div>
     </li>
     <li class="listitem">
      <p>
       Similarly for a query such as the following, each
                predicate combined with
       <a class="link" href="logical-operators.html#operator_and">
        <code class="literal">
         AND
        </code>
       </a>
       uses approximately 125 bytes:
      </p>
      <div class="copytoclipboard-wrapper">
       <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa40961308"><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> t
<span class="token keyword">WHERE</span> a<span class="token operator">=</span><span class="token number">1</span> <span class="token operator">AND</span> b<span class="token operator">=</span><span class="token number">1</span> <span class="token operator">AND</span> c<span class="token operator">=</span><span class="token number">1</span> <span class="token punctuation">.</span><span class="token punctuation">.</span><span class="token punctuation">.</span> <em class="replaceable">N</em><span class="token punctuation">;</span></code></pre>
      </div>
     </li>
     <li class="listitem">
      <p>
       For a query with
       <a class="link" href="comparison-operators.html#operator_in">
        <code class="literal">
         IN()
        </code>
       </a>
       predicates:
      </p>
      <div class="copytoclipboard-wrapper">
       <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa64948275"><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> t
<span class="token keyword">WHERE</span> a <span class="token keyword">IN</span> <span class="token punctuation">(</span><span class="token number">1</span><span class="token punctuation">,</span><span class="token number">2</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> <em class="replaceable">M</em><span class="token punctuation">)</span> <span class="token operator">AND</span> b <span class="token keyword">IN</span> <span class="token punctuation">(</span><span class="token number">1</span><span class="token punctuation">,</span><span class="token number">2</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> <em class="replaceable">N</em><span class="token punctuation">)</span><span class="token punctuation">;</span></code></pre>
      </div>
      <p>
       Each literal value in an
       <a class="link" href="comparison-operators.html#operator_in">
        <code class="literal">
         IN()
        </code>
       </a>
       list counts as a
                predicate combined with
       <a class="link" href="logical-operators.html#operator_or">
        <code class="literal">
         OR
        </code>
       </a>
       .
                If there are two
       <a class="link" href="comparison-operators.html#operator_in">
        <code class="literal">
         IN()
        </code>
       </a>
       lists, the number of predicates combined with
       <a class="link" href="logical-operators.html#operator_or">
        <code class="literal">
         OR
        </code>
       </a>
       is the product of the
                number of literal values in each list. Thus, the number
                of predicates combined with
       <a class="link" href="logical-operators.html#operator_or">
        <code class="literal">
         OR
        </code>
       </a>
       in the preceding case is
       <em class="replaceable">
        <code>
         M
        </code>
       </em>
       ×
       <em class="replaceable">
        <code>
         N
        </code>
       </em>
       .
      </p>
     </li>
    </ul>
   </div>
  </div>
 </div>
 <br/>
</div>
