Understanding MySQL Row Overflow and InnoDB Row Formats

1. MySQL Row Storage Formats

InnoDB provides four row formats: Redundant (legacy, used before MySQL 5.0), Compact (compact format introduced in MySQL 5.0 and default since 5.1), Dynamic and Compressed (both based on Compact, with Dynamic becoming the default from MySQL 5.7).

The choice of row format directly affects query and DML performance because a more compact format allows more rows per page, speeding up lookups.

2. Row Overflow

InnoDB stores data in 16KB pages. When a row’s size exceeds a page—common with large VARCHAR, TEXT, or BLOB columns—the excess data is moved to an overflow page. In Compact format the first 768 bytes stay on the primary page and the overflow page address is stored; Dynamic and Compressed formats store only a 20‑byte pointer to the overflow page.

3. Impact of Row Overflow

Performance: Additional disk I/O is required to read overflow pages, increasing query latency.

Space usage: Extra pages are allocated for overflow data, consuming more storage.

4. How to Avoid Row Overflow

Reduce the use of large BLOB or TEXT columns; redesign tables to store large objects elsewhere.

Adjust InnoDB configuration: increase the page size (e.g., to 32KB) by setting innodb_page_size. This change must be applied at instance initialization and affects the whole database.