Documentation Home
MySQL 5.6 Reference Manual
Related Documentation Download this Manual
PDF (US Ltr) - 31.5Mb
PDF (A4) - 31.6Mb
PDF (RPM) - 30.1Mb
EPUB - 7.7Mb
HTML Download (TGZ) - 7.5Mb
HTML Download (Zip) - 7.6Mb
HTML Download (RPM) - 6.5Mb
Eclipse Doc Plugin (TGZ) - 8.2Mb
Eclipse Doc Plugin (Zip) - 10.1Mb
Man Pages (TGZ) - 181.3Kb
Man Pages (Zip) - 292.5Kb
Info (Gzip) - 2.8Mb
Info (Zip) - 2.8Mb
Excerpts from this Manual

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

14.9.3 DYNAMIC and COMPRESSED Row Formats

This section discusses the DYNAMIC and COMPRESSED row formats for InnoDB tables. To create tables that use these row formats, innodb_file_format must be set to Barracuda, and innodb_file_per_table must be enabled. (The Barracuda file format also allows the COMPACT and REDUNDANT row formats.)

When a table is created with ROW_FORMAT=DYNAMIC or ROW_FORMAT=COMPRESSED, long variable-length column values (for VARBINARY, VARCHAR, BLOB, and TEXT columns) are stored fully off-page, and the clustered index record contains only a 20-byte pointer to the overflow page. InnoDB will also store long CHAR column values off-page if the column value is greater than or equal to 768 bytes, which can occur when the maximum byte length of the character set is greater than 3, as it is with utf8mb4, for example.

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 this new 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 option KEY_BLOCK_SIZE 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.7, “InnoDB Table 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 disabled by default. See the innodb_large_prefix option description for more information.

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

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