<div id="docs-body">
 <div class="section">
  <div class="titlepage">
   <div>
    <div>
     <h3 class="title">
      <a name="information-schema-partitions-table">
      </a>
      28.3.21 The INFORMATION_SCHEMA PARTITIONS Table
     </h3>
    </div>
   </div>
  </div>
  <a class="indexterm" name="idm46045079385264">
  </a>
  <p>
   The
   <a class="link" href="information-schema-partitions-table.html" title="28.3.21 The INFORMATION_SCHEMA PARTITIONS Table">
    <code class="literal">
     PARTITIONS
    </code>
   </a>
   table provides
      information about table partitions. Each row in this table
      corresponds to an individual partition or subpartition of a
      partitioned table. For more information about partitioning tables,
      see
   <a class="xref" href="partitioning.html" title="Chapter 26 Partitioning">
    Chapter 26,
    <i>
     Partitioning
    </i>
   </a>
   .
  </p>
  <p>
   The
   <a class="link" href="information-schema-partitions-table.html" title="28.3.21 The INFORMATION_SCHEMA PARTITIONS Table">
    <code class="literal">
     PARTITIONS
    </code>
   </a>
   table has these
      columns:
  </p>
  <div class="itemizedlist">
   <ul class="itemizedlist" style="list-style-type: disc; ">
    <li class="listitem">
     <p>
      <code class="literal">
       TABLE_CATALOG
      </code>
     </p>
     <p>
      The name of the catalog to which the table belongs. This value
          is always
      <code class="literal">
       def
      </code>
      .
     </p>
    </li>
    <li class="listitem">
     <p>
      <code class="literal">
       TABLE_SCHEMA
      </code>
     </p>
     <p>
      The name of the schema (database) to which the table belongs.
     </p>
    </li>
    <li class="listitem">
     <p>
      <code class="literal">
       TABLE_NAME
      </code>
     </p>
     <p>
      The name of the table containing the partition.
     </p>
    </li>
    <li class="listitem">
     <p>
      <code class="literal">
       PARTITION_NAME
      </code>
     </p>
     <p>
      The name of the partition.
     </p>
    </li>
    <li class="listitem">
     <p>
      <code class="literal">
       SUBPARTITION_NAME
      </code>
     </p>
     <p>
      If the
      <a class="link" href="information-schema-partitions-table.html" title="28.3.21 The INFORMATION_SCHEMA PARTITIONS Table">
       <code class="literal">
        PARTITIONS
       </code>
      </a>
      table row
          represents a subpartition, the name of subpartition; otherwise
      <code class="literal">
       NULL
      </code>
      .
     </p>
     <p>
      For
      <code class="literal">
       NDB
      </code>
      : This value is always
      <code class="literal">
       NULL
      </code>
      .
     </p>
    </li>
    <li class="listitem">
     <p>
      <code class="literal">
       PARTITION_ORDINAL_POSITION
      </code>
     </p>
     <p>
      All partitions are indexed in the same order as they are
          defined, with
      <code class="literal">
       1
      </code>
      being the number assigned
          to the first partition. The indexing can change as partitions
          are added, dropped, and reorganized; the number shown is this
          column reflects the current order, taking into account any
          indexing changes.
     </p>
    </li>
    <li class="listitem">
     <p>
      <code class="literal">
       SUBPARTITION_ORDINAL_POSITION
      </code>
     </p>
     <p>
      Subpartitions within a given partition are also indexed and
          reindexed in the same manner as partitions are indexed within
          a table.
     </p>
    </li>
    <li class="listitem">
     <p>
      <code class="literal">
       PARTITION_METHOD
      </code>
     </p>
     <p>
      One of the values
      <code class="literal">
       RANGE
      </code>
      ,
      <code class="literal">
       LIST
      </code>
      ,
      <code class="literal">
       HASH
      </code>
      ,
      <code class="literal">
       LINEAR HASH
      </code>
      ,
      <code class="literal">
       KEY
      </code>
      , or
      <code class="literal">
       LINEAR KEY
      </code>
      ; that is, one of the available
          partitioning types as discussed in
      <a class="xref" href="partitioning-types.html" title="26.2 Partitioning Types">
       Section 26.2, “Partitioning Types”
      </a>
      .
     </p>
    </li>
    <li class="listitem">
     <p>
      <code class="literal">
       SUBPARTITION_METHOD
      </code>
     </p>
     <p>
      One of the values
      <code class="literal">
       HASH
      </code>
      ,
      <code class="literal">
       LINEAR
          HASH
      </code>
      ,
      <code class="literal">
       KEY
      </code>
      , or
      <code class="literal">
       LINEAR
          KEY
      </code>
      ; that is, one of the available subpartitioning
          types as discussed in
      <a class="xref" href="partitioning-subpartitions.html" title="26.2.6 Subpartitioning">
       Section 26.2.6, “Subpartitioning”
      </a>
      .
     </p>
    </li>
    <li class="listitem">
     <p>
      <code class="literal">
       PARTITION_EXPRESSION
      </code>
     </p>
     <p>
      The expression for the partitioning function used in the
      <a class="link" href="create-table.html" title="15.1.20 CREATE TABLE Statement">
       <code class="literal">
        CREATE TABLE
       </code>
      </a>
      or
      <a class="link" href="alter-table.html" title="15.1.9 ALTER TABLE Statement">
       <code class="literal">
        ALTER TABLE
       </code>
      </a>
      statement that
          created the table's current partitioning scheme.
     </p>
     <p>
      For example, consider a partitioned table created in the
      <code class="literal">
       test
      </code>
      database using this statement:
     </p>
     <div class="copytoclipboard-wrapper">
      <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa10309431"><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> tp <span class="token punctuation">(</span>
    c1 <span class="token datatype">INT</span><span class="token punctuation">,</span>
    c2 <span class="token datatype">INT</span><span class="token punctuation">,</span>
    c3 <span class="token datatype">VARCHAR</span><span class="token punctuation">(</span><span class="token number">25</span><span class="token punctuation">)</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>c1 <span class="token operator">+</span> c2<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>
      The
      <code class="literal">
       PARTITION_EXPRESSION
      </code>
      column in a
      <a class="link" href="information-schema-partitions-table.html" title="28.3.21 The INFORMATION_SCHEMA PARTITIONS Table">
       <code class="literal">
        PARTITIONS
       </code>
      </a>
      table row for a
          partition from this table displays
      <code class="literal">
       c1 + c2
      </code>
      ,
          as shown here:
     </p>
     <div class="copytoclipboard-wrapper">
      <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa93355282"><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">SELECT</span> <span class="token keyword">DISTINCT</span> PARTITION_EXPRESSION
       <span class="token keyword">FROM</span> INFORMATION_SCHEMA<span class="token punctuation">.</span><span class="token keyword">PARTITIONS</span>
       <span class="token keyword">WHERE</span> <span class="token keyword">TABLE_NAME</span><span class="token operator">=</span><span class="token string">'tp'</span> <span class="token operator">AND</span> TABLE_SCHEMA<span class="token operator">=</span><span class="token string">'test'</span><span class="token punctuation">;</span>
<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>
<span class="token output"><span class="token punctuation">|</span> PARTITION_EXPRESSION <span class="token punctuation">|</span></span>
<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>
<span class="token output"><span class="token punctuation">|</span> c1 <span class="token punctuation">+</span> c2              <span class="token punctuation">|</span></span>
<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></code></pre>
     </div>
     <p>
      For a table that is not explicitly partitioned, this column is
          always
      <code class="literal">
       NULL
      </code>
      , regardless of storage engine.
     </p>
    </li>
    <li class="listitem">
     <p>
      <code class="literal">
       SUBPARTITION_EXPRESSION
      </code>
     </p>
     <p>
      This works in the same fashion for the subpartitioning
          expression that defines the subpartitioning for a table as
      <code class="literal">
       PARTITION_EXPRESSION
      </code>
      does for the
          partitioning expression used to define a table's partitioning.
     </p>
     <p>
      If the table has no subpartitions, this column is
      <code class="literal">
       NULL
      </code>
      .
     </p>
    </li>
    <li class="listitem">
     <p>
      <code class="literal">
       PARTITION_DESCRIPTION
      </code>
     </p>
     <p>
      This column is used for RANGE and LIST partitions. For a
      <code class="literal">
       RANGE
      </code>
      partition, it contains the value set
          in the partition's
      <code class="literal">
       VALUES LESS THAN
      </code>
      clause,
          which can be either an integer or
      <code class="literal">
       MAXVALUE
      </code>
      .
          For a
      <code class="literal">
       LIST
      </code>
      partition, this column contains
          the values defined in the partition's
      <code class="literal">
       VALUES
          IN
      </code>
      clause, which is a list of comma-separated
          integer values.
     </p>
     <p>
      For partitions whose
      <code class="literal">
       PARTITION_METHOD
      </code>
      is
          other than
      <code class="literal">
       RANGE
      </code>
      or
      <code class="literal">
       LIST
      </code>
      , this column is always
      <code class="literal">
       NULL
      </code>
      .
     </p>
    </li>
    <li class="listitem">
     <p>
      <code class="literal">
       TABLE_ROWS
      </code>
     </p>
     <p>
      The number of table rows in the partition.
     </p>
     <p>
      For partitioned
      <a class="link" href="innodb-storage-engine.html" title="Chapter 17 The InnoDB Storage Engine">
       <code class="literal">
        InnoDB
       </code>
      </a>
      tables,
          the row count given in the
      <code class="literal">
       TABLE_ROWS
      </code>
      column is only an estimated value used in SQL optimization,
          and may not always be exact.
     </p>
     <p>
      For
      <a class="link" href="mysql-cluster.html" title="Chapter 25 MySQL NDB Cluster 8.4">
       <code class="literal">
        NDB
       </code>
      </a>
      tables, you can also
          obtain this information using the
      <a class="link" href="mysql-cluster-programs-ndb-desc.html" title="25.5.9 ndb_desc — Describe NDB Tables">
       <span class="command">
        <strong>
         ndb_desc
        </strong>
       </span>
      </a>
      utility.
     </p>
    </li>
    <li class="listitem">
     <p>
      <code class="literal">
       AVG_ROW_LENGTH
      </code>
     </p>
     <p>
      The average length of the rows stored in this partition or
          subpartition, in bytes. This is the same as
      <code class="literal">
       DATA_LENGTH
      </code>
      divided by
      <code class="literal">
       TABLE_ROWS
      </code>
      .
     </p>
     <p>
      For
      <a class="link" href="mysql-cluster.html" title="Chapter 25 MySQL NDB Cluster 8.4">
       <code class="literal">
        NDB
       </code>
      </a>
      tables, you can also
          obtain this information using the
      <a class="link" href="mysql-cluster-programs-ndb-desc.html" title="25.5.9 ndb_desc — Describe NDB Tables">
       <span class="command">
        <strong>
         ndb_desc
        </strong>
       </span>
      </a>
      utility.
     </p>
    </li>
    <li class="listitem">
     <p>
      <code class="literal">
       DATA_LENGTH
      </code>
     </p>
     <p>
      The total length of all rows stored in this partition or
          subpartition, in bytes; that is, the total number of bytes
          stored in the partition or subpartition.
     </p>
     <p>
      For
      <a class="link" href="mysql-cluster.html" title="Chapter 25 MySQL NDB Cluster 8.4">
       <code class="literal">
        NDB
       </code>
      </a>
      tables, you can also
          obtain this information using the
      <a class="link" href="mysql-cluster-programs-ndb-desc.html" title="25.5.9 ndb_desc — Describe NDB Tables">
       <span class="command">
        <strong>
         ndb_desc
        </strong>
       </span>
      </a>
      utility.
     </p>
    </li>
    <li class="listitem">
     <p>
      <code class="literal">
       MAX_DATA_LENGTH
      </code>
     </p>
     <p>
      The maximum number of bytes that can be stored in this
          partition or subpartition.
     </p>
     <p>
      For
      <a class="link" href="mysql-cluster.html" title="Chapter 25 MySQL NDB Cluster 8.4">
       <code class="literal">
        NDB
       </code>
      </a>
      tables, you can also
          obtain this information using the
      <a class="link" href="mysql-cluster-programs-ndb-desc.html" title="25.5.9 ndb_desc — Describe NDB Tables">
       <span class="command">
        <strong>
         ndb_desc
        </strong>
       </span>
      </a>
      utility.
     </p>
    </li>
    <li class="listitem">
     <p>
      <code class="literal">
       INDEX_LENGTH
      </code>
     </p>
     <p>
      The length of the index file for this partition or
          subpartition, in bytes.
     </p>
     <p>
      For partitions of
      <a class="link" href="mysql-cluster.html" title="Chapter 25 MySQL NDB Cluster 8.4">
       <code class="literal">
        NDB
       </code>
      </a>
      tables,
          whether the tables use implicit or explicit partitioning, the
      <code class="literal">
       INDEX_LENGTH
      </code>
      column value is always 0.
          However, you can obtain equivalent information using the
      <a class="link" href="mysql-cluster-programs-ndb-desc.html" title="25.5.9 ndb_desc — Describe NDB Tables">
       <span class="command">
        <strong>
         ndb_desc
        </strong>
       </span>
      </a>
      utility.
     </p>
    </li>
    <li class="listitem">
     <p>
      <code class="literal">
       DATA_FREE
      </code>
     </p>
     <p>
      The number of bytes allocated to the partition or subpartition
          but not used.
     </p>
     <p>
      For
      <a class="link" href="mysql-cluster.html" title="Chapter 25 MySQL NDB Cluster 8.4">
       <code class="literal">
        NDB
       </code>
      </a>
      tables, you can also
          obtain this information using the
      <a class="link" href="mysql-cluster-programs-ndb-desc.html" title="25.5.9 ndb_desc — Describe NDB Tables">
       <span class="command">
        <strong>
         ndb_desc
        </strong>
       </span>
      </a>
      utility.
     </p>
    </li>
    <li class="listitem">
     <p>
      <code class="literal">
       CREATE_TIME
      </code>
     </p>
     <p>
      The time that the partition or subpartition was created.
     </p>
    </li>
    <li class="listitem">
     <p>
      <code class="literal">
       UPDATE_TIME
      </code>
     </p>
     <p>
      The time that the partition or subpartition was last modified.
     </p>
    </li>
    <li class="listitem">
     <p>
      <code class="literal">
       CHECK_TIME
      </code>
     </p>
     <p>
      The last time that the table to which this partition or
          subpartition belongs was checked.
     </p>
     <p>
      For partitioned
      <a class="link" href="innodb-storage-engine.html" title="Chapter 17 The InnoDB Storage Engine">
       <code class="literal">
        InnoDB
       </code>
      </a>
      tables,
          the value is always
      <code class="literal">
       NULL
      </code>
      .
     </p>
    </li>
    <li class="listitem">
     <p>
      <code class="literal">
       CHECKSUM
      </code>
     </p>
     <p>
      The checksum value, if any; otherwise
      <code class="literal">
       NULL
      </code>
      .
     </p>
    </li>
    <li class="listitem">
     <p>
      <code class="literal">
       PARTITION_COMMENT
      </code>
     </p>
     <p>
      The text of the comment, if the partition has one. If not,
          this value is empty.
     </p>
     <p>
      The maximum length for a partition comment is defined as 1024
          characters, and the display width of the
      <code class="literal">
       PARTITION_COMMENT
      </code>
      column is also 1024,
          characters to match this limit.
     </p>
    </li>
    <li class="listitem">
     <p>
      <code class="literal">
       NODEGROUP
      </code>
     </p>
     <p>
      This is the nodegroup to which the partition belongs. For NDB
          Cluster tables, this is always
      <code class="literal">
       default
      </code>
      . For
          partitioned tables using storage engines other than
      <a class="link" href="mysql-cluster.html" title="Chapter 25 MySQL NDB Cluster 8.4">
       <code class="literal">
        NDB
       </code>
      </a>
      , the value is also
      <code class="literal">
       default
      </code>
      . Otherwise, this column is empty.
     </p>
    </li>
    <li class="listitem">
     <p>
      <code class="literal">
       TABLESPACE_NAME
      </code>
     </p>
     <p>
      The name of the tablespace to which the partition belongs. The
          value is always
      <code class="literal">
       DEFAULT
      </code>
      , unless the table
          uses the
      <code class="literal">
       NDB
      </code>
      storage engine (see the
      <em class="citetitle">
       Notes
      </em>
      at the end of this section).
     </p>
    </li>
   </ul>
  </div>
  <h4>
   <a name="idm46045079263216">
   </a>
   Notes
  </h4>
  <div class="itemizedlist">
   <ul class="itemizedlist" style="list-style-type: disc; ">
    <li class="listitem">
     <p>
      <a class="link" href="information-schema-partitions-table.html" title="28.3.21 The INFORMATION_SCHEMA PARTITIONS Table">
       <code class="literal">
        PARTITIONS
       </code>
      </a>
      is a nonstandard
      <code class="literal">
       INFORMATION_SCHEMA
      </code>
      table.
     </p>
    </li>
    <li class="listitem">
     <p>
      A table using any storage engine other than
      <a class="link" href="mysql-cluster.html" title="Chapter 25 MySQL NDB Cluster 8.4">
       <code class="literal">
        NDB
       </code>
      </a>
      and which is not partitioned
          has one row in the
      <a class="link" href="information-schema-partitions-table.html" title="28.3.21 The INFORMATION_SCHEMA PARTITIONS Table">
       <code class="literal">
        PARTITIONS
       </code>
      </a>
      table. However, the values of the
      <code class="literal">
       PARTITION_NAME
      </code>
      ,
      <code class="literal">
       SUBPARTITION_NAME
      </code>
      ,
      <code class="literal">
       PARTITION_ORDINAL_POSITION
      </code>
      ,
      <code class="literal">
       SUBPARTITION_ORDINAL_POSITION
      </code>
      ,
      <code class="literal">
       PARTITION_METHOD
      </code>
      ,
      <code class="literal">
       SUBPARTITION_METHOD
      </code>
      ,
      <code class="literal">
       PARTITION_EXPRESSION
      </code>
      ,
      <code class="literal">
       SUBPARTITION_EXPRESSION
      </code>
      , and
      <code class="literal">
       PARTITION_DESCRIPTION
      </code>
      columns are all
      <code class="literal">
       NULL
      </code>
      . Also, the
      <code class="literal">
       PARTITION_COMMENT
      </code>
      column in this case is
          blank.
     </p>
    </li>
    <li class="listitem">
     <p>
      An
      <code class="literal">
       NDB
      </code>
      table which is not explicitly
          partitioned has one row in the
      <code class="literal">
       PARTITIONS
      </code>
      table for each data node in the NDB cluster. For each such
          row:
     </p>
     <div class="itemizedlist">
      <ul class="itemizedlist" style="list-style-type: circle; ">
       <li class="listitem">
        <p>
         The
         <code class="literal">
          SUBPARTITION_NAME
         </code>
         ,
         <code class="literal">
          SUBPARTITION_ORDINAL_POSITION
         </code>
         ,
         <code class="literal">
          SUBPARTITION_METHOD
         </code>
         ,
         <code class="literal">
          PARTITION_EXPRESSION
         </code>
         ,
         <code class="literal">
          SUBPARTITION_EXPRESSION
         </code>
         ,
         <code class="literal">
          CREATE_TIME
         </code>
         ,
         <code class="literal">
          UPDATE_TIME
         </code>
         ,
         <code class="literal">
          CHECK_TIME
         </code>
         ,
         <code class="literal">
          CHECKSUM
         </code>
         , and
         <code class="literal">
          TABLESPACE_NAME
         </code>
         columns are all
         <code class="literal">
          NULL
         </code>
         .
        </p>
       </li>
       <li class="listitem">
        <p>
         The
         <code class="literal">
          PARTITION_METHOD
         </code>
         is always
         <code class="literal">
          AUTO
         </code>
         .
        </p>
       </li>
       <li class="listitem">
        <p>
         The
         <code class="literal">
          NODEGROUP
         </code>
         column is
         <code class="literal">
          default
         </code>
         .
        </p>
       </li>
       <li class="listitem">
        <p>
         The
         <code class="literal">
          PARTITION_COMMENT
         </code>
         column is empty.
        </p>
       </li>
      </ul>
     </div>
    </li>
   </ul>
  </div>
 </div>
 <br/>
</div>
