InnoDB B+ Tree Capacity and Height: How Many Rows Can It Store?

InnoDB stores data in pages of 16KB, which are the smallest storage unit for the engine; a page can hold about 16 rows if each row is ~1KB.

Pages are organized into a B+ tree where leaf pages store the actual rows and internal pages store key‑pointer pairs. The fan‑out of internal pages is determined by the size of a primary key (8 bytes) plus a 6‑byte pointer, allowing roughly 1,170 entries per internal page.

Assuming a two‑level tree (root + leaves), the tree can store 1,170 × 16 ≈ 18,720 rows; a three‑level tree can store 1,170² × 16 ≈ 21,902,400 rows, which explains why a single InnoDB B+ tree can hold tens of millions of rows.

The root page of a primary‑key index is always page number 3. Its page‑level field (offset 64 bytes) indicates the tree height: height = page level + 1. By reading this value from the tablespace file (e.g., using hexdump ) you can determine the actual height of the index.

Example queries to inspect the page size and index metadata:

mysql> show variables like 'innodb_page_size';
+------------------+-------+
| Variable_name    | Value |
+------------------+-------+
| innodb_page_size | 16384 |
+------------------+-------+

SELECT b.name, a.name, index_id, type, a.space, a.PAGE_NO
FROM information_schema.INNODB_SYS_INDEXES a,
     information_schema.INNODB_SYS_TABLES b
WHERE a.table_id = b.table_id AND a.space <> 0;

Practical measurements on sample tables (region, lineitem, customer) show that even with millions of rows the B+ tree height remains at most three, meaning index lookups require only 1–3 I/O operations.

Finally, the article answers a common interview question: MySQL uses B+ trees instead of B trees because B+ trees keep data only in leaf nodes, allowing a higher fan‑out and shallower trees, which reduces I/O and improves query performance.