The storage for rows and associated columns affects performance for queries and DML operations. As more rows fit into a single disk page, queries and index lookups can work faster, less cache memory is required in the InnoDB buffer pool, and less I/O is required to write out updated values for the numeric and short string columns.
All data in InnoDB is stored in database pages that make up a B-tree index (the clustered index organized according to the primary key columns). Table data and indexes both use this type of structure. The nodes of the index data structure contain the values of all the columns in that row (for the clustered index) or the index columns and the primary key columns (for secondary indexes).
Variable-length columns are an exception to this rule. Columns
that are too long to fit on a B-tree page are stored on separately
allocated disk pages called
overflow pages. We call
such columns off-page
column. The values of these columns are stored on
singly-linked lists of overflow pages, and each such column has
its own list of one or more overflow pages. In some cases, all or
a prefix of the long column value is stored in the B-tree, to
avoid wasting storage and eliminating the need to read a separate
The Barracuda file format
provides a new option (
control how much column data is stored in the clustered index, and
how much is placed on overflow pages.