How Many Rows Can an InnoDB B+Tree Store? Understanding Page Size, Tree Height, and Index Organization

InnoDB’s minimal storage unit is a page of 16 KB. While a disk sector is 512 bytes and a file‑system block is typically 4 KB, InnoDB stores both data rows and index entries inside these 16 KB pages.

The default page size can be verified with the MySQL command:

mysql> show variables like 'innodb_page_size';
+------------------+-------+
| Variable_name    | Value |
+------------------+-------+
| innodb_page_size | 16384 |
+------------------+-------+
1 row in set (0.00 sec)

If a row occupies roughly 1 KB, a single page can hold about 16 rows (16384 / 1024). This forms the basis for estimating how many rows a leaf page of a B+‑tree can store.

In a B+‑tree, leaf pages store the actual rows, while non‑leaf pages store key‑pointer pairs. Assuming a BIGINT primary key (8 bytes) and a 6‑byte pointer, each entry consumes 14 bytes, allowing approximately 1170 entries per non‑leaf page (16384 / 14).

Consequently, a height‑2 B+‑tree can store 1170 × 16 ≈ 18,720 rows, and a height‑3 tree can store 1170² × 16 ≈ 21,902,400 rows. In practice, InnoDB B+‑trees usually have a height of 1–3, meaning most tables require only 1–3 page reads (I/O) to locate a row.

The tree height can be derived from the page‑level metadata stored in the root page (page number 3). The page level is located at offset 64 bytes within the root page, and the height equals page level + 1 . The following query retrieves index metadata, including the root page number:

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;

Results show that for tables such as customer and lineitem , the primary‑key index root page is 3, while secondary indexes use page 4. By examining the root page with a hex‑dump tool at offset 16384 × 3 + 64 = 49216 , the stored page level reveals the tree height (e.g., level 2 → height 3).

Empirical data from a benchmark database demonstrates that even with millions of rows, the B+‑tree height remains 3, confirming that query performance does not degrade significantly as row count grows, because the number of I/O operations stays low.

Finally, an interview question—why MySQL uses B+‑trees instead of B‑trees—is answered: B+‑trees keep data only in leaf nodes, allowing non‑leaf nodes to hold more pointers, reducing tree height and I/O compared to B‑trees where both leaf and internal nodes store data.