Analyzing Row Format Efficiency

Select between Compact, Redundant, and Dynamic row formats for optimal storage.

In this lesson, you will learn how to select between COMPACT, REDUNDANT, and DYNAMIC row formats in InnoDB to balance storage efficiency with performance.

Understanding Row Formats

When you create an InnoDB table, the way your data is physically packed onto the 16KB pages dictates how much space you use and how fast your queries run. The row format determines how the engine handles variable-length columns (like VARCHAR or BLOB) and how it stores "overflow" data when a row is too large to fit in a single page.

The Three Formats

REDUNDANT: This is the legacy format from older versions of InnoDB. It is largely inefficient by modern standards and is rarely used today as it lacks many of the performance optimizations present in newer formats.
COMPACT: This was the default for many years. It stores a prefix of variable-length columns in the B-tree leaf node and moves the rest to "overflow pages." It is highly efficient for tables with short columns, keeping index pages slim.
DYNAMIC: This is the modern default and the most flexible. Like COMPACT, it uses a prefix strategy for overflow, but it handles large VARCHAR and TEXT columns much more efficiently. It stores the entire long column off-page (in overflow pages) and only keeps a 20-byte pointer in the main row. This prevents the main B-tree from becoming bloated, keeping your index scans significantly faster.

Putting it into practice

You can define or alter the row format at the table level to suit your schema's needs:

-- Creating a table with the modern DYNAMIC format
CREATE TABLE user_logs (
    id INT PRIMARY KEY,
    payload TEXT
) ROW_FORMAT=DYNAMIC;

-- Checking the current format of your tables
SHOW TABLE STATUS LIKE 'user_logs';

If you are dealing with large BLOB or TEXT fields, DYNAMIC is almost always the superior choice. By keeping the main page "light," you ensure that the buffer pool can cache more rows at once, which directly reduces the frequency of disk I/O during heavy read workloads.

Key Takeaway: Use DYNAMIC as your default row format to ensure optimal handling of large data types and maximum performance during index traversal.