InnoDB COMPACT Row Format
MariaDB until 10.2.1
In MariaDB 10.2.1 and before, the default row format is COMPACT.
Contents
The COMPACT row format is similar to the REDUNDANT row format, but it stores data in a more compact manner that requires about 20% less storage.
Using the COMPACT Row Format
MariaDB starting with 10.2.2
In MariaDB 10.2.2 and later, the easiest way to create an InnoDB table that uses the COMPACT row format is by setting the ROW_FORMAT table option to to COMPACT in a CREATE TABLE or ALTER TABLE statement.
It is recommended to set the innodb_strict_mode system variable to ON when using this row format.
The COMPACT row format is supported by both the Antelope and the Barracuda file formats, so tables with this row format can be created regardless of the value of the innodb_file_format system variable.
For example:
SET SESSION innodb_strict_mode=ON; CREATE TABLE tab ( id int, str varchar(50) ) ENGINE=InnoDB ROW_FORMAT=COMPACT;
MariaDB until 10.2.1
In MariaDB 10.2.1 and before, the default row format is COMPACT. Therefore, in these versions, the easiest way to create an InnoDB table that uses the COMPACT row format is by not setting the ROW_FORMAT table option at all in the CREATE TABLE or ALTER TABLE statement.
It is recommended to set the innodb_strict_mode system variable to ON when using this row format.
The COMPACT row format is supported by both the Antelope and the Barracuda file formats, so tables with this row format can be created regardless of the value of the innodb_file_format system variable.
For example:
SET SESSION innodb_strict_mode=ON; CREATE TABLE tab ( id int, str varchar(50) ) ENGINE=InnoDB;
Index Prefixes with the COMPACT Row Format
The COMPACT row format supports index prefixes up to 767 bytes.
Overflow Pages with the COMPACT Row Format
All InnoDB row formats can store certain kinds of data in overflow pages. This allows for the maximum row size of an InnoDB table to be larger than the maximum amount of data that can be stored in the row's main data page. See Maximum Row Size for more information about the other factors that can contribute to the maximum row size for InnoDB tables.
In the COMPACT row format variable-length columns, such as columns using the VARBINARY, VARCHAR, BLOB and TEXT data types, can be partially stored in overflow pages.
InnoDB only considers using overflow pages if the table's row size is greater than half of innodb_page_size. If the row size is greater than this, then InnoDB chooses variable-length columns to be stored on overflow pages until the row size is less than half of innodb_page_size.
For VARBINARY, VARCHAR, BLOB and TEXT columns, only values longer than 767 bytes are considered for storage on overflow pages. Bytes that are stored to track a value's length do not count towards this limit. This limit is only based on the length of the actual column's data.
Fixed-length columns greater than 767 bytes are encoded as variable-length columns, so they can also be stored in overflow pages if the table's row size is greater than half of innodb_page_size. Even though a column using the CHAR data type can hold at most 255 characters, a CHAR column can still exceed 767 bytes in some cases. For example, a char(255) column can exceed 767 bytes if the character set is utf8mb4.
If a column is chosen to be stored on overflow pages, then the first 767 bytes of the column's value and a 20-byte pointer to the column's first overflow page are stored on the main page. Each overflow page is the size of innodb_page_size. If a column is too large to be stored on a single overflow page, then it is stored on multiple overflow pages. Each overflow page contains part of the data and a 20-byte pointer to the next overflow page, if a next page exists.
