Documentation Home
MySQL 9.4 Reference Manual
Related Documentation Download this Manual
PDF (US Ltr) - 41.2Mb
PDF (A4) - 41.3Mb
Man Pages (TGZ) - 262.8Kb
Man Pages (Zip) - 368.8Kb
Info (Gzip) - 4.1Mb
Info (Zip) - 4.1Mb


17.9.1.2 Creating Compressed Tables

Compressed tables can be created infile-per-table tablespaces or ingeneral tablespaces. Table compression is not available for the InnoDBsystem tablespace. The system tablespace (space 0, the.ibdata files) 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.

Creating a Compressed Table in File-Per-Table Tablespace

To create a compressed table in a file-per-table tablespace,innodb_file_per_table must be enabled (the default). You can set this parameter in the MySQL configuration file (my.cnf ormy.ini) or dynamically, using aSET statement.

After theinnodb_file_per_table option is configured, specify theROW_FORMAT=COMPRESSED clause orKEY_BLOCK_SIZE clause, or both, in aCREATE TABLE orALTER TABLE statement to create a compressed table in a file-per-table tablespace.

For example, you might use the following statements:

SET GLOBAL innodb_file_per_table=1;CREATE TABLE t1 (c1 INT PRIMARY KEY) ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=8;
Creating a Compressed Table in a General Tablespace

To create a compressed table in a general tablespace,FILE_BLOCK_SIZE must be defined for the general tablespace, which is specified when the tablespace is created. TheFILE_BLOCK_SIZE value must be a valid compressed page size in relation to theinnodb_page_size value, and the page size of the compressed table, defined by theCREATE TABLE orALTER TABLEKEY_BLOCK_SIZE clause, must be equal toFILE_BLOCK_SIZE/1024. For example, ifinnodb_page_size=16384 andFILE_BLOCK_SIZE=8192, theKEY_BLOCK_SIZE of the table must be 8. For more information, seeSection 17.6.3.3, “General Tablespaces”.

The following example demonstrates creating a general tablespace and adding a compressed table. The example assumes a defaultinnodb_page_size of 16K. TheFILE_BLOCK_SIZE of 8192 requires that the compressed table have aKEY_BLOCK_SIZE of 8.

mysql> CREATE TABLESPACE `ts2` ADD DATAFILE 'ts2.ibd' FILE_BLOCK_SIZE = 8192 Engine=InnoDB;mysql> CREATE TABLE t4 (c1 INT PRIMARY KEY) TABLESPACE ts2 ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=8;
Notes

  • As of MySQL 9.4, the tablespace file for a compressed table is created using the physical page size instead of theInnoDB page size, which makes the initial size of a tablespace file for an empty compressed table smaller than in previous MySQL releases.

  • If you specifyROW_FORMAT=COMPRESSED, you can omitKEY_BLOCK_SIZE; theKEY_BLOCK_SIZE setting defaults to half theinnodb_page_size value.

  • If you specify a validKEY_BLOCK_SIZE value, you can omitROW_FORMAT=COMPRESSED; compression is enabled automatically.

  • To determine the best value forKEY_BLOCK_SIZE, typically you create several copies of the same table with different values for this clause, then measure the size of the resulting.ibd files and see how well each performs with a realisticworkload. For general tablespaces, keep in mind that dropping a table does not reduce the size of the general tablespace.ibd file, nor does it return disk space to the operating system. For more information, seeSection 17.6.3.3, “General Tablespaces”.

  • TheKEY_BLOCK_SIZE value is treated as a hint; a different size could be used byInnoDB if necessary. For file-per-table tablespaces, theKEY_BLOCK_SIZE can only be less than or equal to theinnodb_page_size value. If you specify a value greater than theinnodb_page_size value, the specified value is ignored, a warning is issued, andKEY_BLOCK_SIZE is set to half of theinnodb_page_size value. Ifinnodb_strict_mode=ON, specifying an invalidKEY_BLOCK_SIZE value returns an error. For general tablespaces, validKEY_BLOCK_SIZE values depend on theFILE_BLOCK_SIZE setting of the tablespace. For more information, seeSection 17.6.3.3, “General Tablespaces”.

  • InnoDB supports 32KB and 64KB page sizes but these page sizes do not support compression. For more information, refer to theinnodb_page_size documentation.

  • The default uncompressed size ofInnoDB datapages 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 (.ibd file). The actual compression algorithm is not affected by theKEY_BLOCK_SIZE value; the value determines how large each compressed chunk is, which in turn affects how many rows can be packed into each compressed page.

  • When creating a compressed table in a file-per-table tablespace, settingKEY_BLOCK_SIZE equal to theInnoDBpage size does not typically result in much compression. For example, settingKEY_BLOCK_SIZE=16 typically would not result in much compression, since the normalInnoDB page size is 16KB. This setting may still be useful for tables with many longBLOB,VARCHAR orTEXT columns, because such values often do compress well, and might therefore require feweroverflow pages as described inSection 17.9.1.5, “How Compression Works for InnoDB Tables”. For general tablespaces, aKEY_BLOCK_SIZE value equal to theInnoDB page size is not permitted. For more information, seeSection 17.6.3.3, “General Tablespaces”.

  • All indexes of a table (including theclustered index) are compressed using the same page size, as specified in theCREATE TABLE orALTER TABLE statement. Table attributes such asROW_FORMAT andKEY_BLOCK_SIZE are not part of theCREATE INDEX syntax forInnoDB tables, and are ignored if they are specified (although, if specified, they appear in the output of theSHOW CREATE TABLE statement).

  • For performance-related configuration options, seeSection 17.9.1.3, “Tuning Compression for InnoDB Tables”.

Restrictions on Compressed Tables

  • Compressed tables cannot be stored in theInnoDB system tablespace.

  • General tablespaces can contain multiple tables, but compressed and uncompressed tables cannot coexist within the same general tablespace.

  • Compression applies to an entire table and all its associated indexes, not to individual rows, despite the clause nameROW_FORMAT.

  • InnoDB does not support compressed temporary tables. Wheninnodb_strict_mode is enabled (the default),CREATE TEMPORARY TABLE returns errors ifROW_FORMAT=COMPRESSED orKEY_BLOCK_SIZE is specified. Ifinnodb_strict_mode is disabled, warnings are issued and the temporary table is created using a non-compressed row format. The same restrictions apply toALTER TABLE operations on temporary tables.