O'Reilly logo

MySQL 8 Cookbook by Karthik Appigatla

Stay ahead with the world's most comprehensive technology and business learning platform.

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, tutorials, and more.

Start Free Trial

No credit card required

Enabling Compression for file_per_table Tables

  1. Make sure that file_per_table is enabled:
mysql> SET GLOBAL innodb_file_per_table=1;
  1. 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` ...

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, interactive tutorials, and more.

Start Free Trial

No credit card required