About SRGTechnical ArticlesRecruitment Information
About SRGTechnical ArticlesRecruitment Information
HOME/Technical Articles/Speeding up SELECT COUNT, which has become slow in MySQL 8.0

Speeding up SELECT COUNT, which has become slow in MySQL 8.0

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 summarizes how to speed up SELECT COUNT, which has become slow in MySQL 8.0, without upgrading.
I hope this helps in some way.
 

MySQL8.0 SELECT COUNT slowdown problem

In MySQL 8.0, SELECT COUNT can be very slow.
There are cases where it is several dozen times slower than MySQL 5.7.
 
SELECT COUNT result in MySQL 5.7.41
 
SELECT COUNT result in MySQL8.0.28
This issue has been resolved in MySQL 8.0.37 and later (including MySQL 8.4 and MySQL 9.0).
However, there are many environments where it is not yet possible to upgrade to the latest MySQL, such as when using Aurora MySQL Version 3.
 

Root cause of SELECT COUNT slowness

Let's take another look at the release notes for MySQL 8.0.37.
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)
📕
InnoDB: MySQL no longer ignores optimizer hints to use a secondary index scan, which would force a clustered (parallel) index scan instead. (Bug #100597, Bug #112767, Bug #31791868, Bug #35952353)
 

Bug number: 100597

This is in SELECT COUNT.Even if a hint clause is added, the secondary index is not used and the clustered index (PK index) is used instead.This was a bug.
The EXPLAIN result shows that the secondary index is used, but it is not actually used.Used only when adding a WHERE clauseIt was reported that:
 

Bug number: 112767

The ability to pre-load pages for parallel reading threadsClustered indexes read more pages than necessary, causing excessive read I/O and degrading SELECT COUNT performance.I found out that this is the case.
 
If you do not use parallel scan of clustered index (PK index) in SELECT COUNT,SELECT COUNT slowdown can be avoidedIt seems that this is the case.
  • You must specify a secondary index using a hint clause (USE INDEX or FORCE INDEX).
  • Condition required in WHERE clause
 

Verification of speedup

 

Validation table (1 million records)

COUNT result for MySQL5.7

0.37秒
 

COUNT result for MySQL8.0

7.65秒
 
idx
 
Check the index statistics and buffer pool usage.
PRIMARY
The above query checks the contents of mysql.innodb_index_stats and information_schema.innodb_buffer_page.
  • mysql.innodb_index_stats
    • InnnoDB index statistics
  • information_schema.innodb_buffer_page
    • Buffer pool page information
By aggregating the number of pages in the buffer pool for each index of the specified table, we can check whether the index is actually being used and whether data is stored in the buffer pool.
 
PRIMARY
 

Adding id > 0 condition to WHERE clause in COUNT of MySQL8.0

When I added a WHERE clause condition and ran it, it became slightly faster.
 
PRIMARY
 
Of course, index statistics and buffer pool remain unchanged.

Add the condition id > 0 to the WHERE clause of COUNT in MySQL8.0 + specify "idx" in FORCE INDEX

Adding FORCE INDEX sped it up to 0.85 seconds.
 
Using Index for skip scan
 
Check the index statistics and buffer pool usage.
idx
 
col_idxカラム
I'll try to improve this part further.
 

[Solution] Specify the appropriate index using the appropriate WHERE clause condition + FORCE INDEX for COUNT in MySQL 8.0

In this sample table example, the following two patterns are possible:
  • 0.44秒
     
    • 0.54秒

      Conclusion

      SELECT COUNT has become slow,Possibility of saving somehowWe found that there is.
      This can be solved if you can upgrade to the latest version of MySQL8.0.
      There are probably many people who cannot use the latest MySQL 8.0 for various reasons, including Aurora MySQL.
      I hope this information reaches people like that!
      SRG is looking for people to work with us. If you're interested, please contact us here.