Documentation Home
MySQL 5.7 Reference Manual
Related Documentation Download this Manual Excerpts from this Manual

MySQL 5.7 Reference Manual  /  ...  /  DYNAMIC and COMPRESSED Row Formats

14.8.3 DYNAMIC and COMPRESSED Row Formats

When a table is created with a DYNAMIC or COMPRESSED row format, long column values are stored fully off-page, and the clustered index record contains only a 20-byte pointer to the overflow page. By comparison, the COMPACT row format stores up to the first 768 bytes of variable-length columns values in the clustered index record along with a 20-byte pointer to the overflow page.

Whether any columns are stored off-page depends on the page size and the total size of the row. When the row is too long, InnoDB chooses the longest columns for off-page storage until the clustered index record fits on the B-tree page. TEXT and BLOB columns that are less than or equal to 40 bytes are always stored in-line.

The DYNAMIC row format maintains the efficiency of storing the entire row in the index node if it fits (as do the COMPACT and REDUNDANT formats), but the DYNAMIC row format avoids the problem of filling B-tree nodes with a large number of data bytes of long columns. The DYNAMIC format is based on the idea that if a portion of a long data value is stored off-page, it is usually most efficient to store all of the value off-page. With DYNAMIC format, shorter columns are likely to remain in the B-tree node, minimizing the number of overflow pages needed for any given row.

The COMPRESSED row format uses similar internal details for off-page storage as the DYNAMIC row format, with additional storage and performance considerations from the table and index data being compressed and using smaller page sizes. With the COMPRESSED row format, the KEY_BLOCK_SIZE option controls how much column data is stored in the clustered index, and how much is placed on overflow pages. For full details about the COMPRESSED row format, see Section 14.6, “InnoDB Table and Page Compression”.

Both DYNAMIC and COMPRESSED row formats support index key prefixes up to 3072 bytes. This feature is controlled by the innodb_large_prefix configuration option, which is enabled by default as of MySQL 5.7.7. See the innodb_large_prefix option description for more information.

Tables that use the COMPRESSED row format can be created in file_per_table tablespaces or general tablespaces (introduced in MySQL 5.7.6). The system tablespace does not support the COMPRESSED row format. To store a COMPRESSED table in a file-per-table tablespace, innodb_file_per_table must be enabled and innodb_file_format must be set to Barracuda. The innodb_file_per_table and innodb_file_format configuration options are not applicable to general tablespaces. General tablespaces support all row formats with the caveat that compressed and uncompressed tables cannot coexist in the same general tablespace due to different physical page sizes. For more information about general tablespaces, see Section 14.4.9, “InnoDB General Tablespaces”.

In MySQL 5.7.5 and earlier, tables that use the DYNAMIC row format can only be stored in file_per_table tablespaces, requiring that innodb_file_per_table be enabled and innodb_file_format be set to Barracuda.

As of MySQL 5.7.6, DYNAMIC tables can be stored in file-per-table tablespaces, general tablespaces, and the system tablespace. To store DYNAMIC tables in the system tablespace, you must use the TABLESPACE [=] innodb_system table option with CREATE TABLE or ALTER TABLE. The innodb_file_per_table and innodb_file_format configuration options are not applicable to general tablespaces, nor are they applicable when using the TABLESPACE [=] innodb_system table option to store DYNAMIC tables in the system tablespace.

As of MySQL 5.7.9, you can add a DYNAMIC table to the system tablespace by disabling innodb_file_per_table and using a regular CREATE TABLE or ALTER TABLE statement. The innodb_file_format setting is ignored. A DYNAMIC table always uses the Barracuda file format.

DYNAMIC and COMPRESSED row formats are variations of the COMPACT row format and therefore handle CHAR storage in the same way as the COMPACT row format. For more information, see Section, “Physical Row Structure”.

Download this Manual
User Comments
Sign Up Login You must be logged in to post a comment.