Is 20M of rows still a valid soft limit of MySQL table in 2023?

Re:Is 20M of rows still a valid soft limit of MySQL table in... by grumple on Monday May 22, 2023 @09:01PM (#36038595)

You can safely go to a billion rows. Use indexes. Don't do silly things like count(*) or select *, select the small data sets you need using your indexes. If you need a full table count for some reason, use a primary key that auto increments and select the max, or use information_schema for an estimate. Nobody will sweat the slight inaccuracy on a table that size.

[ Parent | on HN ]

Re:Is 20M of rows still a valid soft limit of MySQL table in... by Guest9081239812 on Monday May 22, 2023 @09:15PM (#36038674)

Agreed. I have a handful of tables with over 2 billion rows and have experienced no issues. One database is 2.5TB in size. I feel like 20 million rows is a relatively small MySQL table.

[ Parent | on HN ]

Re:Is 20M of rows still a valid soft limit of MySQL table in... by mensetmanusman on Monday May 22, 2023 @09:31PM (#36038769)

What types of data with over two billion rows?

[ Parent | on HN ]

Re:Is 20M of rows still a valid soft limit of MySQL table in... by Guest9081239812 on Monday May 22, 2023 @09:44PM (#36038837)

It's related to user activity, along the lines of products a user views. The other large tables in the billions of rows are about analyzing how products are performing on other websites over time to view historical data along with past and current trends.

[ Parent | on HN ]

Re:Is 20M of rows still a valid soft limit of MySQL table in... by grumple on Tuesday May 23, 2023 @09:04AM (#36043662)

We have two tables at nearly 2 billion rows for line items on invoices (we aggregate data for a substantial portion of a major industry). We have ~20 tables that are over 100 million rows but less than 1 billion. Looks like another ~20 tables over 20 million.

To get this info on your own mysql db: `select * from information_schema.TABLES;`. As previously disclaimed, the TABLE_ROWS here are an estimate generally, see https://dev.mysql.com/doc/mysql-infoschema-excerpt/5.7/en/in...

[ Parent | on HN ]

Re:Is 20M of rows still a valid soft limit of MySQL table in... by pollux1997 on Monday May 22, 2023 @09:55PM (#36038894)

Could you provide some details, like MySQL version, IOPS and read/write performance?

[ Parent | on HN ]

Re:Is 20M of rows still a valid soft limit of MySQL table in... by grumple on Monday May 22, 2023 @10:10PM (#36038982)

Nice! We're at 9TB... I agree that 20M is small enough that I'm not concerned about performance at all.

[ Parent | on HN ]

Re:Is 20M of rows still a valid soft limit of MySQL table in... by twelve40 on Monday May 22, 2023 @09:11PM (#36038644)

yeah i was also under the 20mil impression initially until building a system that worked with a 500mil row table without any problems (just a beefy RDS instance, nothing crazy distributed or anything). Schema changes became a pain at that point, so i would probably split it if I were to do it again, but reads and writes never gave us much trouble.

[ Parent | on HN ]

Re:Is 20M of rows still a valid soft limit of MySQL table in... by newtom on Monday May 22, 2023 @09:59PM (#36038918)

check out gh-ost migrations: https://github.com/github/gh-ost

[ Parent | on HN ]

Re:Is 20M of rows still a valid soft limit of MySQL table in... by charrondev on Monday May 22, 2023 @10:48PM (#36039212)

What were you using for schema changes? I’ve been using pt-online-schema-change on InnoDB tables with 200m+ rows for a while, it’s just a bit slow.

[ Parent | on HN ]