Why MySQL Single Tables Should Stay Below 20 Million Rows – The Real Reason

1. Background

In the backend community it is often said that a MySQL table should not exceed 20 million rows, otherwise query speed degrades. This article puts that rule to the test.

2. Experiment

Experiment 1

Create a simple table:

CREATE TABLE person(
  id int NOT NULL AUTO_INCREMENT PRIMARY KEY COMMENT '主键',
  person_id tinyint NOT NULL COMMENT '用户id',
  person_name VARCHAR(200) COMMENT '用户名称',
  gmt_create datetime COMMENT '创建时间',
  gmt_modified datetime COMMENT '修改时间'
) COMMENT '人员信息表';

Insert a row:

insert into person values(1,1,'user_1',NOW(),now());

Use a MySQL pseudo‑column to generate sequential numbers:

select (@i:=@i+1) as rownum, person_name from person, (select @i:=100) as init;
set @i=1;

Repeatedly insert data to generate millions of rows (each execution roughly doubles the row count):

insert into person(id, person_id, person_name, gmt_create, gmt_modified)
select @i:=@i+1,
       left(rand()*10,10) as person_id,
       concat('user_',@i%2048),
       date_add(gmt_create, interval + @i*cast(rand()*100 as signed) SECOND),
       date_add(date_add(gmt_modified, interval +@i*cast(rand()*100 as signed) SECOND),
                interval + cast(rand()*1000000 as signed) SECOND)
from person;

If the row count approaches 8‑10 million, MySQL may report “The total number of locks exceeds the lock table size”. Increase temporary table size and InnoDB buffer pool:

SET GLOBAL tmp_table_size = 512*1024*1024;  -- 512M
SET GLOBAL innodb_buffer_pool_size = 1*1024*1024*1024;  -- 1G

3. Single‑Table Row Limit

The maximum number of rows is limited by the size of the auto‑increment primary key. An unsigned BIGINT can hold up to 2^64‑1 (~1.8×10^19) rows, but practical limits appear far earlier due to storage and memory constraints.

4. Table Space

InnoDB stores data in .ibd files divided into 16 KB pages. Each page contains a header, trailer, and free space for rows.

Only about 15 KB of each page is usable for user data.

5. Page Data Structure

Each page records its predecessor/successor addresses, a page number, and a checksum. The page directory points to the actual rows.

6. Index Data Structure

InnoDB indexes are also 16 KB pages organized as a B+ tree. Non‑leaf pages store the minimum primary‑key value of the child page and the child page number; leaf pages store the actual rows.

A typical B+ tree for a table has two or three levels.

7. Recommended Single‑Table Size

Assuming a three‑level B+ tree with a branching factor of about 1 280 (15 KB / 12 B per index entry) and roughly 15 rows per leaf page (1 KB per row), the theoretical maximum rows are:

Two‑level tree: ~19 200 rows

Three‑level tree: ~24.6 million rows (≈2 kw)

If each row occupies 5 KB, a leaf page holds only three rows, reducing the maximum to about 5 million rows.

8. Summary

MySQL tables are stored as 16 KB pages that are not necessarily contiguous on disk.

Only part of each page is usable for row data; the rest stores metadata such as headers, footers, and checksums.

In a B+ tree, leaf nodes contain the actual rows, while internal nodes contain primary‑key values and page pointers.

The 20 million‑row recommendation keeps the B+ tree shallow, avoiding extra disk I/O and performance degradation.