The issue where SELECT COUNT(*) was slow in MySQL 8.0 was resolved in version 8.0.37!
This is Yuta Kikai (@fat47) from the Service Reliability Group (SRG) of the Media Division.
#SRGThe Service Reliability Group primarily provides comprehensive support for the infrastructure surrounding our media services, focusing on improving existing services, launching new ones, and contributing to open-source software (OSS).
This article describes how the well-known issue of SELECT COUNT(*) being slow in MySQL 8.0 has been resolved in MySQL 8.0.37.
I hope this is of some help.
SELECT COUNT(*) becomes slow in MySQL 8.0.Has the slowness issue in MySQL 8.0.37 been resolved?!Operation verificationWhat about LTS MySQL 8.4?In conclusion
SELECT COUNT(*) becomes slow in MySQL 8.0.
It's a well-known fact that SELECT COUNT(*) is slow in MySQL 8.0.
I have encountered this problem several times while actually upgrading to Aurora MySQL Version 3 (MySQL 8.0 compatible).
Until now, it was necessary to either modify the query to stop using SELECT COUNT or to take action on the application side.
However, several years after the release of MySQL 8.0, this issue appears to have been resolved in the latest version of MySQL 8.0.
Has the slowness issue in MySQL 8.0.37 been resolved?!
MySQL 8.0.37 was released in April 2024.
However, no matter how much I look at the release notes, there is no mention whatsoever of how the behavior of SELECT COUNT(*) has been resolved.
However, if you look at the pages 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 Verification Team
Thank you for your feedback and test cases.
The degradation you observed is due to SELECT COUNT(This may be caused by the use of a clustered index instead of a secondary index.
This issue is bug #112767 in 8.0.37: SELECT COUNT(This has been fixed in version 8.0.37. Try it out and if you still encounter the problem, please report the new bug.
Let's take another look at the release notes and examine section #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 that use secondary index scans. Additionally, it now provides index hints that force the use of clustered indexes. (Bugs #100597, #112767, #31791868, #35952353)
References: This issue is a regression: A regression of Bug #12978.
It appears that this fix resolves the slowness of SELECT COUNT(*).
Operation verification
The reproduction procedure was found in the bugs.mysql file below, so we will use that method to verify the issue.
Table creation and data generation
Executing SELECT COUNT(*) on this table took approximately 0.04 seconds in MySQL 5.7.
In a MySQL 8.0.28 environment, the process now takes 0.8 seconds (approximately 20 times longer), as shown below.
Let's try upgrading this to MySQL 8.0.36.
This version is compatible with the latest Aurora MySQL 3.07.0 as of June 2024.
SELECT COUNT remains slow in this version as well.
I'll try upgrading to MySQL 8.0.37.
As of June 2024, no version of Aurora MySQL compatible with this version has been released.
We were able to confirm that the response time has become faster.
Furthermore, I have also observed a slowdown in SELECT COUNT in the actual service I am in charge of.
In your case, it took about 0.6 seconds with MySQL 5.7, but now it takes as long as 38 seconds with MySQL 8.0.
The service was in a state where it was difficult to continue.
Similarly, after upgrading this environment to MySQL 8.0.37, we confirmed that the response time improved to the level of version 5.7.
What about LTS MySQL 8.4?
Is this improvement included in the LTS version of MySQL 8.4?
[16 Feb 22:07] Philip Olson
It appears that improvements have also been incorporated into version 8.4.
Upon checking the release notes, I found the same description.
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 did a test run, and it was indeed faster.
In conclusion
It was a well-known issue that SELECT COUNT(*) was slow after the release of MySQL 8.0, but finally, improvements have been made!
Aurora MySQL doesn't currently support this, so I hope a version with support will be released soon!
SRG is looking for new team members.
If you are interested, please contact us here.