Why MySQL Tables Slow Down After 20 Million Rows and How to Estimate Safe Limits

Background

Many MySQL practitioners hear the rule of thumb that a single table should not exceed 20 million rows, but the origin of this figure is rarely explained. The article treats the table as a "car" and pushes the limit to see what really happens.

Experiment

First, a simple person table is created:

CREATE TABLE person(
  id int NOT NULL AUTO_INCREMENT PRIMARY KEY COMMENT 'primary key',
  person_id tinyint NOT NULL COMMENT 'user id',
  person_name VARCHAR(200) COMMENT 'user name',
  gmt_create datetime COMMENT 'creation time',
  gmt_modified datetime COMMENT 'modification time'
) COMMENT 'person info table';

One row is inserted, then a user‑defined variable @i is used to generate a pseudo‑column rownum. By repeatedly executing a SELECT‑INSERT loop that doubles the row count (2^20 ≈ 1 million, 2^23 ≈ 8 million, etc.), massive test data can be generated quickly. Adding a WHERE id > … clause allows finer control of the inserted volume.

When the row count approaches 8–10 million, MySQL may raise The total number of locks exceeds the lock table size. Increasing temporary table size and InnoDB buffer pool resolves the error:

SET GLOBAL tmp_table_size = 512*1024*1024;   -- 512 MB
SET GLOBAL innodb_buffer_pool_size = 1*1024*1024*1024;   -- 1 GB

Single‑Table Row Limit

The maximum number of rows is bounded by the primary‑key data type. An INT primary key (32 bit) caps at ~2.1 billion rows; a BIGINT (64 bit) caps at ~1.8×10^19 rows, which is far beyond practical limits because other resources (disk, memory, I/O) become bottlenecks first.

Using an unsigned BIGINT yields a theoretical maximum of 18,446,744,073,709,551,615 rows, which would take centuries to fill at one insert per second.

Table Space and Page Structure

InnoDB stores data in 16 KB pages inside an .ibd file. Each page consists of a fixed header/footer and a variable‑size data area. Only about 15 KB per page is usable for rows or index entries.

Index pages also use 16 KB pages, holding the minimum primary‑key value and the page number for each child page. The index structure is a B+ tree: leaf pages store actual rows, non‑leaf pages store pointers.

Data Page Layout

A page is divided into seven sections (file header, page header, infimum/supremum, file trailer, page directory, free space, and user records). New rows are allocated from the free‑space area and moved into the user‑records area. When free space is exhausted, a new page is allocated.

Index Structure

Index pages mirror the data‑page layout but store BIGINT primary keys (8 bytes) and page numbers (4 bytes), so each index entry occupies 12 bytes. With ~15 KB usable space, an index page can hold roughly 1,280 entries ( 15*1024/12 ≈ 1280).

Estimating Maximum Row Count

Assuming a three‑level B+ tree (typical for large tables), the total row capacity can be approximated as: Total = x^(z‑1) * y where x is the number of index entries per non‑leaf page (≈ 1,280), z is the tree depth (3), and y is the number of rows per leaf page. If each row occupies about 1 KB, a leaf page holds ~15 rows, giving:

Total = 1280^(3‑1) * 15 ≈ 24,576,000 rows (≈ 2.45 million)

Adjusting the row size changes y. For 5 KB rows, a leaf page holds ~3 rows, yielding:

Total = 1280^(3‑1) * 3 ≈ 4,915,200 rows (≈ 5 million)

Thus the commonly cited 20 million‑row recommendation aligns with a three‑level B+ tree and typical row sizes.

Conclusion

MySQL tables are stored as 16 KB pages; not all space is usable for data.

Index pages and data pages share the same page size, but index entries are much smaller.

The B+ tree depth determines how many pages must be read for a lookup; keeping the depth ≤ 3 maintains acceptable I/O.

Therefore, a practical upper bound of around 20 million rows (2 × 10^7) is reasonable for many workloads, though actual limits depend on row size, hardware, MySQL version, and query patterns.