Why MySQL Tables Should Stay Under 20 Million Rows: Theory and Calculations

1. Auto‑increment Primary Key Perspective

When a table uses an auto‑increment primary key, the data type determines the theoretical maximum number of rows: INT can hold up to 2^32‑1 (~2.1 billion) values, BIGINT up to 2^64‑1 (practically unreachable because the disk fills first), and TINYINT only up to 255.

2. Data Page Perspective

InnoDB stores a table in an .ibd file divided into 16 KB data pages. Each page contains a page number, forward/backward pointers, a checksum, a page directory, and the actual row data.

Data pages are linked together via a B+‑tree. Leaf pages hold the row records, while internal pages store index entries. Each level of the B+‑tree corresponds to one disk I/O.

The B+‑tree can be analyzed with the following variables:

X – number of child pointers per internal node (determined by usable space per page);

Y – number of rows that fit in a leaf page;

N – height of the tree (number of levels).

Using a 16 KB page, after subtracting metadata the usable space is about 15 KB. Assuming an 8‑byte BIGINT primary key and a 4‑byte page number, each pointer occupies 12 bytes, giving X ≈ 15 KB / 12 ≈ 1280. If a row occupies roughly 1 KB, a leaf page can store Y ≈ 15 rows.

With these numbers, the total row capacity M can be estimated as: M = X^(N‑1) × Y Examples:

Two‑level tree (N=2): M ≈ 1280^(1) × 15 ≈ 19 200 rows (≈ 2 × 10⁴).

Three‑level tree (N=3): M ≈ 1280^(2) × 15 ≈ 2.5 × 10⁶ rows.

Four‑level tree (N=4): M ≈ 1280^(3) × 15 ≈ 3 × 10⁹ rows.

In practice, because real rows are often larger than 1 KB and overhead exists, a safe guideline is to keep a single MySQL table under about 20 million rows.

3. Thinking Exercise

Question: For a four‑level B+‑tree with a BIGINT primary key and an average row size of 1 KB (ignoring fragmentation), how many rows can be stored?

Answer: Using the same calculations, X = 1280, Y = 15, N = 4, so M = 1280^(3) × 15 ≈ 3 × 10⁹ rows.