<div id="docs-body">
 <div class="section">
  <div class="titlepage">
   <div>
    <div>
     <h2 class="title">
      <a name="partitioning-pruning">
      </a>
      26.4 Partition Pruning
     </h2>
    </div>
   </div>
  </div>
  <a class="indexterm" name="idm46045083359696">
  </a>
  <a class="indexterm" name="idm46045083358624">
  </a>
  <p>
   The optimization known as
   <span class="firstterm">
    partition
      pruning
   </span>
   is based on a relatively simple concept which
      can be described as
   <span class="quote">
    “
    <span class="quote">
     Do not scan partitions where there can
      be no matching values
    </span>
    ”
   </span>
   . Suppose a partitioned table
   <code class="literal">
    t1
   </code>
   is created by this statement:
  </p>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa2076728"><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>
    fname <span class="token datatype">VARCHAR</span><span class="token punctuation">(</span><span class="token number">50</span><span class="token punctuation">)</span> <span class="token operator">NOT</span> <span class="token boolean">NULL</span><span class="token punctuation">,</span>
    lname <span class="token datatype">VARCHAR</span><span class="token punctuation">(</span><span class="token number">50</span><span class="token punctuation">)</span> <span class="token operator">NOT</span> <span class="token boolean">NULL</span><span class="token punctuation">,</span>
    region_code <span class="token datatype">TINYINT</span> <span class="token keyword">UNSIGNED</span> <span class="token operator">NOT</span> <span class="token boolean">NULL</span><span class="token punctuation">,</span>
    dob <span class="token datatype">DATE</span> <span class="token operator">NOT</span> <span class="token boolean">NULL</span>
<span class="token punctuation">)</span>
<span class="token keyword">PARTITION</span> <span class="token keyword">BY</span> <span class="token keyword">RANGE</span><span class="token punctuation">(</span> region_code <span class="token punctuation">)</span> <span class="token punctuation">(</span>
    <span class="token keyword">PARTITION</span> p0 <span class="token keyword">VALUES</span> <span class="token keyword">LESS</span> <span class="token keyword">THAN</span> <span class="token punctuation">(</span><span class="token number">64</span><span class="token punctuation">)</span><span class="token punctuation">,</span>
    <span class="token keyword">PARTITION</span> p1 <span class="token keyword">VALUES</span> <span class="token keyword">LESS</span> <span class="token keyword">THAN</span> <span class="token punctuation">(</span><span class="token number">128</span><span class="token punctuation">)</span><span class="token punctuation">,</span>
    <span class="token keyword">PARTITION</span> p2 <span class="token keyword">VALUES</span> <span class="token keyword">LESS</span> <span class="token keyword">THAN</span> <span class="token punctuation">(</span><span class="token number">192</span><span class="token punctuation">)</span><span class="token punctuation">,</span>
    <span class="token keyword">PARTITION</span> p3 <span class="token keyword">VALUES</span> <span class="token keyword">LESS</span> <span class="token keyword">THAN</span> <span class="token keyword">MAXVALUE</span>
<span class="token punctuation">)</span><span class="token punctuation">;</span></code></pre>
  </div>
  <p>
   Suppose that you wish to obtain results from a
   <a class="link" href="select.html" title="15.2.13 SELECT Statement">
    <code class="literal">
     SELECT
    </code>
   </a>
   statement such as this one:
  </p>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa56687889"><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> fname<span class="token punctuation">,</span> lname<span class="token punctuation">,</span> region_code<span class="token punctuation">,</span> dob
    <span class="token keyword">FROM</span> t1
    <span class="token keyword">WHERE</span> region_code <span class="token operator">&gt;</span> <span class="token number">125</span> <span class="token operator">AND</span> region_code <span class="token operator">&lt;</span> <span class="token number">130</span><span class="token punctuation">;</span></code></pre>
  </div>
  <p>
   It is easy to see that none of the rows which ought to be returned
      are in either of the partitions
   <code class="literal">
    p0
   </code>
   or
   <code class="literal">
    p3
   </code>
   ; that is, we need search only in partitions
   <code class="literal">
    p1
   </code>
   and
   <code class="literal">
    p2
   </code>
   to find matching
      rows. By limiting the search, it is possible to expend much less
      time and effort in finding matching rows than by scanning all
      partitions in the table. This
   <span class="quote">
    “
    <span class="quote">
     cutting away
    </span>
    ”
   </span>
   of
      unneeded partitions is known as
   <span class="firstterm">
    pruning
   </span>
   . When the optimizer
      can make use of partition pruning in performing this query,
      execution of the query can be an order of magnitude faster than
      the same query against a nonpartitioned table containing the same
      column definitions and data.
  </p>
  <p>
   The optimizer can perform pruning whenever a
   <code class="literal">
    WHERE
   </code>
   condition can be reduced to either one of
      the following two cases:
  </p>
  <div class="itemizedlist">
   <ul class="itemizedlist" style="list-style-type: disc; ">
    <li class="listitem">
     <p>
      <code class="literal">
       <em class="replaceable">
        <code>
         partition_column
        </code>
       </em>
       =
       <em class="replaceable">
        <code>
         constant
        </code>
       </em>
      </code>
     </p>
    </li>
    <li class="listitem">
     <p>
      <code class="literal">
       <em class="replaceable">
        <code>
         partition_column
        </code>
       </em>
       IN
          (
       <em class="replaceable">
        <code>
         constant1
        </code>
       </em>
       ,
       <em class="replaceable">
        <code>
         constant2
        </code>
       </em>
       , ...,
       <em class="replaceable">
        <code>
         constantN
        </code>
       </em>
       )
      </code>
     </p>
    </li>
   </ul>
  </div>
  <p>
   In the first case, the optimizer simply evaluates the partitioning
      expression for the value given, determines which partition
      contains that value, and scans only this partition. In many cases,
      the equal sign can be replaced with another arithmetic comparison,
      including
   <code class="literal">
    &lt;
   </code>
   ,
   <code class="literal">
    &gt;
   </code>
   ,
   <code class="literal">
    &lt;=
   </code>
   ,
   <code class="literal">
    &gt;=
   </code>
   , and
   <code class="literal">
    &lt;&gt;
   </code>
   . Some queries using
   <code class="literal">
    BETWEEN
   </code>
   in the
   <code class="literal">
    WHERE
   </code>
   clause
      can also take advantage of partition pruning. See the examples
      later in this section.
  </p>
  <p>
   In the second case, the optimizer evaluates the partitioning
      expression for each value in the list, creates a list of matching
      partitions, and then scans only the partitions in this partition
      list.
  </p>
  <p>
   <a class="link" href="select.html" title="15.2.13 SELECT Statement">
    <code class="literal">
     SELECT
    </code>
   </a>
   ,
   <a class="link" href="delete.html" title="15.2.2 DELETE Statement">
    <code class="literal">
     DELETE
    </code>
   </a>
   , and
   <a class="link" href="update.html" title="15.2.17 UPDATE Statement">
    <code class="literal">
     UPDATE
    </code>
   </a>
   statements support partition
      pruning. An
   <a class="link" href="insert.html" title="15.2.7 INSERT Statement">
    <code class="literal">
     INSERT
    </code>
   </a>
   statement also
      accesses only one partition per inserted row; this is true even
      for a table that is partitioned by
   <code class="literal">
    HASH
   </code>
   or
   <code class="literal">
    KEY
   </code>
   although this is not currently shown in the
      output of
   <a class="link" href="explain.html" title="15.8.2 EXPLAIN Statement">
    <code class="literal">
     EXPLAIN
    </code>
   </a>
   .
  </p>
  <p>
   Pruning can also be applied to short ranges, which the optimizer
      can convert into equivalent lists of values. For instance, in the
      previous example, the
   <code class="literal">
    WHERE
   </code>
   clause can be
      converted to
   <code class="literal">
    WHERE region_code IN (126, 127, 128,
      129)
   </code>
   . Then the optimizer can determine that the first
      two values in the list are found in partition
   <code class="literal">
    p1
   </code>
   , the remaining two values in partition
   <code class="literal">
    p2
   </code>
   , and that the other partitions contain no
      relevant values and so do not need to be searched for matching
      rows.
  </p>
  <p>
   The optimizer can also perform pruning for
   <code class="literal">
    WHERE
   </code>
   conditions that involve comparisons of
      the preceding types on multiple columns for tables that use
   <code class="literal">
    RANGE COLUMNS
   </code>
   or
   <code class="literal">
    LIST
      COLUMNS
   </code>
   partitioning.
  </p>
  <a class="indexterm" name="idm46045083318064">
  </a>
  <p>
   This type of optimization can be applied whenever the partitioning
      expression consists of an equality or a range which can be reduced
      to a set of equalities, or when the partitioning expression
      represents an increasing or decreasing relationship. Pruning can
      also be applied for tables partitioned on a
   <a class="link" href="datetime.html" title="13.2.2 The DATE, DATETIME, and TIMESTAMP Types">
    <code class="literal">
     DATE
    </code>
   </a>
   or
   <a class="link" href="datetime.html" title="13.2.2 The DATE, DATETIME, and TIMESTAMP Types">
    <code class="literal">
     DATETIME
    </code>
   </a>
   column when the
      partitioning expression uses the
   <a class="link" href="date-and-time-functions.html#function_year">
    <code class="literal">
     YEAR()
    </code>
   </a>
   or
   <a class="link" href="date-and-time-functions.html#function_to-days">
    <code class="literal">
     TO_DAYS()
    </code>
   </a>
   function. Pruning can
      also be applied for such tables when the partitioning expression
      uses the
   <a class="link" href="date-and-time-functions.html#function_to-seconds">
    <code class="literal">
     TO_SECONDS()
    </code>
   </a>
   function.
  </p>
  <p>
   Suppose that table
   <code class="literal">
    t2
   </code>
   , partitioned on a
   <a class="link" href="datetime.html" title="13.2.2 The DATE, DATETIME, and TIMESTAMP Types">
    <code class="literal">
     DATE
    </code>
   </a>
   column, is created using the
      statement shown here:
  </p>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa48450255"><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> t2 <span class="token punctuation">(</span>
    fname <span class="token datatype">VARCHAR</span><span class="token punctuation">(</span><span class="token number">50</span><span class="token punctuation">)</span> <span class="token operator">NOT</span> <span class="token boolean">NULL</span><span class="token punctuation">,</span>
    lname <span class="token datatype">VARCHAR</span><span class="token punctuation">(</span><span class="token number">50</span><span class="token punctuation">)</span> <span class="token operator">NOT</span> <span class="token boolean">NULL</span><span class="token punctuation">,</span>
    region_code <span class="token datatype">TINYINT</span> <span class="token keyword">UNSIGNED</span> <span class="token operator">NOT</span> <span class="token boolean">NULL</span><span class="token punctuation">,</span>
    dob <span class="token datatype">DATE</span> <span class="token operator">NOT</span> <span class="token boolean">NULL</span>
<span class="token punctuation">)</span>
<span class="token keyword">PARTITION</span> <span class="token keyword">BY</span> <span class="token keyword">RANGE</span><span class="token punctuation">(</span> <span class="token function">YEAR</span><span class="token punctuation">(</span>dob<span class="token punctuation">)</span> <span class="token punctuation">)</span> <span class="token punctuation">(</span>
    <span class="token keyword">PARTITION</span> d0 <span class="token keyword">VALUES</span> <span class="token keyword">LESS</span> <span class="token keyword">THAN</span> <span class="token punctuation">(</span><span class="token number">1970</span><span class="token punctuation">)</span><span class="token punctuation">,</span>
    <span class="token keyword">PARTITION</span> d1 <span class="token keyword">VALUES</span> <span class="token keyword">LESS</span> <span class="token keyword">THAN</span> <span class="token punctuation">(</span><span class="token number">1975</span><span class="token punctuation">)</span><span class="token punctuation">,</span>
    <span class="token keyword">PARTITION</span> d2 <span class="token keyword">VALUES</span> <span class="token keyword">LESS</span> <span class="token keyword">THAN</span> <span class="token punctuation">(</span><span class="token number">1980</span><span class="token punctuation">)</span><span class="token punctuation">,</span>
    <span class="token keyword">PARTITION</span> d3 <span class="token keyword">VALUES</span> <span class="token keyword">LESS</span> <span class="token keyword">THAN</span> <span class="token punctuation">(</span><span class="token number">1985</span><span class="token punctuation">)</span><span class="token punctuation">,</span>
    <span class="token keyword">PARTITION</span> d4 <span class="token keyword">VALUES</span> <span class="token keyword">LESS</span> <span class="token keyword">THAN</span> <span class="token punctuation">(</span><span class="token number">1990</span><span class="token punctuation">)</span><span class="token punctuation">,</span>
    <span class="token keyword">PARTITION</span> d5 <span class="token keyword">VALUES</span> <span class="token keyword">LESS</span> <span class="token keyword">THAN</span> <span class="token punctuation">(</span><span class="token number">2000</span><span class="token punctuation">)</span><span class="token punctuation">,</span>
    <span class="token keyword">PARTITION</span> d6 <span class="token keyword">VALUES</span> <span class="token keyword">LESS</span> <span class="token keyword">THAN</span> <span class="token punctuation">(</span><span class="token number">2005</span><span class="token punctuation">)</span><span class="token punctuation">,</span>
    <span class="token keyword">PARTITION</span> d7 <span class="token keyword">VALUES</span> <span class="token keyword">LESS</span> <span class="token keyword">THAN</span> <span class="token keyword">MAXVALUE</span>
<span class="token punctuation">)</span><span class="token punctuation">;</span></code></pre>
  </div>
  <p>
   The following statements using
   <code class="literal">
    t2
   </code>
   can make of
      use partition pruning:
  </p>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa11017501"><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> t2 <span class="token keyword">WHERE</span> dob <span class="token operator">=</span> <span class="token string">'1982-06-23'</span><span class="token punctuation">;</span>

<span class="token keyword">UPDATE</span> t2 <span class="token keyword">SET</span> region_code <span class="token operator">=</span> <span class="token number">8</span> <span class="token keyword">WHERE</span> dob <span class="token operator">BETWEEN</span> <span class="token string">'1991-02-15'</span> <span class="token operator">AND</span> <span class="token string">'1997-04-25'</span><span class="token punctuation">;</span>

<span class="token keyword">DELETE</span> <span class="token keyword">FROM</span> t2 <span class="token keyword">WHERE</span> dob <span class="token operator">&gt;=</span> <span class="token string">'1984-06-21'</span> <span class="token operator">AND</span> dob <span class="token operator">&lt;=</span> <span class="token string">'1999-06-21'</span></code></pre>
  </div>
  <p>
   In the case of the last statement, the optimizer can also act as
      follows:
  </p>
  <div class="orderedlist">
   <ol class="orderedlist" type="1">
    <li class="listitem">
     <p>
      <span class="emphasis">
       <em>
        Find the partition containing the low end of the
          range
       </em>
      </span>
      .
     </p>
     <p>
      <a class="link" href="date-and-time-functions.html#function_year">
       <code class="literal">
        YEAR('1984-06-21')
       </code>
      </a>
      yields the
          value
      <code class="literal">
       1984
      </code>
      , which is found in partition
      <code class="literal">
       d3
      </code>
      .
     </p>
    </li>
    <li class="listitem">
     <p>
      <span class="emphasis">
       <em>
        Find the partition containing the high end of the
          range
       </em>
      </span>
      .
     </p>
     <p>
      <a class="link" href="date-and-time-functions.html#function_year">
       <code class="literal">
        YEAR('1999-06-21')
       </code>
      </a>
      evaluates to
      <code class="literal">
       1999
      </code>
      , which is found in partition
      <code class="literal">
       d5
      </code>
      .
     </p>
    </li>
    <li class="listitem">
     <p>
      <span class="emphasis">
       <em>
        Scan only these two partitions and any partitions
          that may lie between them
       </em>
      </span>
      .
     </p>
     <p>
      In this case, this means that only partitions
      <code class="literal">
       d3
      </code>
      ,
      <code class="literal">
       d4
      </code>
      , and
      <code class="literal">
       d5
      </code>
      are scanned. The remaining partitions
          may be safely ignored (and are ignored).
     </p>
    </li>
   </ol>
  </div>
  <div class="important" style="margin-left: 0.5in; margin-right: 0.5in;">
   <div class="admon-title">
    Important
   </div>
   <p>
    Invalid
    <code class="literal">
     DATE
    </code>
    and
    <code class="literal">
     DATETIME
    </code>
    values referenced in the
    <code class="literal">
     WHERE
    </code>
    condition of a
        statement against a partitioned table are treated as
    <code class="literal">
     NULL
    </code>
    . This means that a query such as
    <code class="literal">
     SELECT * FROM
     <em class="replaceable">
      <code>
       partitioned_table
      </code>
     </em>
     WHERE
     <em class="replaceable">
      <code>
       date_column
      </code>
     </em>
     &lt;
        '2008-12-00'
    </code>
    does not return any values (see Bug
        #40972).
   </p>
  </div>
  <p>
   So far, we have looked only at examples using
   <code class="literal">
    RANGE
   </code>
   partitioning, but pruning can be applied
      with other partitioning types as well.
  </p>
  <p>
   Consider a table that is partitioned by
   <code class="literal">
    LIST
   </code>
   ,
      where the partitioning expression is increasing or decreasing,
      such as the table
   <code class="literal">
    t3
   </code>
   shown here. (In this
      example, we assume for the sake of brevity that the
   <code class="literal">
    region_code
   </code>
   column is limited to values between
      1 and 10 inclusive.)
  </p>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa88609797"><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> t3 <span class="token punctuation">(</span>
    fname <span class="token datatype">VARCHAR</span><span class="token punctuation">(</span><span class="token number">50</span><span class="token punctuation">)</span> <span class="token operator">NOT</span> <span class="token boolean">NULL</span><span class="token punctuation">,</span>
    lname <span class="token datatype">VARCHAR</span><span class="token punctuation">(</span><span class="token number">50</span><span class="token punctuation">)</span> <span class="token operator">NOT</span> <span class="token boolean">NULL</span><span class="token punctuation">,</span>
    region_code <span class="token datatype">TINYINT</span> <span class="token keyword">UNSIGNED</span> <span class="token operator">NOT</span> <span class="token boolean">NULL</span><span class="token punctuation">,</span>
    dob <span class="token datatype">DATE</span> <span class="token operator">NOT</span> <span class="token boolean">NULL</span>
<span class="token punctuation">)</span>
<span class="token keyword">PARTITION</span> <span class="token keyword">BY</span> <span class="token keyword">LIST</span><span class="token punctuation">(</span>region_code<span class="token punctuation">)</span> <span class="token punctuation">(</span>
    <span class="token keyword">PARTITION</span> r0 <span class="token keyword">VALUES</span> <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">3</span><span class="token punctuation">)</span><span class="token punctuation">,</span>
    <span class="token keyword">PARTITION</span> r1 <span class="token keyword">VALUES</span> <span class="token keyword">IN</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 number">8</span><span class="token punctuation">)</span><span class="token punctuation">,</span>
    <span class="token keyword">PARTITION</span> r2 <span class="token keyword">VALUES</span> <span class="token keyword">IN</span> <span class="token punctuation">(</span><span class="token number">4</span><span class="token punctuation">,</span> <span class="token number">9</span><span class="token punctuation">)</span><span class="token punctuation">,</span>
    <span class="token keyword">PARTITION</span> r3 <span class="token keyword">VALUES</span> <span class="token keyword">IN</span> <span class="token punctuation">(</span><span class="token number">6</span><span class="token punctuation">,</span> <span class="token number">7</span><span class="token punctuation">,</span> <span class="token number">10</span><span class="token punctuation">)</span>
<span class="token punctuation">)</span><span class="token punctuation">;</span></code></pre>
  </div>
  <p>
   For a statement such as
   <code class="literal">
    SELECT * FROM t3 WHERE
      region_code BETWEEN 1 AND 3
   </code>
   , the optimizer determines in
      which partitions the values 1, 2, and 3 are found
      (
   <code class="literal">
    r0
   </code>
   and
   <code class="literal">
    r1
   </code>
   ) and skips the
      remaining ones (
   <code class="literal">
    r2
   </code>
   and
   <code class="literal">
    r3
   </code>
   ).
  </p>
  <p>
   For tables that are partitioned by
   <code class="literal">
    HASH
   </code>
   or
   <code class="literal">
    [LINEAR] KEY
   </code>
   , partition pruning is also
      possible in cases in which the
   <code class="literal">
    WHERE
   </code>
   clause
      uses a simple
   <code class="literal">
    =
   </code>
   relation against a column used
      in the partitioning expression. Consider a table created like
      this:
  </p>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa58670312"><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> t4 <span class="token punctuation">(</span>
    fname <span class="token datatype">VARCHAR</span><span class="token punctuation">(</span><span class="token number">50</span><span class="token punctuation">)</span> <span class="token operator">NOT</span> <span class="token boolean">NULL</span><span class="token punctuation">,</span>
    lname <span class="token datatype">VARCHAR</span><span class="token punctuation">(</span><span class="token number">50</span><span class="token punctuation">)</span> <span class="token operator">NOT</span> <span class="token boolean">NULL</span><span class="token punctuation">,</span>
    region_code <span class="token datatype">TINYINT</span> <span class="token keyword">UNSIGNED</span> <span class="token operator">NOT</span> <span class="token boolean">NULL</span><span class="token punctuation">,</span>
    dob <span class="token datatype">DATE</span> <span class="token operator">NOT</span> <span class="token boolean">NULL</span>
<span class="token punctuation">)</span>
<span class="token keyword">PARTITION</span> <span class="token keyword">BY</span> <span class="token keyword">KEY</span><span class="token punctuation">(</span>region_code<span class="token punctuation">)</span>
<span class="token keyword">PARTITIONS</span> <span class="token number">8</span><span class="token punctuation">;</span></code></pre>
  </div>
  <p>
   A statement that compares a column value with a constant can be
      pruned:
  </p>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting one-line language-sql"><div class="docs-select-all right" id="sa23312537"><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">UPDATE</span> t4 <span class="token keyword">WHERE</span> region_code <span class="token operator">=</span> <span class="token number">7</span><span class="token punctuation">;</span></code></pre>
  </div>
  <p>
   Pruning can also be employed for short ranges, because the
      optimizer can turn such conditions into
   <code class="literal">
    IN
   </code>
   relations. For example, using the same table
   <code class="literal">
    t4
   </code>
   as defined previously, queries such as these can be pruned:
  </p>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa55862727"><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> t4 <span class="token keyword">WHERE</span> region_code <span class="token operator">&gt;</span> <span class="token number">2</span> <span class="token operator">AND</span> region_code <span class="token operator">&lt;</span> <span class="token number">6</span><span class="token punctuation">;</span>

<span class="token keyword">SELECT</span> <span class="token operator">*</span> <span class="token keyword">FROM</span> t4 <span class="token keyword">WHERE</span> region_code <span class="token operator">BETWEEN</span> <span class="token number">3</span> <span class="token operator">AND</span> <span class="token number">5</span><span class="token punctuation">;</span></code></pre>
  </div>
  <p>
   In both these cases, the
   <code class="literal">
    WHERE
   </code>
   clause is
      transformed by the optimizer into
   <code class="literal">
    WHERE region_code IN
      (3, 4, 5)
   </code>
   .
  </p>
  <div class="important" style="margin-left: 0.5in; margin-right: 0.5in;">
   <div class="admon-title">
    Important
   </div>
   <p>
    This optimization is used only if the range size is smaller than
        the number of partitions. Consider this statement:
   </p>
   <div class="copytoclipboard-wrapper">
    <pre class="programlisting one-line language-sql"><div class="docs-select-all right" id="sa89894967"><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">DELETE</span> <span class="token keyword">FROM</span> t4 <span class="token keyword">WHERE</span> region_code <span class="token operator">BETWEEN</span> <span class="token number">4</span> <span class="token operator">AND</span> <span class="token number">12</span><span class="token punctuation">;</span></code></pre>
   </div>
   <p>
    The range in the
    <code class="literal">
     WHERE
    </code>
    clause covers 9 values
        (4, 5, 6, 7, 8, 9, 10, 11, 12), but
    <code class="literal">
     t4
    </code>
    has
        only 8 partitions. This means that the
    <code class="literal">
     DELETE
    </code>
    cannot be pruned.
   </p>
  </div>
  <p>
   When a table is partitioned by
   <code class="literal">
    HASH
   </code>
   or
   <code class="literal">
    [LINEAR] KEY
   </code>
   , pruning can be used only on
      integer columns. For example, this statement cannot use pruning
      because
   <code class="literal">
    dob
   </code>
   is a
   <a class="link" href="datetime.html" title="13.2.2 The DATE, DATETIME, and TIMESTAMP Types">
    <code class="literal">
     DATE
    </code>
   </a>
   column:
  </p>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting one-line language-sql"><div class="docs-select-all right" id="sa16811410"><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> t4 <span class="token keyword">WHERE</span> dob <span class="token operator">&gt;=</span> <span class="token string">'2001-04-14'</span> <span class="token operator">AND</span> dob <span class="token operator">&lt;=</span> <span class="token string">'2005-10-15'</span><span class="token punctuation">;</span></code></pre>
  </div>
  <p>
   However, if the table stores year values in an
   <a class="link" href="integer-types.html" title="13.1.2 Integer Types (Exact Value) - INTEGER, INT, SMALLINT, TINYINT, MEDIUMINT, BIGINT">
    <code class="literal">
     INT
    </code>
   </a>
   column, then a query having
   <code class="literal">
    WHERE year_col &gt;= 2001 AND year_col &lt;=
      2005
   </code>
   can be pruned.
  </p>
  <p>
   Tables using a storage engine that provides automatic
      partitioning, such as the
   <code class="literal">
    NDB
   </code>
   storage engine
      used by MySQL Cluster can be pruned if they are explicitly
      partitioned.
  </p>
 </div>
 <br/>
</div>
