Why Keep a MySQL Table Under 20 Million Rows? Primary Key & Data Page Insights

Introduction

As a qualified DBA, when a single table exceeds ten million rows we usually recommend sharding to reduce size. The article explores the underlying reasons why a single table should not exceed about twenty million rows.

Interview scenario

In a Tencent technical interview the candidate is asked about sharding practices. He describes a case where a system stores over 50 million daily operation logs by automatically generating three tables.

MySQL single table should not exceed 20 million rows; only when rows exceed 5 million or size exceeds 2 GB is sharding recommended.

1. Auto‑Increment Primary Key Perspective

If the primary key is an INT, its maximum value is 2³²‑1 (≈2.1 billion). For BIGINT the limit is 2⁶⁴‑1, far beyond practical disk capacity. For TINYINT the limit is 255, causing an error at 256. Thus the primary‑key type defines a theoretical row ceiling.

2. Data Page Perspective

In InnoDB each table is stored in an *.ibd file divided into 16 KB pages. After accounting for page number, pointers, directory and checksum, roughly 15 KB remains for actual rows. Assuming a BIGINT primary key (8 bytes) and a 4‑byte page number, each row occupies 12 bytes of index, giving about 1 280 index entries per page (X = 15 KB / 12 B).

If a typical row size is 1 KB, each page can hold about 15 rows (Y = 15 KB / 1 KB).

Using a B+‑tree, the total record capacity M = X^(N‑1) × Y, where N is the tree height.

Two‑level tree (N=2): M ≈ 1 280 × 15 ≈ 2 × 10⁴ rows.

Three‑level tree (N=3): M ≈ 1 280² × 15 ≈ 2.5 × 10⁶ rows.

Four‑level tree (N=4): M ≈ 1 280³ × 15 ≈ 3 × 10⁹ rows.

These calculations assume 1 KB rows and no fragmentation; the practical recommendation is to keep a single table around 20 million rows.

3. Thought Exercise

Question: With a four‑level B+‑tree, a BIGINT primary key, and 1 KB rows, how many records can be stored?

Answer: X = 1 280, Y = 15, N = 4 → M = 1 280³ × 15 ≈ 3 × 10⁹ records.

Conclusion

The article concludes that, based on primary‑key limits and data‑page storage analysis, a single MySQL table should ideally stay below twenty million rows, though the exact threshold depends on row size and other factors.