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 endorphine on Tuesday May 23, 2023 @02:02AM (#36040453)

Generally speaking, why does SELECT COUNT(*) takes so much? I'd expect that the database maintains internal bookkeeping structures with table metadata that contain the number of rows in each table.

I reckon this is probably not true? If so, is it because it keeping a counter like that up-to-date would be inefficient

Edit: I just realized I might be misunderstanding what that query does

[ Parent | on HN ]

Re:Is 20M of rows still a valid soft limit of MySQL table in... by ddorian43 on Tuesday May 23, 2023 @02:29AM (#36040651)

Learn about MVCC storage. You might have multiple running concurrent transactions. Which is the "true" row count? Hint: you have to count.

[ Parent | on HN ]

Re:Is 20M of rows still a valid soft limit of MySQL table in... by jl6 on Tuesday May 23, 2023 @03:12AM (#36040946)

Makes me wonder if anybody would find a SELECT APPROXIMATELY COUNT(*) useful, which would ignore the impact of current transactions.

[ Parent | on HN ]

Re:Is 20M of rows still a valid soft limit of MySQL table in... by qeternity on Tuesday May 23, 2023 @03:18AM (#36041003)

Many DB systems have some sort of HLL function to provide a similar approximation (although I think you’re overestimating the costs that MVCC impose on large datasets).

[ Parent | on HN ]

Re:Is 20M of rows still a valid soft limit of MySQL table in... by ddorian43 on Tuesday May 23, 2023 @07:48AM (#36042972)

Note that HLL is used for counting `unique` things. You don't need HLL for counting the rows of the table.

[ Parent | on HN ]

Re:Is 20M of rows still a valid soft limit of MySQL table in... by 15155 on Tuesday May 23, 2023 @07:45AM (#36042948)

While not "SELECT APPROXIMATELY COUNT (*)," both MySQL and PostgreSQL both offer various metadata tables with approximate (albeit completely unfiltered) total row counts.

[ Parent | on HN ]

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

I think you can use

Explain select count(*)

If you're only after a (update: really rough) rough value

[ Parent | on HN ]

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

Doesn't SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED do that? Or maybe a NOLOCK hint equivalent in MySQL?

[ Parent | on HN ]

Re:Is 20M of rows still a valid soft limit of MySQL table in... by clarkmcc on Tuesday May 23, 2023 @08:49AM (#36043501)

Seems like you could just count the number of primary key entries in the index.

[ Parent | on HN ]

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

Essentially what SELECT COUNT(*) does in InnoDB is choose the smallest index and fully scans that, in parallel if there's no WHERE clause.

Meanwhile the primary key is typically the largest index, since with InnoDB's clustered index design, the primary key is the table. So it's usually not the best choice for counting unless there are no secondary indexes.

As other commenters mentioned, the query also must account for MVCC, which means properly counting only the rows that existed at the time your transaction started. If your workload has a lot of UPDATEs and/or DELETEs, this means traversing a lot of old row versions in UNDO spaces, which makes it slower.

[ Parent | on HN ]