<div id="docs-body">
 <div class="section">
  <div class="titlepage">
   <div>
    <div>
     <h4 class="title">
      <a name="engine-condition-pushdown-optimization">
      </a>
      10.2.1.5 Engine Condition Pushdown Optimization
     </h4>
    </div>
   </div>
  </div>
  <a class="indexterm" name="idm46045229708800">
  </a>
  <a class="indexterm" name="idm46045229707712">
  </a>
  <p>
   This optimization improves the efficiency of direct
          comparisons between a nonindexed column and a constant. In
          such cases, the condition is
   <span class="quote">
    “
    <span class="quote">
     pushed down
    </span>
    ”
   </span>
   to the
          storage engine for evaluation. This optimization can be used
          only by the
   <a class="link" href="mysql-cluster.html" title="Chapter 25 MySQL NDB Cluster 8.4">
    <code class="literal">
     NDB
    </code>
   </a>
   storage engine.
  </p>
  <p>
   For NDB Cluster, this optimization can eliminate the need to
          send nonmatching rows over the network between the
          cluster's data nodes and the MySQL server that issued the
          query, and can speed up queries where it is used by a factor
          of 5 to 10 times over cases where condition pushdown could be
          but is not used.
  </p>
  <p>
   Suppose that an NDB Cluster table is defined as follows:
  </p>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa11910947"><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>
    a <span class="token datatype">INT</span><span class="token punctuation">,</span>
    b <span class="token datatype">INT</span><span class="token punctuation">,</span>
    <span class="token keyword">KEY</span><span class="token punctuation">(</span>a<span class="token punctuation">)</span>
<span class="token punctuation">)</span> <span class="token keyword">ENGINE</span><span class="token operator">=</span><span class="token keyword">NDB</span><span class="token punctuation">;</span></code></pre>
  </div>
  <p>
   Engine condition pushdown can be used with queries such as the
          one shown here, which includes a comparison between a
          nonindexed column and a constant:
  </p>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting one-line language-sql"><div class="docs-select-all right" id="sa71460055"><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> a<span class="token punctuation">,</span> b <span class="token keyword">FROM</span> t1 <span class="token keyword">WHERE</span> b <span class="token operator">=</span> <span class="token number">10</span><span class="token punctuation">;</span></code></pre>
  </div>
  <p>
   The use of engine condition pushdown can be seen in the output
          of
   <a class="link" href="explain.html" title="15.8.2 EXPLAIN Statement">
    <code class="literal">
     EXPLAIN
    </code>
   </a>
   :
  </p>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa14438267"><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 prompt">mysql&gt;</span> <span class="token keyword">EXPLAIN</span> <span class="token keyword">SELECT</span> a<span class="token punctuation">,</span> b <span class="token keyword">FROM</span> t1 <span class="token keyword">WHERE</span> b <span class="token operator">=</span> <span class="token number">10</span>\G
<span class="token output"><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span> 1. row <span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span>
           id<span class="token punctuation">:</span> 1
  select_type<span class="token punctuation">:</span> SIMPLE
        table<span class="token punctuation">:</span> t1
         type<span class="token punctuation">:</span> ALL
possible_keys<span class="token punctuation">:</span> NULL
          key<span class="token punctuation">:</span> NULL
      key_len<span class="token punctuation">:</span> NULL
          ref<span class="token punctuation">:</span> NULL
         rows<span class="token punctuation">:</span> 10
        Extra<span class="token punctuation">:</span> Using where with pushed condition</span></code></pre>
  </div>
  <p>
   However, engine condition pushdown
   <span class="emphasis">
    <em>
     cannot
    </em>
   </span>
   be used with the following query:
  </p>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting one-line language-sql"><div class="docs-select-all right" id="sa97086757"><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> a<span class="token punctuation">,</span>b <span class="token keyword">FROM</span> t1 <span class="token keyword">WHERE</span> a <span class="token operator">=</span> <span class="token number">10</span><span class="token punctuation">;</span></code></pre>
  </div>
  <p>
   Engine condition pushdown is not applicable here because an
          index exists on column
   <code class="literal">
    a
   </code>
   . (An index access
          method would be more efficient and so would be chosen in
          preference to condition pushdown.)
  </p>
  <p>
   Engine condition pushdown may also be employed when an indexed
          column is compared with a constant using a
   <code class="literal">
    &gt;
   </code>
   or
   <code class="literal">
    &lt;
   </code>
   operator:
  </p>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa60798542"><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 prompt">mysql&gt;</span> <span class="token keyword">EXPLAIN</span> <span class="token keyword">SELECT</span> a<span class="token punctuation">,</span> b <span class="token keyword">FROM</span> t1 <span class="token keyword">WHERE</span> a <span class="token operator">&lt;</span> <span class="token number">2</span>\G
<span class="token output"><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span> 1. row <span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span>
           id<span class="token punctuation">:</span> 1
  select_type<span class="token punctuation">:</span> SIMPLE
        table<span class="token punctuation">:</span> t1
         type<span class="token punctuation">:</span> range
possible_keys<span class="token punctuation">:</span> a
          key<span class="token punctuation">:</span> a
      key_len<span class="token punctuation">:</span> 5
          ref<span class="token punctuation">:</span> NULL
         rows<span class="token punctuation">:</span> 2
        Extra<span class="token punctuation">:</span> Using where with pushed condition</span></code></pre>
  </div>
  <p>
   Other supported comparisons for engine condition pushdown
          include the following:
  </p>
  <div class="itemizedlist">
   <ul class="itemizedlist" style="list-style-type: disc; ">
    <li class="listitem">
     <p>
      <code class="literal">
       <em class="replaceable">
        <code>
         column
        </code>
       </em>
       [NOT] LIKE
       <em class="replaceable">
        <code>
         pattern
        </code>
       </em>
      </code>
     </p>
     <p>
      <em class="replaceable">
       <code>
        pattern
       </code>
      </em>
      must be a string
              literal containing the pattern to be matched; for syntax,
              see
      <a class="xref" href="string-comparison-functions.html" title="14.8.1 String Comparison Functions and Operators">
       Section 14.8.1, “String Comparison Functions and Operators”
      </a>
      .
     </p>
    </li>
    <li class="listitem">
     <p>
      <code class="literal">
       <em class="replaceable">
        <code>
         column
        </code>
       </em>
       IS [NOT]
              NULL
      </code>
     </p>
    </li>
    <li class="listitem">
     <p>
      <code class="literal">
       <em class="replaceable">
        <code>
         column
        </code>
       </em>
       IN
              (
       <em class="replaceable">
        <code>
         value_list
        </code>
       </em>
       )
      </code>
     </p>
     <p>
      Each item in the
      <em class="replaceable">
       <code>
        value_list
       </code>
      </em>
      must be a constant, literal value.
     </p>
    </li>
    <li class="listitem">
     <p>
      <code class="literal">
       <em class="replaceable">
        <code>
         column
        </code>
       </em>
       BETWEEN
       <em class="replaceable">
        <code>
         constant1
        </code>
       </em>
       AND
       <em class="replaceable">
        <code>
         constant2
        </code>
       </em>
      </code>
     </p>
     <p>
      <em class="replaceable">
       <code>
        constant1
       </code>
      </em>
      and
      <em class="replaceable">
       <code>
        constant2
       </code>
      </em>
      must each be a
              constant, literal value.
     </p>
    </li>
   </ul>
  </div>
  <p>
   In all of the cases in the preceding list, it is possible for
          the condition to be converted into the form of one or more
          direct comparisons between a column and a constant.
  </p>
  <p>
   Engine condition pushdown is enabled by default. To disable it
          at server startup, set the
   <a class="link" href="server-system-variables.html#sysvar_optimizer_switch">
    <code class="literal">
     optimizer_switch
    </code>
   </a>
   system
          variable's
   <a class="link" href="switchable-optimizations.html#optflag_engine-condition-pushdown">
    <code class="literal">
     engine_condition_pushdown
    </code>
   </a>
   flag to
   <code class="literal">
    off
   </code>
   . For example, in a
   <code class="filename">
    my.cnf
   </code>
   file, use these lines:
  </p>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting language-ini"><div class="docs-select-all right" id="sa1475101"><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-ini"><span class="token selector">[mysqld]</span>
<span class="token constant">optimizer_switch</span><span class="token attr-value"><span class="token punctuation">=</span>engine_condition_pushdown=off</span></code></pre>
  </div>
  <p>
   At runtime, disable condition pushdown like this:
  </p>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting one-line language-sql"><div class="docs-select-all right" id="sa92286628"><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">'engine_condition_pushdown=off'</span><span class="token punctuation">;</span></code></pre>
  </div>
  <p>
   <b>
    Limitations.
   </b>
   Engine condition pushdown is subject to the following
            limitations:
  </p>
  <div class="itemizedlist">
   <ul class="itemizedlist" style="list-style-type: disc; ">
    <li class="listitem">
     <p>
      Engine condition pushdown is supported only by the
      <a class="link" href="mysql-cluster.html" title="Chapter 25 MySQL NDB Cluster 8.4">
       <code class="literal">
        NDB
       </code>
      </a>
      storage engine.
     </p>
    </li>
    <li class="listitem">
     <p>
      In NDB 8.4, columns can be compared with one
              another as long as they are of exactly the same type,
              including the same signedness, length, character set,
              precision, and scale, where these are applicable.
     </p>
    </li>
    <li class="listitem">
     <p>
      Columns used in comparisons cannot be of any of the
      <a class="link" href="blob.html" title="13.3.4 The BLOB and TEXT Types">
       <code class="literal">
        BLOB
       </code>
      </a>
      or
      <a class="link" href="blob.html" title="13.3.4 The BLOB and TEXT Types">
       <code class="literal">
        TEXT
       </code>
      </a>
      types. This exclusion
              extends to
      <a class="link" href="json.html" title="13.5 The JSON Data Type">
       <code class="literal">
        JSON
       </code>
      </a>
      ,
      <a class="link" href="bit-type.html" title="13.1.5 Bit-Value Type - BIT">
       <code class="literal">
        BIT
       </code>
      </a>
      , and
      <a class="link" href="enum.html" title="13.3.5 The ENUM Type">
       <code class="literal">
        ENUM
       </code>
      </a>
      columns as well.
     </p>
    </li>
    <li class="listitem">
     <p>
      A string value to be compared with a column must use the
              same collation as the column.
     </p>
    </li>
    <li class="listitem">
     <p>
      Joins are not directly supported; conditions involving
              multiple tables are pushed separately where possible. Use
              extended
      <a class="link" href="explain.html" title="15.8.2 EXPLAIN Statement">
       <code class="literal">
        EXPLAIN
       </code>
      </a>
      output to
              determine which conditions are actually pushed down. See
      <a class="xref" href="explain-extended.html" title="10.8.3 Extended EXPLAIN Output Format">
       Section 10.8.3, “Extended EXPLAIN Output Format”
      </a>
      .
     </p>
    </li>
   </ul>
  </div>
  <p>
   Previously, engine condition pushdown was limited to terms
          referring to column values from the same table to which the
          condition was being pushed. In NDB 8.4, column
          values from tables earlier in the query plan can also be
          referred to from pushed conditions. This reduces the number of
          rows which must be handled by the SQL node during join
          processing. Filtering can be also performed in parallel in the
          LDM threads, rather than in a single
   <a class="link" href="mysqld.html" title="6.3.1 mysqld — The MySQL Server">
    <span class="command">
     <strong>
      mysqld
     </strong>
    </span>
   </a>
   process. This has the potential to improve performance of
          queries by a significant margin.
  </p>
  <p>
   <code class="literal">
    NDB
   </code>
   can push an outer join using a scan if
          there are no unpushable conditions on any table used in the
          same join nest, or on any table in join nests above it on
          which it depends. This is also true for a semijoin, provided
          the optimization strategy employed is
   <code class="literal">
    firstMatch
   </code>
   (see
   <a class="ulink" href="/doc/refman/8.0/en/semijoins.html" target="_top">
    Optimizing IN and EXISTS Subquery Predicates with Semijoin Transformations
   </a>
   ).
  </p>
  <p>
   Join algorithms cannot be combined with referring columns from
          previous tables in the following two situations:
  </p>
  <div class="orderedlist">
   <ol class="orderedlist" type="1">
    <li class="listitem">
     <p>
      When any of the referred previous tables are in a join
              buffer. In this case, each row retrieved from the
              scan-filtered table is matched against every row in the
              buffer. This means that there is no single specific row
              from which column values can be fetched from when
              generating the scan filter.
     </p>
    </li>
    <li class="listitem">
     <p>
      When the column originates from a child operation in a
              pushed join. This is because rows referenced from ancestor
              operations in the join have not yet been retrieved when
              the scan filter is generated.
     </p>
    </li>
   </ol>
  </div>
  <p>
   Columns from ancestor tables in a join can be pushed down,
          provided that they meet the requirements listed previously. An
          example of such a query, using the table
   <code class="literal">
    t1
   </code>
   created previously, is shown here:
  </p>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa51716798"><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 prompt">mysql&gt;</span> <span class="token keyword">EXPLAIN</span> 
<span class="token prompt">    -&gt;</span>   <span class="token keyword">SELECT</span> <span class="token operator">*</span> <span class="token keyword">FROM</span> t1 <span class="token keyword">AS</span> x 
<span class="token prompt">    -&gt;</span>   <span class="token keyword">LEFT</span> <span class="token keyword">JOIN</span> t1 <span class="token keyword">AS</span> y 
<span class="token prompt">    -&gt;</span>   <span class="token keyword">ON</span> x<span class="token punctuation">.</span>a<span class="token operator">=</span><span class="token number">0</span> <span class="token operator">AND</span> y<span class="token punctuation">.</span>b<span class="token operator">&gt;=</span><span class="token number">3</span>\G
<span class="token output"><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span> 1. row <span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span>
           id<span class="token punctuation">:</span> 1
  select_type<span class="token punctuation">:</span> SIMPLE
        table<span class="token punctuation">:</span> x
   partitions<span class="token punctuation">:</span> p0,p1
         type<span class="token punctuation">:</span> ALL
possible_keys<span class="token punctuation">:</span> NULL
          key<span class="token punctuation">:</span> NULL
      key_len<span class="token punctuation">:</span> NULL
          ref<span class="token punctuation">:</span> NULL
         rows<span class="token punctuation">:</span> 4
     filtered<span class="token punctuation">:</span> 100.00
        Extra<span class="token punctuation">:</span> NULL
<span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span> 2. row <span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span>
           id<span class="token punctuation">:</span> 1
  select_type<span class="token punctuation">:</span> SIMPLE
        table<span class="token punctuation">:</span> y
   partitions<span class="token punctuation">:</span> p0,p1
         type<span class="token punctuation">:</span> ALL
possible_keys<span class="token punctuation">:</span> NULL
          key<span class="token punctuation">:</span> NULL
      key_len<span class="token punctuation">:</span> NULL
          ref<span class="token punctuation">:</span> NULL
         rows<span class="token punctuation">:</span> 4
     filtered<span class="token punctuation">:</span> 100.00
        Extra<span class="token punctuation">:</span> Using where; Using pushed condition (`test`.`y`.`b` &gt;= 3); Using join buffer (hash join)
</span><span class="token output">2 rows in set, 2 warnings (0.00 sec)</span></code></pre>
  </div>
 </div>
 <br/>
</div>
