Choose MySQL InnoDB Row Format: Dynamic vs Compressed (2026)

The InnoDB row format is more than just a storage optimization; it’s a fundamental choice that dictates how your data is physically laid out on disk, impacting everything from read/write performance to disk space usage.

Let’s see it in action. Imagine two identical tables, users_dynamic and users_compressed, both with a VARCHAR(255) column for bio.

CREATE TABLE users_dynamic (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(100),
    bio VARCHAR(255)
) ENGINE=InnoDB ROW_FORMAT=DYNAMIC;

CREATE TABLE users_compressed (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(100),
    bio VARCHAR(255)
) ENGINE=InnoDB ROW_FORMAT=COMPRESSED;

Now, let’s insert some data. For bio, we’ll use short strings for users_dynamic and longer ones for users_compressed, simulating typical usage patterns where DYNAMIC might be favored for shorter strings and COMPRESSED for longer ones.

-- Insert short bios for dynamic
INSERT INTO users_dynamic (name, bio) VALUES
('Alice', 'A short bio.'),
('Bob', 'Another brief description.');

-- Insert longer bios for compressed
INSERT INTO users_compressed (name, bio) VALUES
('Charlie', 'This is a much longer biography for Charlie, detailing his extensive career in quantum physics and his passion for vintage synthesizers. He has published several influential papers and enjoys hiking in his free time.'),
('David', 'David has a fascinating background in marine biology, specializing in deep-sea ecosystems. He often gives lectures at universities and is a strong advocate for ocean conservation.');

If we were to inspect the actual disk usage (which is hard to do directly without tools like innodb_file_per_table enabled and inspecting the .ibd files), users_dynamic would likely consume more space per row due to the overhead of storing even short strings with potential for future expansion. users_compressed, on the other hand, would benefit from its compression algorithm, significantly reducing the footprint of those longer bio entries.

The problem this solves is the trade-off between disk space efficiency and the overhead of data management. Historically, COMPACT was the default, and it stored fixed-length columns inline and variable-length columns inline if they fit, otherwise off-page. DYNAMIC improved on this by always storing variable-length data off-page if it didn’t fit inline, reducing page splits and improving performance for rows with long variable-length fields. COMPRESSED adds another layer: it compresses the off-page data blocks, further saving disk space at the cost of CPU cycles for compression/decompression.

Internally, InnoDB pages are 16KB. When a row is inserted, InnoDB tries to fit as much data as possible onto a single page.

• COMPACT: Stores variable-length data inline if it fits. If not, it’s moved off-page, and a 20-byte pointer is stored inline. This could lead to a lot of small off-page pointers.
• DYNAMIC: Similar to COMPACT but always stores variable-length data off-page if it doesn’t fit inline. This means fewer rows per page if variable-length fields are large, but it avoids the overhead of storing short variable-length fields inline when they might grow later. It also has a more efficient way of handling pointers to off-page data.
• COMPRESSED: Uses a compressed page format. It takes the data that would normally go into a DYNAMIC page and compresses it using zlib. This means you can fit more logical data into fewer physical pages, significantly reducing disk I/O and storage. The trade-off is the CPU cost of compression and decompression. It also uses a separate index page to map compressed pages to their original data.

The exact levers you control are innodb_default_row_format at the server level, and ROW_FORMAT at the table creation level. You can change the row format of an existing table, but it’s a resource-intensive operation: ALTER TABLE table_name ROW_FORMAT=new_format;. This effectively rebuilds the table.

A key characteristic of COMPRESSED row format is its use of a separate index page. This index page is not compressed and stores pointers to the actual compressed data pages. This means that even with COMPRESSED row format, there’s a small, uncompressed overhead for managing the compressed data. This can make very small tables or tables with mostly very short data less suitable for COMPRESSED if the overhead of the index page outweighs the compression benefits.

The next concept you’ll likely encounter is how these row formats interact with full-text indexing and spatial indexing, as their implementations can be sensitive to the underlying data layout.