The slow behavior of SELECT COUNT(*) in MySQL 8.0 has been fixed in 8.0.37!
This is Onkai Yuta (@fat47) from the Service Reliability Group (SRG) of the Media Headquarters.
#SRG(Service Reliability Group) mainly provides cross-sectional support for the infrastructure of our media services, improving existing services, launching new ones, contributing to OSS, etc.
This article describes how the well-known issue of SELECT COUNT(*) slowing down in MySQL 8.0 has been 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 appropriate action 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 that the behavior of SELECT COUNT(*) has been 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 cases.
The degradation you observed is due to SELECT COUNT() is using the clustered index instead of a secondary index.
This issue is fixed in 8.0.37 bug #112767 SELECT COUNT() Please try it with 8.0.37 and if you still face the issue, please file a new bug.
Let's look at the release notes again and look at the part #112767.
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 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 issue, we will follow the reproduction steps in the bugs.mysql file below.
Creating tables and populating them with data
When executing SELECT COUNT(*) on this table, it took about 0.04 seconds in MySQL 5.7.
In a MySQL 8.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 compatible version with Aurora MySQL 3.07.0 as of June 2024.
SELECT COUNT is still slow in this version.
I will 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 in MySQL 5.7, but on MySQL 8.0 it took 38 seconds.
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 implemented in 8.4 as well.
I checked the release notes and found a similar 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 just to be sure, and it was definitely faster.
Conclusion
Since the release of MySQL 8.0, it has been well-known that SELECT COUNT(*) has become slow, but now it has finally been improved!
This is not currently supported by Aurora MySQL, so I hope a compatible version will be released soon!
SRG is looking for people to work with us. If you are interested, please contact us here.