The slowness of SELECT COUNT(*) in MySQL 8.0 was fixed in 8.0.37!
This is Onkai Yuta (@fat47) from the Service Reliability Group (SRG) of the Media Headquarters.
#SRG(Service Reliability Group) is a group that mainly provides cross-sectional support for the infrastructure of our media services, improving existing services, launching new ones, and contributing to OSS.
This article describes how the notorious issue of SELECT COUNT(*) slowing down in MySQL 8.0 was resolved in MySQL 8.0.37.
I hope this helps in some way.
SELECT COUNT(*) is slow in MySQL 8.0The slowness has been resolved with MySQL8.0.37!Operation verificationWhat about LTS MySQL 8.4?Conclusion
SELECT COUNT(*) is slow in MySQL 8.0
It is well known that SELECT COUNT(*) is slow in MySQL 8.0.
In fact, we encountered this problem several times while upgrading to Aurora MySQL Version 3 (compatible with MySQL 8.0).
Until now, it was necessary to change the query and stop using SELECT COUNT, or take other measures on the application side.
However, several years have passed since the release of MySQL 8.0, and it appears that this issue has been resolved in the latest version of MySQL 8.0.
The slowness has been resolved with MySQL8.0.37!
MySQL 8.0.37 was released in April 2024.
However, no matter how much I look at the release notes, there is no mention of the behavior of SELECT COUNT(*) being resolved.
However, if you look at the page posted to bugs.mysql in 2019,
Bug #97709 MySQL 8 Select Count(*) is very slow
The following post was made in April 2024:
[30 Apr 16:31] MySQL Verification Team
[30 Apr 16:31] MySQL Testing Team
Thank you for your feedback and test case.
The degradation you observed is due to SELECT COUNT() is using the clustered index instead of a secondary index.
This issue is due to bug #112767 in 8.0.37, SELECT COUNT() fixed it. Please try it with 8.0.37 and if you still encounter the issue, please file a new bug.
Let's look at the release notes again and look at the #112767 section.
InnoDB: MySQL no longer ignores the optimizer hint to use a secondary index scan, which instead forced a clustered (parallel) index scan. In addition, added the ability to provide an index hint that forces use of a clustered index. (Bug #100597, Bug #112767, Bug #31791868, Bug #35952353) References: This issue is a regression of: Bug #12978.
InnoDB: MySQL no longer ignores optimizer hints to use secondary index scans. Additionally, the ability to provide index hints to force the use of a clustered index has been added. (Bug #100597, Bug #112767, Bug #31791868, Bug #35952353)
References: This issue is a regression: a regression of Bug #12978.
It seems that fixing this part has resolved the slowness of SELECT COUNT(*).
Operation verification
To verify the reproducibility, we will follow the reproduction steps in the bugs.mysql below.
Creating a table and populating it with data
When executing SELECT COUNT(*) on this table, it took about 0.04 seconds in MySQL 5.7.
In a MySQL8.0.28 environment, it took 0.8 seconds (about 20 times longer) as shown below.
Let's try upgrading this to MySQL 8.0.36.
This is the latest version compatible with Aurora MySQL 3.07.0 as of June 2024.
SELECT COUNT is still slow in this version.
I'll try upgrading to MySQL 8.0.37.
As of June 2024, there are no Aurora MySQL releases compatible with this version.
It was confirmed that the response was faster.
In addition, I have confirmed that SELECT COUNT is slowing down in the actual service I am in charge of.
On that site, it took about 0.6 seconds with MySQL 5.7, but it took 38 seconds with MySQL 8.0.
It was becoming difficult to continue providing services.
Similarly, when we upgraded this environment to MySQL 8.0.37, we confirmed that the response time had improved to the same level as 5.7.
What about LTS MySQL 8.4?
Does the LTS version of MySQL 8.4 include this improvement?
[16 Feb 22:07] Philip Olson
Apparently the improvements have been incorporated into 8.4 as well.
I checked the release notes and found the same statement.
InnoDB: MySQL no longer ignores the optimizer hint to use a secondary index scan, which instead forced a clustered (parallel) index scan. In addition, added the ability to provide an index hint that forces use of a clustered index. (Bug #100597, Bug #112767, Bug #31791868, Bug #35952353)
I checked the operation and it was indeed fast.
Conclusion
Since the release of MySQL 8.0, it has been well-known that SELECT COUNT(*) has become slow, but now there has finally been an improvement!
Aurora MySQL does not currently support this, so I hope a compatible version will be released soon!
SRG is looking for people to work with us.
If you're interested, please contact us here.