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.
Signed-in readers can open the original source through BestHub's protected redirect.
This article has been distilled and summarized from source material, then republished for learning and reference. If you believe it infringes your rights, please contactand we will review it promptly.
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.