5.3. DYNAMIC and COMPRESSED Row Formats

This section discusses the DYNAMIC and
COMPRESSED row formats for InnoDB tables. You
can only create these kinds of tables when the
innodb_file_format configuration
option is set to Barracuda. (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 column values
are stored fully off-page, and the clustered index record contains
only 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.

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
Chapter 3, Working with InnoDB Compressed Tables.