How Many Rows Can a MySQL InnoDB B+Tree Store?

InnoDB stores data in 16KB pages, which are the smallest storage unit for the engine, while the underlying disk uses 512‑byte sectors and 4KB blocks.

Each page can hold roughly 16 rows of 1KB each, or about 105 rows when the average row size is 153 bytes, as shown in the example of the sp_job_log table.

The B+‑tree index organizes these pages: leaf pages store the actual rows, non‑leaf pages store key‑value pairs and pointers. The number of pointers per non‑leaf page is 16384 / 14 ≈ 1170.

Tree height determines the number of I/O operations needed to locate a row. For a two‑level tree the capacity is 1170 × 16 ≈ 18 720 rows; a three‑level tree can store 1170 × 1170 × 16 ≈ 21 902 400 rows, and with a realistic row size the capacity reaches over 140 million rows.

Queries use the primary‑key (clustered) B+‑tree; secondary indexes add an extra traversal, so the worst‑case I/O for a lookup using a secondary index is the sum of the heights of both trees (e.g., 3 + 3 = 6 page reads).

Practical commands such as show variables like 'innodb_page_size' , show table status like 'sp_job_log'\G , and desc sp_job_log help verify page size and row statistics.

Understanding these storage details allows accurate estimation of how many rows a MySQL InnoDB B+‑tree can hold and the I/O cost of different query patterns.