MySQL 8 Cookbook

Enabling Compression for file_per_table Tables

1. Make sure that file_per_table is enabled:

mysql> SET GLOBAL innodb_file_per_table=1;

2. Specify the ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=8 in the create statement:

mysql> CREATE TABLE compressed_table (id INT PRIMARY KEY) ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=8;Query OK, 0 rows affected (0.07 sec)

If the table already exists, you can execute ALTER:

mysql> ALTER TABLE event_history ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=8;Query OK, 0 rows affected (0.67 sec)Records: 0  Duplicates: 0  Warnings: 0

If you try to compress a table that is in the system tablespace, you will get an error:

mysql> ALTER TABLE employees ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=8;ERROR 1478 (HY000): InnoDB: Tablespace `innodb_system` ...