MariaDB Enterprise Server InnoDB Compressed Row Format

This page is part of MariaDB's Documentation.

The parent of this page is: MariaDB Enterprise Server InnoDB Row Formats

Topics on this page:

Overview
Supported Block Sizes
Supported Index Prefix Limits
Create an InnoDB Table with the Compressed Row Format using ROW_FORMAT
- Step-by-Step Procedure
Create an InnoDB Table with the Compressed Row Format using KEY_BLOCK_SIZE
- Step-by-Step Procedure

Overview

The Compressed row format is similar to the Dynamic row format, except that it compresses pages before they are written to disk. Some details about the Compressed row format:

The Compressed row format does not efficiently use the InnoDB buffer pool, so it is not the recommended way to achieve InnoDB table compression.
For more information about how to compress InnoDB tables, see Configure InnoDB Page Compression.
Like the Dynamic row format, the Compressed row format can most efficiently store large columns that use the CHAR, VARCHAR, LONGTEXT, TEXT, MEDIUMTEXT, and TINYTEXT, VARBINARY, LONGBLOB, BLOB, MEDIUMBLOB, and TINYBLOB data types.
With the Compressed row format, the block size is set by the KEY_BLOCK_SIZE table option.
If the KEY_BLOCK_SIZE table option is not set, the block size defaults to half of innodb_page_size. With the default 16 KB page size, the block size defaults to 8 KB.
Like the Dynamic row format, the Compressed row format can index the longest column values. The limit for indexing column values depends on the innodb_page_size value.
The Compressed row format cannot be set to InnoDB's default row format using the innodb_default_row_format system variable.
The Compressed row format is not supported if innodb_page_size is set to 32k or 64k.
The Compressed row format does not support add/drop column operations with the INSTANT algorithm.

Supported Block Sizes

The Compressed row format supports the following block sizes:

Block Size	KEY_BLOCK_SIZE Value
1 KB	1
2 KB	2
4 KB	4
8 KB	8
16 KB	16

If the KEY_BLOCK_SIZE table option is not specified, the block size defaults to half of innodb_page_size. With the default 16 KB page size, the block size defaults to 8 KB.

For compression to be effective, the chosen block size should be smaller than the servers value of the innodb_page_size system variable.

Supported Index Prefix Limits

The limit for indexing column values depends on the innodb_page_size value:

Page Size	Index Prefix Limit
`16k`	3072 bytes
`8k`	1536 bytes
`4k`	768 bytes

Create an InnoDB Table with the Compressed Row Format using ROW_FORMAT

The default block size for tables that use the Compressed row format is half of innodb_page_size. With the default 16 KB page size, the block size defaults to 8 KB. A compressed table with the default block size can be created by setting the ROW_FORMAT table option to COMPRESSED:

CREATE TABLE hq_sales.invoices (
   invoice_id BIGINT UNSIGNED AUTO_INCREMENT NOT NULL,
   branch_id INT NOT NULL,
   customer_id INT,
   invoice_date DATETIME(6),
   invoice_total DECIMAL(13, 2),
   payment_method ENUM('NONE', 'CASH', 'WIRE_TRANSFER', 'CREDIT_CARD', 'GIFT_CARD'),
   PRIMARY KEY(invoice_id)
) ROW_FORMAT = Compressed;

Step-by-Step Procedure

Connect to the server using MariaDB Client:
```
$ mariadb --user=root
```

Confirm that the default storage engine is InnoDB by checking the default_storage_engine system variable using the SHOW SESSION VARIABLES statement:

SHOW SESSION VARIABLES
   LIKE 'default_storage_engine';

+------------------------+--------+
| Variable_name          | Value  |
+------------------------+--------+
| default_storage_engine | InnoDB |
+------------------------+--------+

Create the table using the CREATE TABLE statement, and specify the Compressed row format using the ROW_FORMAT table option:

CREATE TABLE hq_sales.invoices (
   invoice_id BIGINT UNSIGNED AUTO_INCREMENT NOT NULL,
   branch_id INT NOT NULL,
   customer_id INT,
   invoice_date DATETIME(6),
   invoice_total DECIMAL(13, 2),
   payment_method ENUM('NONE', 'CASH', 'WIRE_TRANSFER', 'CREDIT_CARD', 'GIFT_CARD'),
   PRIMARY KEY(invoice_id)
) ROW_FORMAT = Compressed;

Confirm that the table uses the Compressed row format with an 8 KB block size by querying the information_schema.INNODB_SYS_TABLES table:

SELECT NAME, ROW_FORMAT, ZIP_PAGE_SIZE
FROM information_schema.INNODB_SYS_TABLES
WHERE NAME='hq_sales/invoices';

+-------------------+------------+---------------+
| NAME              | ROW_FORMAT | ZIP_PAGE_SIZE |
+-------------------+------------+---------------+
| hq_sales/invoices | Compressed |          8192 |
+-------------------+------------+---------------+

Create an InnoDB Table with the Compressed Row Format using KEY_BLOCK_SIZE

The default block size for tables that use the Compressed row format is half of innodb_page_size. With the default 16 KB page size, the block size defaults to 8 KB. A compressed table with a non-default block size can be created by setting the KEY_BLOCK_SIZE table option to the desired block size.

CREATE TABLE hq_sales.invoices (
   invoice_id BIGINT UNSIGNED AUTO_INCREMENT NOT NULL,
   branch_id INT NOT NULL,
   customer_id INT,
   invoice_date DATETIME(6),
   invoice_total DECIMAL(13, 2),
   payment_method ENUM('NONE', 'CASH', 'WIRE_TRANSFER', 'CREDIT_CARD', 'GIFT_CARD'),
   PRIMARY KEY(invoice_id)
) KEY_BLOCK_SIZE = 4;

Step-by-Step Procedure

Connect to the server using MariaDB Client:
```
$ mariadb --user=root
```

Confirm that the default storage engine is InnoDB by checking the default_storage_engine system variable using the SHOW SESSION VARIABLES statement:

SHOW SESSION VARIABLES
   LIKE 'default_storage_engine';

+------------------------+--------+
| Variable_name          | Value  |
+------------------------+--------+
| default_storage_engine | InnoDB |
+------------------------+--------+

Create the table using the CREATE TABLE statement, and specify the block size using the KEY_BLOCK_SIZE table option, which implies the Compressed row format:

CREATE TABLE hq_sales.invoices (
   invoice_id BIGINT UNSIGNED AUTO_INCREMENT NOT NULL,
   branch_id INT NOT NULL,
   customer_id INT,
   invoice_date DATETIME(6),
   invoice_total DECIMAL(13, 2),
   payment_method ENUM('NONE', 'CASH', 'WIRE_TRANSFER', 'CREDIT_CARD', 'GIFT_CARD'),
   PRIMARY KEY(invoice_id)
) KEY_BLOCK_SIZE = 4;

Confirm that the table uses the Compressed row format with an 8 KB block size by querying the information_schema.INNODB_SYS_TABLES table:

SELECT NAME, ROW_FORMAT, ZIP_PAGE_SIZE
FROM information_schema.INNODB_SYS_TABLES
WHERE NAME='hq_sales/invoices';

+-------------------+------------+---------------+
| NAME              | ROW_FORMAT | ZIP_PAGE_SIZE |
+-------------------+------------+---------------+
| hq_sales/invoices | Compressed |          4096 |
+-------------------+------------+---------------+