<div id="docs-body">
 <div class="section">
  <div class="titlepage">
   <div>
    <div>
     <h4 class="title">
      <a name="innodb-compression-usage">
      </a>
      17.9.1.2 Creating Compressed Tables
     </h4>
    </div>
   </div>
  </div>
  <a class="indexterm" name="idm46045161724704">
  </a>
  <a class="indexterm" name="idm46045161723216">
  </a>
  <a class="indexterm" name="idm46045161722144">
  </a>
  <a class="indexterm" name="idm46045161720656">
  </a>
  <a class="indexterm" name="idm46045161719584">
  </a>
  <p>
   Compressed tables can be created in
   <a class="link" href="glossary.html#glos_file_per_table" title="file-per-table">
    file-per-table
   </a>
   tablespaces or in
   <a class="link" href="glossary.html#glos_general_tablespace" title="general tablespace">
    general
        tablespaces
   </a>
   . Table compression is not available for the
        InnoDB
   <a class="link" href="glossary.html#glos_system_tablespace" title="system tablespace">
    system
        tablespace
   </a>
   . The system tablespace (space 0, the
   <a class="link" href="glossary.html#glos_ibd_file" title=".ibd file">
    .ibdata files
   </a>
   ) can contain
        user-created tables, but it also contains internal system data,
        which is never compressed. Thus, compression applies only to
        tables (and indexes) stored in file-per-table or general
        tablespaces.
  </p>
  <h5>
   <a name="idm46045161713952">
   </a>
   Creating a Compressed Table in File-Per-Table Tablespace
  </h5>
  <p>
   To create a compressed table in a file-per-table tablespace,
   <a class="link" href="innodb-parameters.html#sysvar_innodb_file_per_table">
    <code class="literal">
     innodb_file_per_table
    </code>
   </a>
   must be
        enabled (the default). You can set this parameter in the MySQL
        configuration file (
   <code class="filename">
    my.cnf
   </code>
   or
   <code class="filename">
    my.ini
   </code>
   ) or dynamically, using a
   <a class="link" href="set-variable.html" title="15.7.6.1 SET Syntax for Variable Assignment">
    <code class="literal">
     SET
    </code>
   </a>
   statement.
  </p>
  <p>
   After the
   <a class="link" href="innodb-parameters.html#sysvar_innodb_file_per_table">
    <code class="literal">
     innodb_file_per_table
    </code>
   </a>
   option is configured, specify the
   <code class="literal">
    ROW_FORMAT=COMPRESSED
   </code>
   clause or
   <code class="literal">
    KEY_BLOCK_SIZE
   </code>
   clause, or both, in a
   <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 to create a
        compressed table in a file-per-table tablespace.
  </p>
  <p>
   For example, you might use the following statements:
  </p>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa65922435"><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> <span class="token keyword">GLOBAL</span> innodb_file_per_table<span class="token operator">=</span><span class="token number">1</span><span class="token punctuation">;</span>
<span class="token keyword">CREATE</span> <span class="token keyword">TABLE</span> t1
 <span class="token punctuation">(</span>c1 <span class="token datatype">INT</span> <span class="token keyword">PRIMARY</span> <span class="token keyword">KEY</span><span class="token punctuation">)</span>
 <span class="token keyword">ROW_FORMAT</span><span class="token operator">=</span><span class="token keyword">COMPRESSED</span>
 <span class="token keyword">KEY_BLOCK_SIZE</span><span class="token operator">=</span><span class="token number">8</span><span class="token punctuation">;</span></code></pre>
  </div>
  <h5>
   <a name="idm46045161701472">
   </a>
   Creating a Compressed Table in a General Tablespace
  </h5>
  <p>
   To create a compressed table in a general tablespace,
   <code class="literal">
    FILE_BLOCK_SIZE
   </code>
   must be defined for the
        general tablespace, which is specified when the tablespace is
        created. The
   <code class="literal">
    FILE_BLOCK_SIZE
   </code>
   value must be a
        valid compressed page size in relation to the
   <a class="link" href="innodb-parameters.html#sysvar_innodb_page_size">
    <code class="literal">
     innodb_page_size
    </code>
   </a>
   value, and the
        page size of the compressed table, defined by 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>
   <code class="literal">
    KEY_BLOCK_SIZE
   </code>
   clause, must be equal to
   <code class="literal">
    FILE_BLOCK_SIZE/1024
   </code>
   . For example, if
   <a class="link" href="innodb-parameters.html#sysvar_innodb_page_size">
    <code class="literal">
     innodb_page_size=16384
    </code>
   </a>
   and
   <code class="literal">
    FILE_BLOCK_SIZE=8192
   </code>
   , the
   <code class="literal">
    KEY_BLOCK_SIZE
   </code>
   of the table must be 8. For
        more information, see
   <a class="xref" href="general-tablespaces.html" title="17.6.3.3 General Tablespaces">
    Section 17.6.3.3, “General Tablespaces”
   </a>
   .
  </p>
  <p>
   The following example demonstrates creating a general tablespace
        and adding a compressed table. The example assumes a default
   <a class="link" href="innodb-parameters.html#sysvar_innodb_page_size">
    <code class="literal">
     innodb_page_size
    </code>
   </a>
   of 16K. The
   <code class="literal">
    FILE_BLOCK_SIZE
   </code>
   of 8192 requires that the
        compressed table have a
   <code class="literal">
    KEY_BLOCK_SIZE
   </code>
   of 8.
  </p>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa5060560"><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">CREATE</span> <span class="token keyword">TABLESPACE</span> <span class="token punctuation">`</span>ts2<span class="token punctuation">`</span> <span class="token keyword">ADD</span> <span class="token keyword">DATAFILE</span> <span class="token string">'ts2.ibd'</span> <span class="token keyword">FILE_BLOCK_SIZE</span> <span class="token operator">=</span> <span class="token number">8192</span> <span class="token keyword">Engine</span><span class="token operator">=</span>InnoDB<span class="token punctuation">;</span>

<span class="token prompt">mysql&gt;</span> <span class="token keyword">CREATE</span> <span class="token keyword">TABLE</span> t4 <span class="token punctuation">(</span>c1 <span class="token datatype">INT</span> <span class="token keyword">PRIMARY</span> <span class="token keyword">KEY</span><span class="token punctuation">)</span> <span class="token keyword">TABLESPACE</span> ts2 <span class="token keyword">ROW_FORMAT</span><span class="token operator">=</span><span class="token keyword">COMPRESSED</span> <span class="token keyword">KEY_BLOCK_SIZE</span><span class="token operator">=</span><span class="token number">8</span><span class="token punctuation">;</span></code></pre>
  </div>
  <h5>
   <a name="idm46045161684320">
   </a>
   Notes
  </h5>
  <div class="itemizedlist">
   <ul class="itemizedlist" style="list-style-type: disc; ">
    <li class="listitem">
     <p>
      As of MySQL 8.4, the tablespace file for a
            compressed table is created using the physical page size
            instead of the
      <code class="literal">
       InnoDB
      </code>
      page size, which
            makes the initial size of a tablespace file for an empty
            compressed table smaller than in previous MySQL releases.
     </p>
    </li>
    <li class="listitem">
     <p>
      If you specify
      <code class="literal">
       ROW_FORMAT=COMPRESSED
      </code>
      , you
            can omit
      <code class="literal">
       KEY_BLOCK_SIZE
      </code>
      ; the
      <code class="literal">
       KEY_BLOCK_SIZE
      </code>
      setting defaults to half
            the
      <a class="link" href="innodb-parameters.html#sysvar_innodb_page_size">
       <code class="literal">
        innodb_page_size
       </code>
      </a>
      value.
     </p>
    </li>
    <li class="listitem">
     <p>
      If you specify a valid
      <code class="literal">
       KEY_BLOCK_SIZE
      </code>
      value, you can omit
      <code class="literal">
       ROW_FORMAT=COMPRESSED
      </code>
      ; compression is
            enabled automatically.
     </p>
    </li>
    <li class="listitem">
     <p>
      To determine the best value for
      <code class="literal">
       KEY_BLOCK_SIZE,
      </code>
      typically you create
            several copies of the same table with different values for
            this clause, then measure the size of the resulting
      <code class="filename">
       .ibd
      </code>
      files and see how well each
            performs with a realistic
      <a class="link" href="glossary.html#glos_workload" title="workload">
       workload
      </a>
      . For general
            tablespaces, keep in mind that dropping a table does not
            reduce the size of the general tablespace
      <code class="filename">
       .ibd
      </code>
      file, nor does it return disk
            space to the operating system. For more information, see
      <a class="xref" href="general-tablespaces.html" title="17.6.3.3 General Tablespaces">
       Section 17.6.3.3, “General Tablespaces”
      </a>
      .
     </p>
    </li>
    <li class="listitem">
     <p>
      The
      <code class="literal">
       KEY_BLOCK_SIZE
      </code>
      value is treated as a
            hint; a different size could be used by
      <code class="literal">
       InnoDB
      </code>
      if necessary. For file-per-table
            tablespaces, the
      <code class="literal">
       KEY_BLOCK_SIZE
      </code>
      can only
            be less than or equal to the
      <a class="link" href="innodb-parameters.html#sysvar_innodb_page_size">
       <code class="literal">
        innodb_page_size
       </code>
      </a>
      value. If
            you specify a value greater than the
      <a class="link" href="innodb-parameters.html#sysvar_innodb_page_size">
       <code class="literal">
        innodb_page_size
       </code>
      </a>
      value, the
            specified value is ignored, a warning is issued, and
      <code class="literal">
       KEY_BLOCK_SIZE
      </code>
      is set to half of the
      <a class="link" href="innodb-parameters.html#sysvar_innodb_page_size">
       <code class="literal">
        innodb_page_size
       </code>
      </a>
      value. If
      <code class="literal">
       innodb_strict_mode=ON
      </code>
      , specifying an
            invalid
      <code class="literal">
       KEY_BLOCK_SIZE
      </code>
      value returns an
            error. For general tablespaces, valid
      <code class="literal">
       KEY_BLOCK_SIZE
      </code>
      values depend on the
      <code class="literal">
       FILE_BLOCK_SIZE
      </code>
      setting of the
            tablespace. For more information, see
      <a class="xref" href="general-tablespaces.html" title="17.6.3.3 General Tablespaces">
       Section 17.6.3.3, “General Tablespaces”
      </a>
      .
     </p>
    </li>
    <li class="listitem">
     <p>
      <code class="literal">
       InnoDB
      </code>
      supports 32KB and 64KB page sizes
            but these page sizes do not support compression. For more
            information, refer to the
      <a class="link" href="innodb-parameters.html#sysvar_innodb_page_size">
       <code class="literal">
        innodb_page_size
       </code>
      </a>
      documentation.
     </p>
    </li>
    <li class="listitem">
     <p>
      The default uncompressed size of
      <code class="literal">
       InnoDB
      </code>
      data
      <a class="link" href="glossary.html#glos_page" title="page">
       pages
      </a>
      is 16KB.
            Depending on the combination of option values, MySQL uses a
            page size of 1KB, 2KB, 4KB, 8KB, or 16KB for the tablespace
            data file (
      <code class="filename">
       .ibd
      </code>
      file). The actual
            compression algorithm is not affected by the
      <code class="literal">
       KEY_BLOCK_SIZE
      </code>
      value; the value
            determines how large each compressed chunk is, which in turn
            affects how many rows can be packed into each compressed
            page.
     </p>
    </li>
    <li class="listitem">
     <p>
      When creating a compressed table in a file-per-table
            tablespace, setting
      <code class="literal">
       KEY_BLOCK_SIZE
      </code>
      equal
            to the
      <code class="literal">
       InnoDB
      </code>
      <a class="link" href="glossary.html#glos_page_size" title="page size">
       page size
      </a>
      does not
            typically result in much compression. For example, setting
      <code class="literal">
       KEY_BLOCK_SIZE=16
      </code>
      typically would not
            result in much compression, since the normal
      <code class="literal">
       InnoDB
      </code>
      page size is 16KB. This setting
            may still be useful for tables with many long
      <a class="link" href="blob.html" title="13.3.4 The BLOB and TEXT Types">
       <code class="literal">
        BLOB
       </code>
      </a>
      ,
      <a class="link" href="char.html" title="13.3.2 The CHAR and VARCHAR Types">
       <code class="literal">
        VARCHAR
       </code>
      </a>
      or
      <a class="link" href="blob.html" title="13.3.4 The BLOB and TEXT Types">
       <code class="literal">
        TEXT
       </code>
      </a>
      columns, because such
            values often do compress well, and might therefore require
            fewer
      <a class="link" href="glossary.html#glos_overflow_page" title="overflow page">
       overflow
            pages
      </a>
      as described in
      <a class="xref" href="innodb-compression-internals.html" title="17.9.1.5 How Compression Works for InnoDB Tables">
       Section 17.9.1.5, “How Compression Works for InnoDB Tables”
      </a>
      . For general
            tablespaces, a
      <code class="literal">
       KEY_BLOCK_SIZE
      </code>
      value equal
            to the
      <code class="literal">
       InnoDB
      </code>
      page size is not permitted.
            For more information, see
      <a class="xref" href="general-tablespaces.html" title="17.6.3.3 General Tablespaces">
       Section 17.6.3.3, “General Tablespaces”
      </a>
      .
     </p>
    </li>
    <li class="listitem">
     <p>
      All indexes of a table (including the
      <a class="link" href="glossary.html#glos_clustered_index" title="clustered index">
       clustered index
      </a>
      )
            are compressed using the same page size, as specified in the
      <code class="literal">
       CREATE TABLE
      </code>
      or
      <code class="literal">
       ALTER
            TABLE
      </code>
      statement. Table attributes such as
      <code class="literal">
       ROW_FORMAT
      </code>
      and
      <code class="literal">
       KEY_BLOCK_SIZE
      </code>
      are not part of the
      <code class="literal">
       CREATE INDEX
      </code>
      syntax for
      <code class="literal">
       InnoDB
      </code>
      tables, and are ignored if they
            are specified (although, if specified, they appear in the
            output of the
      <a class="link" href="show-create-table.html" title="15.7.7.11 SHOW CREATE TABLE Statement">
       <code class="literal">
        SHOW CREATE
            TABLE
       </code>
      </a>
      statement).
     </p>
    </li>
    <li class="listitem">
     <p>
      For performance-related configuration options, see
      <a class="xref" href="innodb-compression-tuning.html" title="17.9.1.3 Tuning Compression for InnoDB Tables">
       Section 17.9.1.3, “Tuning Compression for InnoDB Tables”
      </a>
      .
     </p>
    </li>
   </ul>
  </div>
  <h5>
   <a name="idm46045161629520">
   </a>
   Restrictions on Compressed Tables
  </h5>
  <div class="itemizedlist">
   <ul class="itemizedlist" style="list-style-type: disc; ">
    <li class="listitem">
     <p>
      Compressed tables cannot be stored in the
      <code class="literal">
       InnoDB
      </code>
      system tablespace.
     </p>
    </li>
    <li class="listitem">
     <p>
      General tablespaces can contain multiple tables, but
            compressed and uncompressed tables cannot coexist within the
            same general tablespace.
     </p>
    </li>
    <li class="listitem">
     <p>
      Compression applies to an entire table and all its
            associated indexes, not to individual rows, despite the
            clause name
      <code class="literal">
       ROW_FORMAT
      </code>
      .
     </p>
    </li>
    <li class="listitem">
     <p>
      <code class="literal">
       InnoDB
      </code>
      does not support compressed
            temporary tables. When
      <a class="link" href="innodb-parameters.html#sysvar_innodb_strict_mode">
       <code class="literal">
        innodb_strict_mode
       </code>
      </a>
      is
            enabled (the default),
      <a class="link" href="create-table.html" title="15.1.20 CREATE TABLE Statement">
       <code class="literal">
        CREATE
            TEMPORARY TABLE
       </code>
      </a>
      returns errors if
      <code class="literal">
       ROW_FORMAT=COMPRESSED
      </code>
      or
      <code class="literal">
       KEY_BLOCK_SIZE
      </code>
      is specified. If
      <a class="link" href="innodb-parameters.html#sysvar_innodb_strict_mode">
       <code class="literal">
        innodb_strict_mode
       </code>
      </a>
      is
            disabled, warnings are issued and the temporary table is
            created using a non-compressed row format. The same
            restrictions apply to
      <a class="link" href="alter-table.html" title="15.1.9 ALTER TABLE Statement">
       <code class="literal">
        ALTER
            TABLE
       </code>
      </a>
      operations on temporary tables.
     </p>
    </li>
   </ul>
  </div>
 </div>
 <br/>
</div>
