How to Estimate MySQL Single-Table Capacity: The 20 Million Row Rule Explained
This article breaks down the reasoning behind MySQL’s commonly cited 20 million‑row limit for a single table by analyzing page size, B+‑tree structure, storage overhead, and how leaf and index pages combine to estimate maximum row counts.
Online sources often claim that a MySQL single table can hold about 20 million rows. This article explains how that figure is derived by examining MySQL’s 16 KB page structure and B+‑tree storage.
Page Layout and Storage Areas
Each 16 KB data page contains User Records and Free Space . The fixed overhead (file header, page header, min/max record pointers, checksum) occupies 128 B. The page directory size varies; we assume it uses 896 B. Therefore, the usable space for records and free space is roughly 15 KB per leaf page.
Estimating Rows per Leaf Page
If a single row occupies 1 KB, a leaf page can store about 15 rows.
Index Page Details
Index pages store only the primary‑key (bigint, 8 B) and the page number (4 B), totaling 12 B per entry. With a 15 KB index page, about (15 KB × 1024) / 12 ≈ 1280 index entries can be stored.
Estimation Formula
The total number of rows a table can hold can be approximated by:
where:
X : number of index entries a non‑leaf (index) node can store
y : number of rows a leaf node can store
Z : height of the B+‑tree
Example Calculations
• With X = 1280, y = 15, Z = 2, the estimated capacity is about 20 000 rows.
• With X = 1280, y = 15, Z = 3, the capacity rises to roughly 250 000 rows.
Impact of B+‑Tree Height
Increasing the B+‑tree height dramatically raises capacity. A 3‑level tree can store up to tens of millions of rows, and a 4‑level tree can handle even larger datasets. Typically, a 3‑level B+‑tree requires only three I/O operations for a query, offering a good balance of performance and size.
Effect of Row Size
If each row is 1 KB, a leaf page of 500 KB can store about twice as many rows, yielding roughly 50 million rows for a 3‑level tree. With a 250 KB row size, capacity can reach about 100 million rows while still maintaining three I/O reads.
Thus, the “2 000 万 (20 million) rows” guideline stems from typical assumptions about page size, row size, and a three‑level B+‑tree structure.
Lobster Programming
Sharing insights on technical analysis and exchange, making life better through technology.
How this landed with the community
Was this worth your time?
0 Comments
Thoughtful readers leave field notes, pushback, and hard-won operational detail here.