Understanding InnoDB Storage Engine: Page Size, Row Format, and VARCHAR Limits

1. What is InnoDB?

InnoDB is a storage engine that persists table data on disk.

2. How does InnoDB read and write data?

Data is loaded from disk into memory in 16 KB pages; the first page read for a query incurs a 300‑400 ms delay, while subsequent pages are served from memory with 30‑40 ms latency. InnoDB reads or writes whole pages, not individual rows.

3. InnoDB row format and VARCHAR handling

InnoDB supports four row formats (Dynamic, Compact, Redundant, Compressed). The default in modern MySQL versions is Dynamic , which stores variable‑length columns (VARCHAR, TEXT, BLOB) using a length list, a NULL‑value list, and a record header.

The length list records the actual byte length (L) of each variable‑length column using 1 or 2 bytes. The maximum storable byte length is 2^16‑1 = 65535 bytes. With the utf8mb4 character set (up to 4 bytes per character), the practical maximum number of characters is ⌊65535 / 4⌋ = 16383.

Example to view the default row format:

SHOW VARIABLES LIKE "innodb_default_row_format"

Example table used for illustration:

CREATE TABLE test (
  c1 VARCHAR(10),
  c2 VARCHAR(10) NOT NULL,
  c3 CHAR(10),
  c4 VARCHAR(10)
) CHARSET=utf8mb4;

Inserting sample rows:

INSERT INTO test (c1, c2, c3, c4) VALUES
  ('aaaa', '你好啊', 'cc', 'd'),
  ('eeee', 'fff', NULL, NULL);

To check the byte length of a VARCHAR column:

SELECT LENGTH(c2) FROM test WHERE c1='aaaa';

3.1 Length list storage

Each variable‑length column stores its actual byte length; short values (< 255 bytes) use a single‑byte length field, longer values use two bytes. The length list is stored in reverse column order.

3.2 NULL‑value list

Columns that allow NULL are represented in a bitmap; each bit indicates whether the column is NULL. The bitmap occupies whole bytes, padding the high bits with zeros if necessary.

3.3 Overflow columns

If a column’s data exceeds the page size, InnoDB stores a 20‑byte pointer in the row and places the actual data in overflow pages linked as a singly‑linked list.

4. Practical limits

Although VARCHAR(16383) is theoretically allowed under utf8mb4, the row size limit (65535 bytes) and overhead from length lists, NULL bitmaps, and record headers reduce the usable size. In tests, a VARCHAR column reached about 48 545 bytes before errors occurred.

For very large text, using TEXT or BLOB types is recommended.

5. Further reading

For deeper insight into other InnoDB row formats and record header details, refer to the book “How MySQL Works”. The article focuses on the Dynamic format, which is most common in everyday development.