About SRGArticlesRecruitment Information
About SRGArticlesRecruitment Information
HOME/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) mainly provides cross-sectional support for the infrastructure of our media services, improving existing services, launching new ones, contributing to OSS, etc.
This article summarizes ways to speed up SELECT COUNT, which has become slower 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 insteadThis 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 preload pages for parallel reading threadsClustered indexes read more pages than necessary, causing excessive read I/O and poor SELECT COUNT performance.I found out that this is the case.
 
If you do not allow SELECT COUNT to use parallel scan of the clustered index (PK index),SELECT COUNT slowdown can be avoidedIt seems that this is the case.
  • Secondary indexes must be specified using hint clauses (USE INDEX or FORCE INDEX).
  • Condition required in WHERE clause
 

Verification of speedup

 

Verification table (1 million records)

COUNT result for MySQL5.7

0.37秒
 

MySQL8.0 COUNT result

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 tallying up the number of pages in the buffer pool for each index on a specified table, we can check whether the indexes are actually being used and whether data is stored in the buffer pool.
 
PRIMARY
 

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

When I ran it with a WHERE clause condition added, 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.
 

[Solved] 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,There is a chance that we can save it somehowI found out that there is.
      This can be solved if you can upgrade to the latest version of MySQL 8.0.
      I think there are many people who cannot use the latest MySQL 8.0, including Aurora MySQL, for various reasons.
      I hope this information reaches people like that!
      SRG is looking for people to work with us. If you are interested, please contact us here.