<div id="docs-body">
 <div class="section">
  <div class="titlepage">
   <div>
    <div>
     <h4 class="title">
      <a name="index-merge-optimization">
      </a>
      10.2.1.3 Index Merge Optimization
     </h4>
    </div>
   </div>
  </div>
  <a class="indexterm" name="idm46045229892064">
  </a>
  <a class="indexterm" name="idm46045229891024">
  </a>
  <a class="indexterm" name="idm46045229889936">
  </a>
  <p>
   The
   <span class="firstterm">
    Index Merge
   </span>
   access
          method retrieves rows with multiple
   <a class="link" href="explain-output.html#jointype_range">
    <code class="literal">
     range
    </code>
   </a>
   scans and merges
          their results into one. This access method merges index scans
          from a single table only, not scans across multiple tables.
          The merge can produce unions, intersections, or
          unions-of-intersections of its underlying scans.
  </p>
  <p>
   Example queries for which Index Merge may be used:
  </p>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa3684630"><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> <em class="replaceable">tbl_name</em> <span class="token keyword">WHERE</span> <em class="replaceable">key1</em> <span class="token operator">=</span> <span class="token number">10</span> <span class="token operator">OR</span> <em class="replaceable">key2</em> <span class="token operator">=</span> <span class="token number">20</span><span class="token punctuation">;</span>

<span class="token keyword">SELECT</span> <span class="token operator">*</span> <span class="token keyword">FROM</span> <em class="replaceable">tbl_name</em>
  <span class="token keyword">WHERE</span> <span class="token punctuation">(</span><em class="replaceable">key1</em> <span class="token operator">=</span> <span class="token number">10</span> <span class="token operator">OR</span> <em class="replaceable">key2</em> <span class="token operator">=</span> <span class="token number">20</span><span class="token punctuation">)</span> <span class="token operator">AND</span> <em class="replaceable">non_key</em> <span class="token operator">=</span> <span class="token number">30</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> <span class="token punctuation">(</span>t1<span class="token punctuation">.</span><em class="replaceable">key1</em> <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 operator">OR</span> t1<span class="token punctuation">.</span><em class="replaceable">key2</em> <span class="token operator">LIKE</span> <span class="token string">'<em class="replaceable">value</em>%'</span><span class="token punctuation">)</span>
  <span class="token operator">AND</span> t2<span class="token punctuation">.</span><em class="replaceable">key1</em> <span class="token operator">=</span> t1<span class="token punctuation">.</span><em class="replaceable">some_col</em><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">key1</em> <span class="token operator">=</span> <span class="token number">1</span>
  <span class="token operator">AND</span> <span class="token punctuation">(</span>t2<span class="token punctuation">.</span><em class="replaceable">key1</em> <span class="token operator">=</span> t1<span class="token punctuation">.</span><em class="replaceable">some_col</em> <span class="token operator">OR</span> t2<span class="token punctuation">.</span><em class="replaceable">key2</em> <span class="token operator">=</span> t1<span class="token punctuation">.</span><em class="replaceable">some_col2</em><span class="token punctuation">)</span><span class="token punctuation">;</span></code></pre>
  </div>
  <div class="note" style="margin-left: 0.5in; margin-right: 0.5in;">
   <div class="admon-title">
    Note
   </div>
   <p>
    The Index Merge optimization algorithm has the following
            known limitations:
   </p>
   <div class="itemizedlist">
    <ul class="itemizedlist" style="list-style-type: disc; ">
     <li class="listitem">
      <p>
       If your query has a complex
       <code class="literal">
        WHERE
       </code>
       clause with deep
       <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>
       nesting and MySQL does not choose the optimal plan, try
                distributing terms using the following identity
                transformations:
      </p>
      <div class="copytoclipboard-wrapper">
       <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa73763025"><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">x</em> <span class="token operator">AND</span> <em class="replaceable">y</em><span class="token punctuation">)</span> <span class="token operator">OR</span> <em class="replaceable">z</em> <span class="token operator">=</span><span class="token operator">&gt;</span> <span class="token punctuation">(</span><em class="replaceable">x</em> <span class="token operator">OR</span> <em class="replaceable">z</em><span class="token punctuation">)</span> <span class="token operator">AND</span> <span class="token punctuation">(</span><em class="replaceable">y</em> <span class="token operator">OR</span> <em class="replaceable">z</em><span class="token punctuation">)</span>
<span class="token punctuation">(</span><em class="replaceable">x</em> <span class="token operator">OR</span> <em class="replaceable">y</em><span class="token punctuation">)</span> <span class="token operator">AND</span> <em class="replaceable">z</em> <span class="token operator">=</span><span class="token operator">&gt;</span> <span class="token punctuation">(</span><em class="replaceable">x</em> <span class="token operator">AND</span> <em class="replaceable">z</em><span class="token punctuation">)</span> <span class="token operator">OR</span> <span class="token punctuation">(</span><em class="replaceable">y</em> <span class="token operator">AND</span> <em class="replaceable">z</em><span class="token punctuation">)</span></code></pre>
      </div>
     </li>
     <li class="listitem">
      <p>
       Index Merge is not applicable to full-text indexes.
      </p>
     </li>
    </ul>
   </div>
  </div>
  <p>
   In
   <a class="link" href="explain.html" title="15.8.2 EXPLAIN Statement">
    <code class="literal">
     EXPLAIN
    </code>
   </a>
   output, the Index
          Merge method appears as
   <a class="link" href="explain-output.html#jointype_index_merge">
    <code class="literal">
     index_merge
    </code>
   </a>
   in the
   <code class="literal">
    type
   </code>
   column. In this case, the
   <code class="literal">
    key
   </code>
   column contains a list of indexes used,
          and
   <code class="literal">
    key_len
   </code>
   contains a list of the longest
          key parts for those indexes.
  </p>
  <p>
   The Index Merge access method has several algorithms, which
          are displayed in the
   <code class="literal">
    Extra
   </code>
   field of
   <a class="link" href="explain.html" title="15.8.2 EXPLAIN Statement">
    <code class="literal">
     EXPLAIN
    </code>
   </a>
   output:
  </p>
  <div class="itemizedlist">
   <ul class="itemizedlist" style="list-style-type: disc; ">
    <li class="listitem">
     <p>
      <code class="literal">
       Using intersect(...)
      </code>
     </p>
    </li>
    <li class="listitem">
     <p>
      <code class="literal">
       Using union(...)
      </code>
     </p>
    </li>
    <li class="listitem">
     <p>
      <code class="literal">
       Using sort_union(...)
      </code>
     </p>
    </li>
   </ul>
  </div>
  <p>
   The following sections describe these algorithms in greater
          detail. The optimizer chooses between different possible Index
          Merge algorithms and other access methods based on cost
          estimates of the various available options.
  </p>
  <div class="itemizedlist">
   <ul class="itemizedlist" style="list-style-type: disc; ">
    <li class="listitem">
     <p>
      <a class="xref" href="index-merge-optimization.html#index-merge-intersection" title="Index Merge Intersection Access Algorithm">
       Index Merge Intersection Access Algorithm
      </a>
     </p>
    </li>
    <li class="listitem">
     <p>
      <a class="xref" href="index-merge-optimization.html#index-merge-union" title="Index Merge Union Access Algorithm">
       Index Merge Union Access Algorithm
      </a>
     </p>
    </li>
    <li class="listitem">
     <p>
      <a class="xref" href="index-merge-optimization.html#index-merge-sort-union" title="Index Merge Sort-Union Access Algorithm">
       Index Merge Sort-Union Access Algorithm
      </a>
     </p>
    </li>
    <li class="listitem">
     <p>
      <a class="xref" href="index-merge-optimization.html#index-merge-influencing" title="Influencing Index Merge Optimization">
       Influencing Index Merge Optimization
      </a>
     </p>
    </li>
   </ul>
  </div>
  <div class="simplesect">
   <div class="titlepage">
    <div>
     <div class="simple">
      <h5 class="title">
       <a name="index-merge-intersection">
       </a>
       Index Merge Intersection Access Algorithm
      </h5>
     </div>
    </div>
   </div>
   <p>
    This access algorithm is applicable when a
    <code class="literal">
     WHERE
    </code>
    clause is converted to several
            range conditions on different keys combined with
    <a class="link" href="logical-operators.html#operator_and">
     <code class="literal">
      AND
     </code>
    </a>
    , and each condition is one
            of the following:
   </p>
   <div class="itemizedlist">
    <ul class="itemizedlist" style="list-style-type: disc; ">
     <li class="listitem">
      <p>
       An
       <em class="replaceable">
        <code>
         N
        </code>
       </em>
       -part expression of this
                form, where the index has exactly
       <em class="replaceable">
        <code>
         N
        </code>
       </em>
       parts (that is, all index
                parts are covered):
      </p>
      <div class="copytoclipboard-wrapper">
       <pre class="programlisting one-line language-sql"><div class="docs-select-all right" id="sa29533768"><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> <em class="replaceable">const1</em> <span class="token operator">AND</span> <em class="replaceable">key_part2</em> <span class="token operator">=</span> <em class="replaceable">const2</em> <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> <span class="token operator">=</span> <em class="replaceable">constN</em></code></pre>
      </div>
     </li>
     <li class="listitem">
      <p>
       Any range condition over the primary key of an
       <code class="literal">
        InnoDB
       </code>
       table.
      </p>
     </li>
    </ul>
   </div>
   <p>
    Examples:
   </p>
   <div class="copytoclipboard-wrapper">
    <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa15731858"><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> <em class="replaceable">innodb_table</em>
  <span class="token keyword">WHERE</span> <em class="replaceable">primary_key</em> <span class="token operator">&lt;</span> <span class="token number">10</span> <span class="token operator">AND</span> <em class="replaceable">key_col1</em> <span class="token operator">=</span> <span class="token number">20</span><span class="token punctuation">;</span>

<span class="token keyword">SELECT</span> <span class="token operator">*</span> <span class="token keyword">FROM</span> <em class="replaceable">tbl_name</em>
  <span class="token keyword">WHERE</span> <em class="replaceable">key1_part1</em> <span class="token operator">=</span> <span class="token number">1</span> <span class="token operator">AND</span> <em class="replaceable">key1_part2</em> <span class="token operator">=</span> <span class="token number">2</span> <span class="token operator">AND</span> <em class="replaceable">key2</em> <span class="token operator">=</span> <span class="token number">2</span><span class="token punctuation">;</span></code></pre>
   </div>
   <p>
    The Index Merge intersection algorithm performs simultaneous
            scans on all used indexes and produces the intersection of
            row sequences that it receives from the merged index scans.
   </p>
   <p>
    If all columns used in the query are covered by the used
            indexes, full table rows are not retrieved
            (
    <a class="link" href="explain.html" title="15.8.2 EXPLAIN Statement">
     <code class="literal">
      EXPLAIN
     </code>
    </a>
    output contains
    <code class="literal">
     Using index
    </code>
    in
    <code class="literal">
     Extra
    </code>
    field in this case). Here is an example of such a query:
   </p>
   <div class="copytoclipboard-wrapper">
    <pre class="programlisting one-line language-sql"><div class="docs-select-all right" id="sa45522334"><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> t1 <span class="token keyword">WHERE</span> key1 <span class="token operator">=</span> <span class="token number">1</span> <span class="token operator">AND</span> key2 <span class="token operator">=</span> <span class="token number">1</span><span class="token punctuation">;</span></code></pre>
   </div>
   <p>
    If the used indexes do not cover all columns used in the
            query, full rows are retrieved only when the range
            conditions for all used keys are satisfied.
   </p>
   <p>
    If one of the merged conditions is a condition over the
            primary key of an
    <code class="literal">
     InnoDB
    </code>
    table, it is not
            used for row retrieval, but is used to filter out rows
            retrieved using other conditions.
   </p>
  </div>
  <div class="simplesect">
   <div class="titlepage">
    <div>
     <div class="simple">
      <h5 class="title">
       <a name="index-merge-union">
       </a>
       Index Merge Union Access Algorithm
      </h5>
     </div>
    </div>
   </div>
   <p>
    The criteria for this algorithm are similar to those for the
            Index Merge intersection algorithm. The algorithm is
            applicable when the table's
    <code class="literal">
     WHERE
    </code>
    clause is converted to several range conditions on different
            keys combined with
    <a class="link" href="logical-operators.html#operator_or">
     <code class="literal">
      OR
     </code>
    </a>
    , and each
            condition is one of the following:
   </p>
   <div class="itemizedlist">
    <ul class="itemizedlist" style="list-style-type: disc; ">
     <li class="listitem">
      <p>
       An
       <em class="replaceable">
        <code>
         N
        </code>
       </em>
       -part expression of this
                form, where the index has exactly
       <em class="replaceable">
        <code>
         N
        </code>
       </em>
       parts (that is, all index
                parts are covered):
      </p>
      <div class="copytoclipboard-wrapper">
       <pre class="programlisting one-line language-sql"><div class="docs-select-all right" id="sa38473490"><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> <em class="replaceable">const1</em> <span class="token operator">OR</span> <em class="replaceable">key_part2</em> <span class="token operator">=</span> <em class="replaceable">const2</em> <span class="token punctuation">.</span><span class="token punctuation">.</span><span class="token punctuation">.</span> <span class="token operator">OR</span> <em class="replaceable">key_partN</em> <span class="token operator">=</span> <em class="replaceable">constN</em></code></pre>
      </div>
     </li>
     <li class="listitem">
      <p>
       Any range condition over a primary key of an
       <code class="literal">
        InnoDB
       </code>
       table.
      </p>
     </li>
     <li class="listitem">
      <p>
       A condition for which the Index Merge intersection
                algorithm is applicable.
      </p>
     </li>
    </ul>
   </div>
   <p>
    Examples:
   </p>
   <div class="copytoclipboard-wrapper">
    <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa41940481"><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">key1</em> <span class="token operator">=</span> <span class="token number">1</span> <span class="token operator">OR</span> <em class="replaceable">key2</em> <span class="token operator">=</span> <span class="token number">2</span> <span class="token operator">OR</span> <em class="replaceable">key3</em> <span class="token operator">=</span> <span class="token number">3</span><span class="token punctuation">;</span>

<span class="token keyword">SELECT</span> <span class="token operator">*</span> <span class="token keyword">FROM</span> <em class="replaceable">innodb_table</em>
  <span class="token keyword">WHERE</span> <span class="token punctuation">(</span><em class="replaceable">key1</em> <span class="token operator">=</span> <span class="token number">1</span> <span class="token operator">AND</span> <em class="replaceable">key2</em> <span class="token operator">=</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">key3</em> <span class="token operator">=</span> <span class="token string">'foo'</span> <span class="token operator">AND</span> <em class="replaceable">key4</em> <span class="token operator">=</span> <span class="token string">'bar'</span><span class="token punctuation">)</span> <span class="token operator">AND</span> <em class="replaceable">key5</em> <span class="token operator">=</span> <span class="token number">5</span><span class="token punctuation">;</span></code></pre>
   </div>
  </div>
  <div class="simplesect">
   <div class="titlepage">
    <div>
     <div class="simple">
      <h5 class="title">
       <a name="index-merge-sort-union">
       </a>
       Index Merge Sort-Union Access Algorithm
      </h5>
     </div>
    </div>
   </div>
   <p>
    This access algorithm is applicable when the
    <code class="literal">
     WHERE
    </code>
    clause is converted to several
            range conditions combined by
    <a class="link" href="logical-operators.html#operator_or">
     <code class="literal">
      OR
     </code>
    </a>
    , but the Index Merge union
            algorithm is not applicable.
   </p>
   <p>
    Examples:
   </p>
   <div class="copytoclipboard-wrapper">
    <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa29749457"><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> <em class="replaceable">tbl_name</em>
  <span class="token keyword">WHERE</span> <em class="replaceable">key_col1</em> <span class="token operator">&lt;</span> <span class="token number">10</span> <span class="token operator">OR</span> <em class="replaceable">key_col2</em> <span class="token operator">&lt;</span> <span class="token number">20</span><span class="token punctuation">;</span>

<span class="token keyword">SELECT</span> <span class="token operator">*</span> <span class="token keyword">FROM</span> <em class="replaceable">tbl_name</em>
  <span class="token keyword">WHERE</span> <span class="token punctuation">(</span><em class="replaceable">key_col1</em> <span class="token operator">&gt;</span> <span class="token number">10</span> <span class="token operator">OR</span> <em class="replaceable">key_col2</em> <span class="token operator">=</span> <span class="token number">20</span><span class="token punctuation">)</span> <span class="token operator">AND</span> <em class="replaceable">nonkey_col</em> <span class="token operator">=</span> <span class="token number">30</span><span class="token punctuation">;</span></code></pre>
   </div>
   <p>
    The difference between the sort-union algorithm and the
            union algorithm is that the sort-union algorithm must first
            fetch row IDs for all rows and sort them before returning
            any rows.
   </p>
  </div>
  <div class="simplesect">
   <div class="titlepage">
    <div>
     <div class="simple">
      <h5 class="title">
       <a name="index-merge-influencing">
       </a>
       Influencing Index Merge Optimization
      </h5>
     </div>
    </div>
   </div>
   <p>
    Use of Index Merge is subject to the value of the
    <a class="link" href="switchable-optimizations.html#optflag_index-merge">
     <code class="literal">
      index_merge
     </code>
    </a>
    ,
    <a class="link" href="switchable-optimizations.html#optflag_index-merge-intersection">
     <code class="literal">
      index_merge_intersection
     </code>
    </a>
    ,
    <a class="link" href="switchable-optimizations.html#optflag_index-merge-union">
     <code class="literal">
      index_merge_union
     </code>
    </a>
    , and
    <a class="link" href="switchable-optimizations.html#optflag_index-merge-sort-union">
     <code class="literal">
      index_merge_sort_union
     </code>
    </a>
    flags 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, all those flags are
    <code class="literal">
     on
    </code>
    . To
            enable only certain algorithms, set
    <a class="link" href="switchable-optimizations.html#optflag_index-merge">
     <code class="literal">
      index_merge
     </code>
    </a>
    to
    <code class="literal">
     off
    </code>
    , and enable only such of the others
            as should be permitted.
   </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 the Index Merge
            algorithms 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>
 <br/>
</div>
