This section discusses the
COMPRESSED row formats for
InnoDB tables. To create tables that use these
must be set to
innodb_file_per_table must be
enabled. (The Barracuda file format also allows the
When a table is created with
InnoDB can store long variable-length column
TEXT types) fully off-page, with
the clustered index record containing only a 20-byte pointer to
the overflow page.
InnoDB can also store
CHAR column values off-page if the
column value is greater than or equal to 768 bytes, which can
occur if 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
BLOB columns that are less than or
equal to 40 bytes are always stored in-line.
DYNAMIC row format maintains the efficiency
of storing the entire row in the index node if it fits (as do the
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.
COMPRESSED row format uses similar internal
details for off-page storage as the
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 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.9, “InnoDB Table Compression”.
row formats support index key prefixes up to 3072 bytes. This
feature is controlled by the
option, which is disabled by default. See the
description for more information.
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 14.8.3, “Physical Row Structure of InnoDB Tables”.