Why a MySQL Table Can Hold 100 Million Rows Without Slowing Down

Database Single Table Row Limit

Many developers hear the rule of "maximum 2 million rows per table" and assume performance will degrade beyond that. In practice the limit depends on the primary‑key type and InnoDB storage layout.

Primary‑Key Size and Theoretical Limits

The primary key defines the maximum number of rows because it is stored in the index. An INT (32‑bit) primary key can hold up to 2^32‑1 ≈ 21 billion values. A BIGINT (64‑bit) can hold 2^64‑1, but disk space usually becomes the bottleneck long before that. A TINYINT (8‑bit) primary key caps the table at 255 rows.

CREATE TABLE `user` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT COMMENT 'primary key',
  `name` varchar(100) NOT NULL DEFAULT '' COMMENT 'name',
  `age` int(11) NOT NULL DEFAULT '0' COMMENT 'age',
  PRIMARY KEY (`id`),
  KEY `idx_age` (`age`)
) ENGINE=InnoDB AUTO_INCREMENT=100037 DEFAULT CHARSET=utf8;

If the primary key is TINYINT, inserting a row with id=256 fails with Out of range value for column 'id', confirming the 255‑row limit.

InnoDB Page Structure

InnoDB stores data in 16 KB pages inside an .ibd file (the tablespace). Each page has a header, a footer, and a page directory, leaving roughly 15 KB for records or index entries.

B+Tree Index Structure

Indexes use a B+Tree. Leaf pages store the actual rows; non‑leaf pages store only the primary‑key value and the child‑page number. The tree’s fan‑out (number of child pointers per non‑leaf node) is high because a 16 KB page can hold about 1 280 (12 bytes per entry) pointers.

With a two‑level B+Tree (leaf level = 0), the maximum row count is (x^(z‑1)) * y, where x is fan‑out, y is rows per leaf page, and z is tree depth.

Assuming 15 KB usable per page, a leaf row of 1 KB yields y = 15. With x = 1 280 and z = 2, the table can hold roughly 2 × 10⁴ rows. With three levels ( z = 3) it can hold about 2.5 × 10⁴ rows, which matches the common “2 million‑row” recommendation.

If each row is only 250 bytes, a leaf can store about 60 rows, and a three‑level B+Tree can accommodate around 1 billion rows while still requiring at most three disk I/O operations per query.

B‑Tree vs. B+Tree

MySQL uses B+Tree indexes. A classic B‑Tree stores data in both leaf and non‑leaf nodes, reducing fan‑out and increasing tree depth. For the same data volume, a B‑Tree may need six levels, resulting in six disk I/O operations, whereas a B+Tree needs only three.

Conclusion

B+Tree leaf pages store actual rows; non‑leaf pages store primary‑key and child‑page pointers.

Typical B+Tree depth is two to three levels, giving at most 1–3 disk I/O per query.

B‑Tree depth grows faster, leading to more I/O; therefore B+Tree is preferred for MySQL indexes.

The 2 million‑row guideline is a recommendation based on keeping B+Tree depth low; exceeding it may increase depth and affect performance.

Absolute limits are also constrained by primary‑key size and available disk space.