An overview of the four InnoDB row formats (REDUNDANT, COMPACT, DYNAMIC, COMPRESSED), comparing their storage efficiency and feature support.
The InnoDB storage engine supports four different row formats:
The system variable can be used to set the default row format for InnoDB tables. The possible values are:
redundant
compact
dynamic
This system variable's default value is dynamic, which means that the default row format is DYNAMIC.
This system variable cannot be set to compressed, which means that the default row format cannot be COMPRESSED.
For example, the following statements would create a table with the DYNAMIC row format:
One way to specify an InnoDB table's row format is by setting the table option to the relevant row format in a or statement:
The statement can be used to see the row format used by a table:
The table can also be queried to see the row format used by a table:
A table's tablespace is tagged with the lowest InnoDB file format that supports the table's row format. So, even if the Barracuda file format is enabled, tables that use the COMPACT or REDUNDANT row formats are tagged with the Antelope file format in the table.
The REDUNDANT row format is the original non-compacted row format.
The REDUNDANT row format was the only available row format before MySQL 5.0.3. In that release, this row format was retroactively named the REDUNDANT row format. In the same release, the COMPACT row format was introduced as the new default row format.
See for more information.
Default row format in earlier versions COMPACT.
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.
See for more information.
DYNAMIC is the default row format.
The DYNAMIC row format is similar to the COMPACT row format, but tables using the DYNAMIC row format can store even more data on overflow pages than tables using the COMPACT row format. This results in more efficient data storage than tables using the COMPACT row format, especially for tables containing columns using the , , and data types. However, InnoDB tables using the COMPRESSED row format are more efficient.
See for more information.
An alternative way to compress InnoDB tables is by using .
The COMPRESSED row format is similar to the COMPACT row format, but tables using the COMPRESSED row format can store even more data on overflow pages than tables using the COMPACT row format. This results in more efficient data storage than tables using the COMPACT row format, especially for tables containing columns using the , , and data types.
The COMPRESSED row format also supports compression of all data and index pages.
See for more information.
Several factors help determine the maximum row size of an InnoDB table.
First, MariaDB enforces a 65,535 byte limit on a table's maximum row size. The total size of a table's and columns do not count towards this limit. Only the pointers for a table's and columns count towards this limit. MariaDB enforces this limit for all storage engines, so this limit also applies to InnoDB tables. Therefore, this limit is the absolute maximum row size for an InnoDB table.
If you try to create a table that exceeds MariaDB's global limit on a table's maximum row size, then you will see an error like this:
However, InnoDB also has its own limits on the maximum row size, so an InnoDB table's maximum row size could be smaller than MariaDB's global limit.
Second, the maximum amount of data that an InnoDB table can store in a row's main data page depends on the value of the system variable. At most, the data that a single row can consume on the row's main data page is half of the value of the system variable. With the default value of 16k, that would mean that a single row can consume at most around 8 KB on the row's main data page. However, the limit on the row's main data page is not the absolute limit on the row's size.
Third, all InnoDB row formats can store certain kinds of data in overflow pages, so the maximum row size of an InnoDB table can be larger than the maximum amount of data that can be stored in the row's main data page.
Some row formats can store more data in overflow pages than others. For example, the DYNAMIC and COMPRESSED row formats can store the most data in overflow pages. To see how to determine the how the various InnoDB row formats can use overflow pages, see the following sections:
If a table's definition can allow rows that the table's InnoDB row format can't actually store, then InnoDB will raise errors or warnings in certain scenarios.
If the table were using the REDUNDANT or COMPACT row formats, then the error or warning would be the following:
And if the table were using the DYNAMIC or COMPRESSED row formats, then the error or warning would be the following:
These messages are raised in the following cases:
If is enabled and if a statement is executed that touches the table, such as or , then InnoDB will raise an error with this message
If is disabled and if a statement is executed that touches the table, such as or [ALTER TABLE](../../../sql-statements-and-structure/sql-statements/data-definition/alter/alter-table.md), then InnoDB will raise a warning with this message.
Regardless of whether is enabled, if a statement is executed that attempts to write a row that the table's InnoDB row format can't store, then InnoDB will raise an error with this message.
For information on how to solve the problem, see .
In earlier versions, MariaDB doesn't properly calculate the row sizes while executing DDL. In these versions, unsafe tables can be created, even if is enabled. The calculations were fixed by .
As a side effect, some tables that could be created or altered in previous versions may get rejected with the following error in these releases and any later releases.
And users could also see the following message as an error or warning in the :
InnoDB used the wrong calculations to determine row sizes for quite a long time, so a lot of users may unknowingly have unsafe tables that the InnoDB row format can't actually store.
InnoDB does not currently have an easy way to check which existing tables have this problem. See for more information.
For information on how to solve the problem, see .
This page is licensed: CC BY-SA / Gnu FDL
Yes
No
No
Efficiently utilizes buffer pool
Yes
No
Yes
Yes
Supported Page Sizes
• 64k • 32k • 16k • 8k • 4k
• 16k • 8k • 4k
• 64k • 32k • 16k • 8k • 4k
• 64k • 32k • 16k • 8k • 4k
Maximum size of indexed column values
• 3072 bytes (innodb_page_size >= 16k) • 1536 bytes (innodb_page_size == 8k) • 768 bytes (innodb_page_size == 4k)
• 3072 bytes (innodb_page_size >= 16k) • 1536 bytes (innodb_page_size == 8k) • 768 bytes (innodb_page_size == 4k)
767 bytes
767 bytes
Supports ADD/DROP column with INSTANT Algorithm
Yes
No
Yes
Yes
Feature
Default
Yes
No
No
No
Recommended
Yes
No
No
No
Efficiently stores large columns
Yes
SET SESSION innodb_strict_mode=ON;
SET GLOBAL innodb_default_row_format='dynamic';
CREATE TABLE tab (
id INT,
str VARCHAR(50)
) ENGINE=InnoDB;SET SESSION innodb_strict_mode=ON;
SET GLOBAL innodb_file_per_table=ON;
SET GLOBAL innodb_file_format='Barracuda';
CREATE TABLE tab (
id INT,
str VARCHAR(50)
) ENGINE=InnoDB ROW_FORMAT=DYNAMIC;SHOW TABLE STATUS FROM db1 WHERE Name='tab'\G
*************************** 1. row ***************************
Name: tab
Engine: InnoDB
Version: 10
Row_format: Dynamic
Rows: 0
Avg_row_length: 0
Data_length: 16384
Max_data_length: 0
Index_length: 0
Data_free: 0
Auto_increment: NULL
Create_time: 2019-04-18 20:24:04
Update_time: NULL
Check_time: NULL
Collation: latin1_swedish_ci
Checksum: NULL
Create_options: row_format=DYNAMIC
Comment:SELECT * FROM information_schema.INNODB_SYS_TABLES WHERE name='db1/tab'\G
*************************** 1. row ***************************
TABLE_ID: 42
NAME: db1/tab
FLAG: 33
N_COLS: 4
SPACE: 27
FILE_FORMAT: Barracuda
ROW_FORMAT: Dynamic
ZIP_PAGE_SIZE: 0
SPACE_TYPE: SingleERROR 1118 (42000): Row size too large. The maximum row size for the used table type,
not counting BLOBs, is 65535. This includes storage overhead, check the manual. You
have to change some columns to TEXT or BLOBsERROR 1118 (42000): Row size too large (> 8126). Changing some columns to
TEXT or BLOB or using ROW_FORMAT=DYNAMIC or ROW_FORMAT=COMPRESSED
may help. In current row format, BLOB prefix of 768 bytes is stored inline.ERROR 1118 (42000): Row size too large (> 8126). Changing some columns to
TEXT or BLOB may help. In current row format, BLOB prefix of 0 bytes is stored inline.ERROR 1118 (42000): Row size too large (> 8126). Changing some columns to
TEXT or BLOB may help. In current row format, BLOB prefix of 0 bytes is stored inline.[Warning] InnoDB: Cannot add field col in table db1.tab because after adding it, the row size is 8478 which is greater than maximum allowed size (8126) for a record on index leaf page.