Source: Is 20M of rows still a valid soft limit of MySQL table in 2023?
Summary
Rumors state that MySQL tables with over 20 million rows lead to poor performance. This experiment about MySQL 8.0.25 on an AWS db.r5.large instance with General Purpose SSD storage and a Linux AWS t2.micro client tests the query runtime for 9 tables created with the same schema but increasing number of rows from 100k to 60m.
Fact
💾 MySQL uses innodb_buffer_pool to cache data pages, and the query runtime of its first execution takes longer time because buffer pool is empty. After a table has been run once, following queries are faster because the data is stored in the pool and can be accessed in memory. Selecting the count of all rows in a table leads to full table scan, which is poor performance-wise for MySQL when compared to searches run by index.