This section discusses the
COMPRESSED row formats for InnoDB tables. To
create tables that use these row formats,
innodb_file_format must be set to
innodb_file_per_table must be
enabled. (The Barracuda file format also allows the
When a table is created with
ROW_FORMAT=COMPRESSED, long variable-length
column values (for
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
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
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”.