MySQL: InnoDB Table Compression, How we can Compressed Table?

This article is half-done without your Comment! *** Please share your thoughts via Comment ***

MySQL InnoDB Engine Table Compression, which reduces the size of database size, reduces the CPU I/O and improve overall throughput time.

The compressed data requires the small size of pages, compare to uncompressed data.
MySQL implements compression with the help of the well-known zlib library, which implements the LZ77 compression algorithm.

This compression algorithm is mature, robust, and efficient in both CPU utilization and in a reduction of data size.

Because of compressed data, processors and cache memories increases the speed of execution.
Internally buffer pool fetches the compressed data, uncompressed that data, perform the operation and again write compressed data into Disk.

The default uncompressed size of InnoDB data pages is 16KB.

If innodb_file_per_table variable enable, only we can create compressed table.

For InnoDB system tablespace, we cannot create compressed table because the single InnoDB ibdata1 file also contains other metadata information which we cannot compress.

You can access below articles for more details on InnoDB data files.

MySQL: Why InnoDB Engine stores all databases in one file (ibdata1)?

MySQL: The Truth about InnoDB File Per Table Tablespaces

Mostly, we should enable compression for a read-intensive type of application where RAM frequently uses data.
The default uncompressed size of InnoDB data pages is 16KB and 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).

How to create an InnoDB Compressed Table?
First In my.cnf or my.ini, innodb_file_per_table required to enable and innodb_file_format must be set to Barracuda.

We can create a table with an ROW_FORMAT=COMPRESSED option, which set compressed value half of the innodb_page_size variable.
Explicitly, we can also set a KEY_BLOCK_SIZE value to give a hint.
Here, You can check the size of innodb_page_size. (default 16KB)

Create Sample InnoDB Compress Table (Set to 8KB):