How Many Rows Can an InnoDB B+ Tree Store?

InnoDB stores data in pages of 16 KB, which are the smallest allocation unit for the engine; each page can hold either data rows or index entries (key + pointer). Assuming a row size of 1 KB, a single page can contain 16 rows.

The B+‑tree index uses leaf pages to store rows and non‑leaf pages to store keys and pointers. With a primary‑key column of type BIGINT (8 bytes) and a pointer size of 6 bytes, each index entry occupies 14 bytes, allowing 16 384 / 14 ≈ 1 170 entries per non‑leaf page.

For a B+‑tree of height 2 (root + leaf), the total record capacity is 1 170 × 16 = 18 720 rows. For height 3, the capacity becomes 1 170 × 1 170 × 16 ≈ 21 902 400 rows, showing that a tree of only 1‑3 levels can store millions of rows, and each lookup requires only 1‑3 page I/Os.

The root page of the primary‑key index is always page number 3 in the tablespace file. The page‑level value stored at offset 64 within that page indicates the tree height (height = page‑level + 1). The following query can be used to inspect index metadata:

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;

Examining real tables (e.g., lineitem , customer , region ) shows page‑level values of 2, 2, and 0 respectively, corresponding to tree heights of 3, 3, and 1. Despite large differences in row counts, the index height remains low, keeping lookup I/O minimal.

In summary, InnoDB B+‑tree indexes store data efficiently because leaf nodes hold rows while internal nodes hold only keys and pointers, resulting in high fan‑out and low tree height, which is why MySQL prefers B+‑trees over plain B‑trees.