Static format is the default for
MyISAM tables. It is used when the table contains no variable-length columns (
TEXT). Each row is stored using a fixed number of bytes
- Very quick.
- Easy to cache.
- Easy to reconstruct after a crash, because rows are located in fixed positions.
- Usually require more disk space than dynamic-format tables
- Fixed-length row format is only available for tables without
Dynamic storage format is used if a
MyISAM table contains any variable-length columns (
TEXT), or if the table was created with the
ROW_FORMAT=DYNAMIC table option.
Dynamic format is a little more complex than static format because each row has a header that indicates how long it is.
- Much less disk space usually is required than for fixed-length tables.
- More difficult than static-format raw to reconstruct after a crash, because rows may be fragmented into many pieces and links (fragments) may be missing.
- Null and Zero do not stored in disk but there key marked in bitmap
Each row is compressed separately, so there is very little access overhead. The header for a row takes up one to three bytes depending on the biggest row in the table. Each column is compressed differently. There is usually a different Huffman tree for each column. Some of the compression types are:
- Numbers with a value of zero are stored using one bit.
- If values in an integer column have a small range, the column is stored using the smallest possible type. For example, a
BIGINT column (eight bytes) can be stored as a
TINYINT column (one byte) if all its values are in the range from
- If a column has only a small set of possible values, the data type is converted to
- While a compressed table is read only, and you cannot therefore update or add rows in the table
REDUNDANT Row Formats
Previous versions of InnoDB used an unnamed file format (now called “Antelope”) for database files. With that format, tables were defined with
ROW_FORMAT=REDUNDANT) and InnoDB stored up to the first 768 bytes of variable-length columns (such as
VARCHAR) in the index record within the B-tree node, with the remainder stored on the overflow page(s).
To preserve compatibility with those prior versions, tables created with the InnoDB Plugin use the prefix format, unless one of
ROW_FORMAT=COMPRESSED is specified (or implied) on the