InnoDB Table Compression Creating a Compressed Table in File-Per-Table Tablespace Creating a Compressed Table in a General Tablespace Notes 3148 •The  KEY_BLOCK_SIZE  value is treated as a hint; a different size could be used by  InnoDB  if necessary. For file-per-table tablespaces, the  KEY_BLOCK_SIZE  can only be less than or equal to the  innodb_page_size  value. If you specify a value greater than the  innodb_page_size value, the specified value is ignored, a warning is issued, and  KEY_BLOCK_SIZE  is set to half of the •To determine the best value for  KEY_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 realistic workload. 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, see Section 15.6.3.3, “General Tablespaces” . •If you specify a valid  KEY_BLOCK_SIZE  value, you can omit  ROW_FORMAT=COMPRESSED ; compression is enabled automatically. •If you specify  ROW_FORMAT=COMPRESSED , you can omit  KEY_BLOCK_SIZE ; the  KEY_BLOCK_SIZE setting defaults to half the  innodb_page_size  value. •As of MySQL 8.0, the tablespace file for a compressed table is created using the physical page size instead of the  InnoDB  page size, which makes the initial size of a tablespace file for an empty compressed table smaller than in previous MySQL releases. 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  or  my.ini ) or dynamically, using a  SET  statement. After the  innodb_file_per_table  option is configured, specify the  ROW_FORMAT=COMPRESSED clause or  KEY_BLOCK_SIZE  clause, or both, in a  CREATE TABLE  or  ALTER TABLE  statement to create a compressed table in a file-per-table tablespace. For example, you might use the following statements: 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. The  FILE_BLOCK_SIZE  value must be a valid compressed page size in relation to the  innodb_page_size  value, and the page size of the compressed table, defined by the  CREATE TABLE  or  ALTER TABLE KEY_BLOCK_SIZE clause, must be equal to  FILE_BLOCK_SIZE/1024 . For example, if  innodb_page_size=16384  and FILE_BLOCK_SIZE=8192 , the  KEY_BLOCK_SIZE  of the table must be 8. For more information, see Section 15.6.3.3, “General Tablespaces” . The following example demonstrates creating a general tablespace and adding a compressed table. The example assumes a default  innodb_page_size  of 16K. The  FILE_BLOCK_SIZE  of 8192 requires that the compressed table have a  KEY_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; SET GLOBAL innodb_file_per_table=1; CREATE TABLE t1  (c1 INT PRIMARY KEY)  ROW_FORMAT=COMPRESSED  KEY_BLOCK_SIZE=8;