<div id="docs-body">
 <div class="section">
  <div class="titlepage">
   <div>
    <div>
     <h4 class="title">
      <a name="index-condition-pushdown-optimization">
      </a>
      10.2.1.6 Index Condition Pushdown Optimization
     </h4>
    </div>
   </div>
  </div>
  <p>
   Index Condition Pushdown (ICP) is an optimization for the case
          where MySQL retrieves rows from a table using an index.
          Without ICP, the storage engine traverses the index to locate
          rows in the base table and returns them to the MySQL server
          which evaluates the
   <code class="literal">
    WHERE
   </code>
   condition for the
          rows. With ICP enabled, and if parts of the
   <code class="literal">
    WHERE
   </code>
   condition can be evaluated by using
          only columns from the index, the MySQL server pushes this part
          of the
   <code class="literal">
    WHERE
   </code>
   condition down to the storage
          engine. The storage engine then evaluates the pushed index
          condition by using the index entry and only if this is
          satisfied is the row read from the table. ICP can reduce the
          number of times the storage engine must access the base table
          and the number of times the MySQL server must access the
          storage engine.
  </p>
  <p>
   Applicability of the Index Condition Pushdown optimization is
          subject to these conditions:
  </p>
  <div class="itemizedlist">
   <ul class="itemizedlist" style="list-style-type: disc; ">
    <li class="listitem">
     <p>
      ICP is used for the
      <a class="link" href="explain-output.html#jointype_range">
       <code class="literal">
        range
       </code>
      </a>
      ,
      <a class="link" href="explain-output.html#jointype_ref">
       <code class="literal">
        ref
       </code>
      </a>
      ,
      <a class="link" href="explain-output.html#jointype_eq_ref">
       <code class="literal">
        eq_ref
       </code>
      </a>
      , and
      <a class="link" href="explain-output.html#jointype_ref_or_null">
       <code class="literal">
        ref_or_null
       </code>
      </a>
      access
              methods when there is a need to access full table rows.
     </p>
    </li>
    <li class="listitem">
     <p>
      ICP can be used for
      <a class="link" href="innodb-storage-engine.html" title="Chapter 17 The InnoDB Storage Engine">
       <code class="literal">
        InnoDB
       </code>
      </a>
      and
      <a class="link" href="myisam-storage-engine.html" title="18.2 The MyISAM Storage Engine">
       <code class="literal">
        MyISAM
       </code>
      </a>
      tables, including
              partitioned
      <code class="literal">
       InnoDB
      </code>
      and
      <code class="literal">
       MyISAM
      </code>
      tables.
     </p>
    </li>
    <li class="listitem">
     <p>
      For
      <code class="literal">
       InnoDB
      </code>
      tables, ICP is used only for
              secondary indexes. The goal of ICP is to reduce the number
              of full-row reads and thereby reduce I/O operations. For
      <code class="literal">
       InnoDB
      </code>
      clustered indexes, the complete
              record is already read into the
      <code class="literal">
       InnoDB
      </code>
      buffer. Using ICP in this case does not reduce I/O.
     </p>
    </li>
    <li class="listitem">
     <p>
      ICP is not supported with secondary indexes created on
              virtual generated columns.
      <code class="literal">
       InnoDB
      </code>
      supports secondary indexes on virtual generated columns.
     </p>
    </li>
    <li class="listitem">
     <p>
      Conditions that refer to subqueries cannot be pushed down.
     </p>
    </li>
    <li class="listitem">
     <p>
      Conditions that refer to stored functions cannot be pushed
              down. Storage engines cannot invoke stored functions.
     </p>
    </li>
    <li class="listitem">
     <p>
      Triggered conditions cannot be pushed down. (For
              information about triggered conditions, see
      <a class="xref" href="subquery-optimization-with-exists.html" title="10.2.2.3 Optimizing Subqueries with the EXISTS Strategy">
       Section 10.2.2.3, “Optimizing Subqueries with the EXISTS Strategy”
      </a>
      .)
     </p>
    </li>
    <li class="listitem">
     <p>
      Conditions cannot be pushed down to derived tables
              containing references to system variables.
     </p>
    </li>
   </ul>
  </div>
  <p>
   To understand how this optimization works, first consider how
          an index scan proceeds when Index Condition Pushdown is not
          used:
  </p>
  <div class="orderedlist">
   <ol class="orderedlist" type="1">
    <li class="listitem">
     <p>
      Get the next row, first by reading the index tuple, and
              then by using the index tuple to locate and read the full
              table row.
     </p>
    </li>
    <li class="listitem">
     <p>
      Test the part of the
      <code class="literal">
       WHERE
      </code>
      condition
              that applies to this table. Accept or reject the row based
              on the test result.
     </p>
    </li>
   </ol>
  </div>
  <p>
   Using Index Condition Pushdown, the scan proceeds like this
          instead:
  </p>
  <div class="orderedlist">
   <ol class="orderedlist" type="1">
    <li class="listitem">
     <p>
      Get the next row's index tuple (but not the full
              table row).
     </p>
    </li>
    <li class="listitem">
     <p>
      Test the part of the
      <code class="literal">
       WHERE
      </code>
      condition
              that applies to this table and can be checked using only
              index columns. If the condition is not satisfied, proceed
              to the index tuple for the next row.
     </p>
    </li>
    <li class="listitem">
     <p>
      If the condition is satisfied, use the index tuple to
              locate and read the full table row.
     </p>
    </li>
    <li class="listitem">
     <p>
      Test the remaining part of the
      <code class="literal">
       WHERE
      </code>
      condition that applies to this table. Accept or reject the
              row based on the test result.
     </p>
    </li>
   </ol>
  </div>
  <p>
   <a class="link" href="explain.html" title="15.8.2 EXPLAIN Statement">
    <code class="literal">
     EXPLAIN
    </code>
   </a>
   output shows
   <code class="literal">
    Using index condition
   </code>
   in the
   <code class="literal">
    Extra
   </code>
   column when Index Condition Pushdown
          is used. It does not show
   <code class="literal">
    Using index
   </code>
   because that does not apply when full table rows must be read.
  </p>
  <p>
   Suppose that a table contains information about people and
          their addresses and that the table has an index defined as
   <code class="literal">
    INDEX (zipcode, lastname, firstname)
   </code>
   . If we
          know a person's
   <code class="literal">
    zipcode
   </code>
   value but are
          not sure about the last name, we can search like this:
  </p>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa69054830"><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> people
  <span class="token keyword">WHERE</span> zipcode<span class="token operator">=</span><span class="token string">'95054'</span>
  <span class="token operator">AND</span> lastname <span class="token operator">LIKE</span> <span class="token string">'%etrunia%'</span>
  <span class="token operator">AND</span> address <span class="token operator">LIKE</span> <span class="token string">'%Main Street%'</span><span class="token punctuation">;</span></code></pre>
  </div>
  <p>
   MySQL can use the index to scan through people with
   <code class="literal">
    zipcode='95054'
   </code>
   . The second part
          (
   <code class="literal">
    lastname LIKE '%etrunia%'
   </code>
   ) cannot be used
          to limit the number of rows that must be scanned, so without
          Index Condition Pushdown, this query must retrieve full table
          rows for all people who have
   <code class="literal">
    zipcode='95054'
   </code>
   .
  </p>
  <p>
   With Index Condition Pushdown, MySQL checks the
   <code class="literal">
    lastname LIKE '%etrunia%'
   </code>
   part before
          reading the full table row. This avoids reading full rows
          corresponding to index tuples that match the
   <code class="literal">
    zipcode
   </code>
   condition but not the
   <code class="literal">
    lastname
   </code>
   condition.
  </p>
  <p>
   Index Condition Pushdown is enabled by default. It can be
          controlled with the
   <a class="link" href="server-system-variables.html#sysvar_optimizer_switch">
    <code class="literal">
     optimizer_switch
    </code>
   </a>
   system
          variable by setting the
   <a class="link" href="switchable-optimizations.html#optflag_index-condition-pushdown">
    <code class="literal">
     index_condition_pushdown
    </code>
   </a>
   flag:
  </p>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa44274381"><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">SET</span> optimizer_switch <span class="token operator">=</span> <span class="token string">'index_condition_pushdown=off'</span><span class="token punctuation">;</span>
<span class="token keyword">SET</span> optimizer_switch <span class="token operator">=</span> <span class="token string">'index_condition_pushdown=on'</span><span class="token punctuation">;</span></code></pre>
  </div>
  <p>
   See
   <a class="xref" href="switchable-optimizations.html" title="10.9.2 Switchable Optimizations">
    Section 10.9.2, “Switchable Optimizations”
   </a>
   .
  </p>
 </div>
 <br/>
</div>
