<div id="docs-body">
 <div class="section">
  <div class="titlepage">
   <div>
    <div>
     <h3 class="title">
      <a name="partitioning-hash">
      </a>
      26.2.4 HASH Partitioning
     </h3>
    </div>
   </div>
  </div>
  <div class="toc">
   <dl class="toc">
    <dt>
     <span class="section">
      <a href="partitioning-linear-hash.html">
       26.2.4.1 LINEAR HASH Partitioning
      </a>
     </span>
    </dt>
   </dl>
  </div>
  <a class="indexterm" name="idm46045084337344">
  </a>
  <a class="indexterm" name="idm46045084335856">
  </a>
  <p>
   Partitioning by
   <code class="literal">
    HASH
   </code>
   is used primarily to
        ensure an even distribution of data among a predetermined number
        of partitions. With range or list partitioning, you must specify
        explicitly which partition a given column value or set of column
        values should be stored in; with hash partitioning, this
        decision is taken care of for you, and you need only specify a
        column value or expression based on a column value to be hashed
        and the number of partitions into which the partitioned table is
        to be divided.
  </p>
  <p>
   To partition a table using
   <code class="literal">
    HASH
   </code>
   partitioning,
        it is necessary to append to the
   <a class="link" href="create-table.html" title="15.1.20 CREATE TABLE Statement">
    <code class="literal">
     CREATE
        TABLE
    </code>
   </a>
   statement a
   <code class="literal">
    PARTITION BY HASH
        (
    <em class="replaceable">
     <code>
      expr
     </code>
    </em>
    )
   </code>
   clause, where
   <em class="replaceable">
    <code>
     expr
    </code>
   </em>
   is an expression that returns an
        integer. This can simply be the name of a column whose type is
        one of MySQL's integer types. In addition, you most likely
        want to follow this with
   <code class="literal">
    PARTITIONS
    <em class="replaceable">
     <code>
      num
     </code>
    </em>
   </code>
   , where
   <em class="replaceable">
    <code>
     num
    </code>
   </em>
   is a positive integer
        representing the number of partitions into which the table is to
        be divided.
  </p>
  <div class="note" style="margin-left: 0.5in; margin-right: 0.5in;">
   <div class="admon-title">
    Note
   </div>
   <p>
    For simplicity, the tables in the examples that follow do not
          use any keys. You should be aware that, if a table has any
          unique keys, every column used in the partitioning expression
          for this table must be part of every unique key, including the
          primary key. See
    <a class="xref" href="partitioning-limitations-partitioning-keys-unique-keys.html" title="26.6.1 Partitioning Keys, Primary Keys, and Unique Keys">
     Section 26.6.1, “Partitioning Keys, Primary Keys, and Unique Keys”
    </a>
    ,
          for more information.
   </p>
  </div>
  <p>
   The following statement creates a table that uses hashing on the
   <code class="literal">
    store_id
   </code>
   column and is divided into 4
        partitions:
  </p>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa10036804"><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> employees <span class="token punctuation">(</span>
    id <span class="token datatype">INT</span> <span class="token operator">NOT</span> <span class="token boolean">NULL</span><span class="token punctuation">,</span>
    fname <span class="token datatype">VARCHAR</span><span class="token punctuation">(</span><span class="token number">30</span><span class="token punctuation">)</span><span class="token punctuation">,</span>
    lname <span class="token datatype">VARCHAR</span><span class="token punctuation">(</span><span class="token number">30</span><span class="token punctuation">)</span><span class="token punctuation">,</span>
    hired <span class="token datatype">DATE</span> <span class="token operator">NOT</span> <span class="token boolean">NULL</span> <span class="token keyword">DEFAULT</span> <span class="token string">'1970-01-01'</span><span class="token punctuation">,</span>
    separated <span class="token datatype">DATE</span> <span class="token operator">NOT</span> <span class="token boolean">NULL</span> <span class="token keyword">DEFAULT</span> <span class="token string">'9999-12-31'</span><span class="token punctuation">,</span>
    job_code <span class="token datatype">INT</span><span class="token punctuation">,</span>
    store_id <span class="token datatype">INT</span>
<span class="token punctuation">)</span>
<span class="token keyword">PARTITION</span> <span class="token keyword">BY</span> <span class="token keyword">HASH</span><span class="token punctuation">(</span>store_id<span class="token punctuation">)</span>
<span class="token keyword">PARTITIONS</span> <span class="token number">4</span><span class="token punctuation">;</span></code></pre>
  </div>
  <p>
   If you do not include a
   <code class="literal">
    PARTITIONS
   </code>
   clause,
        the number of partitions defaults to
   <code class="literal">
    1
   </code>
   ; using
        the
   <code class="literal">
    PARTITIONS
   </code>
   keyword without a number
        following it results in a syntax error.
  </p>
  <a class="indexterm" name="idm46045084320416">
  </a>
  <p>
   You can also use an SQL expression that returns an integer for
   <em class="replaceable">
    <code>
     expr
    </code>
   </em>
   . For instance, you might want to
        partition based on the year in which an employee was hired. This
        can be done as shown here:
  </p>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa97053718"><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> employees <span class="token punctuation">(</span>
    id <span class="token datatype">INT</span> <span class="token operator">NOT</span> <span class="token boolean">NULL</span><span class="token punctuation">,</span>
    fname <span class="token datatype">VARCHAR</span><span class="token punctuation">(</span><span class="token number">30</span><span class="token punctuation">)</span><span class="token punctuation">,</span>
    lname <span class="token datatype">VARCHAR</span><span class="token punctuation">(</span><span class="token number">30</span><span class="token punctuation">)</span><span class="token punctuation">,</span>
    hired <span class="token datatype">DATE</span> <span class="token operator">NOT</span> <span class="token boolean">NULL</span> <span class="token keyword">DEFAULT</span> <span class="token string">'1970-01-01'</span><span class="token punctuation">,</span>
    separated <span class="token datatype">DATE</span> <span class="token operator">NOT</span> <span class="token boolean">NULL</span> <span class="token keyword">DEFAULT</span> <span class="token string">'9999-12-31'</span><span class="token punctuation">,</span>
    job_code <span class="token datatype">INT</span><span class="token punctuation">,</span>
    store_id <span class="token datatype">INT</span>
<span class="token punctuation">)</span>
<span class="token keyword">PARTITION</span> <span class="token keyword">BY</span> <span class="token keyword">HASH</span><span class="token punctuation">(</span> <span class="token function">YEAR</span><span class="token punctuation">(</span>hired<span class="token punctuation">)</span> <span class="token punctuation">)</span>
<span class="token keyword">PARTITIONS</span> <span class="token number">4</span><span class="token punctuation">;</span></code></pre>
  </div>
  <p>
   <em class="replaceable">
    <code>
     expr
    </code>
   </em>
   must return a nonconstant,
        nonrandom integer value (in other words, it should be varying
        but deterministic), and must not contain any prohibited
        constructs as described in
   <a class="xref" href="partitioning-limitations.html" title="26.6 Restrictions and Limitations on Partitioning">
    Section 26.6, “Restrictions and Limitations on Partitioning”
   </a>
   . You should also keep
        in mind that this expression is evaluated each time a row is
        inserted or updated (or possibly deleted); this means that very
        complex expressions may give rise to performance issues,
        particularly when performing operations (such as batch inserts)
        that affect a great many rows at one time.
  </p>
  <p>
   The most efficient hashing function is one which operates upon a
        single table column and whose value increases or decreases
        consistently with the column value, as this allows for
   <span class="quote">
    “
    <span class="quote">
     pruning
    </span>
    ”
   </span>
   on ranges of partitions. That is, the
        more closely that the expression varies with the value of the
        column on which it is based, the more efficiently MySQL can use
        the expression for hash partitioning.
  </p>
  <p>
   For example, where
   <code class="literal">
    date_col
   </code>
   is a column of
        type
   <a class="link" href="datetime.html" title="13.2.2 The DATE, DATETIME, and TIMESTAMP Types">
    <code class="literal">
     DATE
    </code>
   </a>
   , then the expression
   <a class="link" href="date-and-time-functions.html#function_to-days">
    <code class="literal">
     TO_DAYS(date_col)
    </code>
   </a>
   is said to vary
        directly with the value of
   <code class="literal">
    date_col
   </code>
   , because
        for every change in the value of
   <code class="literal">
    date_col
   </code>
   ,
        the value of the expression changes in a consistent manner. The
        variance of the expression
   <a class="link" href="date-and-time-functions.html#function_year">
    <code class="literal">
     YEAR(date_col)
    </code>
   </a>
   with respect to
   <code class="literal">
    date_col
   </code>
   is not quite as direct as that of
   <a class="link" href="date-and-time-functions.html#function_to-days">
    <code class="literal">
     TO_DAYS(date_col)
    </code>
   </a>
   , because not
        every possible change in
   <code class="literal">
    date_col
   </code>
   produces an
        equivalent change in
   <a class="link" href="date-and-time-functions.html#function_year">
    <code class="literal">
     YEAR(date_col)
    </code>
   </a>
   . Even so,
   <a class="link" href="date-and-time-functions.html#function_year">
    <code class="literal">
     YEAR(date_col)
    </code>
   </a>
   is a good
        candidate for a hashing function, because it varies directly
        with a portion of
   <code class="literal">
    date_col
   </code>
   and there is no
        possible change in
   <code class="literal">
    date_col
   </code>
   that produces a
        disproportionate change in
   <a class="link" href="date-and-time-functions.html#function_year">
    <code class="literal">
     YEAR(date_col)
    </code>
   </a>
   .
  </p>
  <p>
   By way of contrast, suppose that you have a column named
   <code class="literal">
    int_col
   </code>
   whose type is
   <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>
   . Now consider the expression
   <a class="link" href="mathematical-functions.html#function_pow">
    <code class="literal">
     POW(5-int_col,3) + 6
    </code>
   </a>
   . This would
        be a poor choice for a hashing function because a change in the
        value of
   <code class="literal">
    int_col
   </code>
   is not guaranteed to produce
        a proportional change in the value of the expression. Changing
        the value of
   <code class="literal">
    int_col
   </code>
   by a given amount can
        produce widely differing changes in the value of the expression.
        For example, changing
   <code class="literal">
    int_col
   </code>
   from
   <code class="literal">
    5
   </code>
   to
   <code class="literal">
    6
   </code>
   produces a change
        of
   <code class="literal">
    -1
   </code>
   in the value of the expression, but
        changing the value of
   <code class="literal">
    int_col
   </code>
   from
   <code class="literal">
    6
   </code>
   to
   <code class="literal">
    7
   </code>
   produces a change
        of
   <code class="literal">
    -7
   </code>
   in the expression value.
  </p>
  <p>
   In other words, the more closely the graph of the column value
        versus the value of the expression follows a straight line as
        traced by the equation
   <code class="literal">
    y=
    <em class="replaceable">
     <code>
      c
     </code>
    </em>
    x
   </code>
   where
   <em class="replaceable">
    <code>
     c
    </code>
   </em>
   is some nonzero constant, the
        better the expression is suited to hashing. This has to do with
        the fact that the more nonlinear an expression is, the more
        uneven the distribution of data among the partitions it tends to
        produce.
  </p>
  <p>
   In theory, pruning is also possible for expressions involving
        more than one column value, but determining which of such
        expressions are suitable can be quite difficult and
        time-consuming. For this reason, the use of hashing expressions
        involving multiple columns is not particularly recommended.
  </p>
  <p>
   When
   <code class="literal">
    PARTITION BY HASH
   </code>
   is used, the storage
        engine determines which partition of
   <em class="replaceable">
    <code>
     num
    </code>
   </em>
   partitions to use based on the
        modulus of the result of the expression. In other words, for a
        given expression
   <em class="replaceable">
    <code>
     expr
    </code>
   </em>
   , the partition
        in which the record is stored is partition number
   <em class="replaceable">
    <code>
     N
    </code>
   </em>
   , where
   <code class="literal">
    <em class="replaceable">
     <code>
      N
     </code>
    </em>
    =
        MOD(
    <em class="replaceable">
     <code>
      expr
     </code>
    </em>
    ,
    <em class="replaceable">
     <code>
      num
     </code>
    </em>
    )
   </code>
   . Suppose that table
   <code class="literal">
    t1
   </code>
   is defined as follows, so that it has 4
        partitions:
  </p>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa37391553"><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>col1 <span class="token datatype">INT</span><span class="token punctuation">,</span> col2 <span class="token datatype">CHAR</span><span class="token punctuation">(</span><span class="token number">5</span><span class="token punctuation">)</span><span class="token punctuation">,</span> col3 <span class="token datatype">DATE</span><span class="token punctuation">)</span>
    <span class="token keyword">PARTITION</span> <span class="token keyword">BY</span> <span class="token keyword">HASH</span><span class="token punctuation">(</span> <span class="token function">YEAR</span><span class="token punctuation">(</span>col3<span class="token punctuation">)</span> <span class="token punctuation">)</span>
    <span class="token keyword">PARTITIONS</span> <span class="token number">4</span><span class="token punctuation">;</span></code></pre>
  </div>
  <p>
   If you insert a record into
   <code class="literal">
    t1
   </code>
   whose
   <code class="literal">
    col3
   </code>
   value is
   <code class="literal">
    '2005-09-15'
   </code>
   , then the partition in which it
        is stored is determined as follows:
  </p>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa88807730"><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 function">MOD</span><span class="token punctuation">(</span><span class="token function">YEAR</span><span class="token punctuation">(</span><span class="token string">'2005-09-01'</span><span class="token punctuation">)</span><span class="token punctuation">,</span><span class="token number">4</span><span class="token punctuation">)</span>
<span class="token operator">=</span>  <span class="token function">MOD</span><span class="token punctuation">(</span><span class="token number">2005</span><span class="token punctuation">,</span><span class="token number">4</span><span class="token punctuation">)</span>
<span class="token operator">=</span>  <span class="token number">1</span></code></pre>
  </div>
  <p>
   MySQL 8.4 also supports a variant of
   <code class="literal">
    HASH
   </code>
   partitioning known as
   <span class="firstterm">
    linear hashing
   </span>
   which
        employs a more complex algorithm for determining the placement
        of new rows inserted into the partitioned table. See
   <a class="xref" href="partitioning-linear-hash.html" title="26.2.4.1 LINEAR HASH Partitioning">
    Section 26.2.4.1, “LINEAR HASH Partitioning”
   </a>
   , for a description of
        this algorithm.
  </p>
  <p>
   The user-supplied expression is evaluated each time a record is
        inserted or updated. It may also—depending on the
        circumstances—be evaluated when records are deleted.
  </p>
 </div>
 <br/>
</div>
