Understanding MySQL Compressed Tables: Concepts, Benefits, Limitations, and Practical Usage

1. Concept – A compressed table is a table whose data and optionally indexes are stored using a compression algorithm, resulting in a smaller on‑disk footprint.

1.1 Products with strong compression – Column‑store databases such as Greenplum, Inforbright, and Percona’s TokuDB demonstrate high compression capabilities similar to what MySQL offers.

1.2 Why use compressed tables? – They reduce disk usage dramatically (e.g., compressing a 100 GB table to 10 GB allows ten times more tables on the same storage) and can eliminate the need for MySQL’s double‑write buffer when the table page size matches the OS block size.

1.3 Advantages – Smaller disk I/O, reduced network transfer, and lower memory pressure.

1.4 Disadvantages – Write operations (INSERT, UPDATE, DELETE) consume more CPU because data must be decompressed and recompressed.

1.5 Compression algorithms supported by MySQL – MySQL supports zlib , zstd , and uncompressed . The default is zlib . You can query the supported algorithms with:

mysql> SELECT @@protocol_compression_algorithms;

To check the compiled zlib version:

mysql> SELECT @@version_compile_zlib;

2. How to use compressed tables in MySQL

2.1 MyISAM engine (field‑level compression)

Create a MyISAM table and compress it with myisampack :

mysql> CREATE TABLE n1(id INT, r1 TEXT, r2 TEXT, KEY idx_id(id), KEY idx_r1(r1(10))) ENGINE=MyISAM;

After inserting data, check file sizes, then run:

myisampack n1 -v

Rebuild the index:

myisamchk -rq n1

The data size drops from 116 M to 31 M, a four‑fold reduction, making MyISAM compression suitable for read‑only workloads.

2.2 InnoDB engine (page‑level compression)

InnoDB compresses both data and indexes. Page sizes can be 1K, 2K, 4K, 8K, 16K, 32K, or 64K (default 16K). Compression is enabled by setting ROW_FORMAT=COMPRESSED or specifying KEY_BLOCK_SIZE .

mysql> CREATE TABLE t1(id INT PRIMARY KEY, r1 VARCHAR(200), r2 TEXT);

Change the row format:

ALTER TABLE t1 ROW_FORMAT=COMPRESSED;

The file size shrinks from ~22 M to ~10 M (≈50 % compression).

You can create tables with different page sizes in the same tablespace by matching the tablespace FILE_BLOCK_SIZE :

CREATE TABLESPACE ytt_ts1 ADD DATAFILE 'ytt_ts1.ibd' FILE_BLOCK_SIZE=4K;

Attempting to place a table with an 8K page size into this 4K tablespace fails:

ERROR 1478 (HY000): InnoDB: Tablespace `ytt_ts1` uses block size 4096 and cannot contain a table with physical page size 8192

3. Impact on B‑tree pages and InnoDB Buffer Pool

Each compressed B‑tree page stores at least one record and includes a 16 K modification log to avoid full‑page decompression on every write. When the log fills, the page is recompressed.

In the Buffer Pool, both the compressed page and its uncompressed counterpart may coexist, allowing reads to avoid unnecessary decompression.

4. Limitations

System tablespaces do not support compression.

General tablespaces cannot mix compressed and uncompressed tables.

ROW_FORMAT=COMPRESSED applies to the whole table and its indexes, not just individual rows.

Temporary tables cannot be created with compressed row format.

Example of the temporary‑table restriction:

CREATE TEMPORARY TABLE tmp_t1(id INT, r1 TEXT, r2 TEXT) ROW_FORMAT=COMPRESSED;

Results in error 3500.

Conclusion – MySQL compressed tables reduce storage and I/O at the cost of higher CPU usage during writes. They are useful for read‑heavy workloads and when disk space is limited, but careful selection of compression algorithms and page sizes is required.