How Many Rows Can a Single MySQL Table Really Hold? Detailed B+‑Tree Calculations

01 Theory Overview

Alibaba's Java development manual suggests a maximum of 20 million rows per table, while other sources claim up to 100 million, but neither accounts for concrete scenarios. This article aggregates the underlying theory and applies it to practical cases.

B+ Tree Basics

MySQL uses a B+‑tree for its index structures. Key points:

Non‑clustered index: both leaf and internal nodes store index pointers.

Clustered index: internal nodes store pointers, leaf nodes store the actual row data in order.

InnoDB typically keeps the B+‑tree height within three levels.

The following diagram (clustered index, three‑level B+‑tree) illustrates the structure:

Page Storage

Each B+‑tree node is stored in a page of 16 KB. The page layout consists of:

File Header – 38 bytes

Page Header – 56 bytes

Infimum & Supremum – 26 bytes

User Records – variable

Free Space – variable

Page Directory – variable

File Trailer – 8 bytes

Usable space for user records is therefore:

15/16 × 1024 KB – 128 bytes = 15 232 bytes per page

Row Storage Formats

InnoDB supports COMPACT (default up to MySQL 5.6) and DYNAMIC (default from 5.7). Row layout includes:

Row header – 5 bytes

Variable‑length field list – variable

NULL‑value bitmap – variable (1 bit per nullable column)

Transaction ID + rollback pointer – 6 + 7 bytes

Actual column data – variable

02 Leaf‑Node Calculation

Maximum data in a three‑level B+ tree

With three levels, the maximum number of records is x² × y, where x is the number of pointers per internal node and y is the number of records per leaf page.

Pointer size

Assuming a BIGINT primary key (8 bytes) plus a 6‑byte internal pointer and a 5‑byte row header, each index entry occupies 19 bytes.

Given 15 232 bytes usable per page, a page can store roughly 15232 / 19 ≈ 801 index entries. After reserving space for the page directory (≈ 14 bytes), the effective count is about 787 entries per page.

BIGINT primary key → 787² = 619 369 leaf nodes

INT primary key (4 bytes) → pointer size 13 bytes → 993² = 986 049 leaf nodes

These figures are approximate; some sources report up to 1.6 million leaf nodes for BIGINT keys.

03 Total Record Count

Minimum records (assuming 2 rows per leaf)

Each leaf page can hold at least two rows of ~8 KB, giving a lower bound of about 2 × 986 049 ≈ 1.24 million rows for an INT primary key.

Maximum records (dense packing)

Example table definition:

CREATE TABLE `course_schedule` (
  `id` int NOT NULL,
  `teacher_id` int NOT NULL,
  `course_id` int NOT NULL,
  PRIMARY KEY (`id`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

Row size calculation: 4 + 4 + 4 + 6 + 7 + 5 = 30 bytes. Each leaf page can store 15232 / 30 ≈ 507 rows; accounting for the directory reduces this to 502 rows per leaf.

Thus the maximum record count for an INT primary key is 502 × 986 049 ≈ 5 × 10⁸ rows.

04 Practical Scenario – Blog Table

Consider a realistic blog table with many columns (BIGINT IDs, VARCHAR titles, timestamps, etc.). Detailed size breakdown:

Row header: 5 bytes

Variable‑length field list (title 1 byte + description 2 bytes): 3 bytes

NULL bitmap (3 nullable columns): 1 byte

Transaction ID + rollback pointer: 13 bytes

BIGINT columns (id, author_id, school_code): 24 bytes

DATETIME columns (create_time, release_time, modified_time): 24 bytes

TINYINT columns (status, is_delete): 2 bytes

CHAR(32) cover_image: 32 bytes

VARCHAR(50) + VARCHAR(250) (average 70% Chinese, 25% English, 5% emoji): 765 bytes

Total per row ≈ 869 bytes. Each leaf page can therefore store 15232 / 869 ≈ 17 rows, still 17 after accounting for the directory.

Maximum total rows for a BIGINT primary key: 17 × 619 369 ≈ 10.5 million.

05 Conclusion

The calculations above synthesize knowledge from two Chinese blogs ("小白 Debug" and "掘金阿杆"). While the methodology is sound, the exact numbers should be treated as estimates; real‑world results may vary.

References:

https://juejin.cn/post/7165689453124517896

https://mp.weixin.qq.com/s/AOtXJNmt50KowkSDN-FlhA