About SRGTechnical articleRecruitment Information
About SRGTechnical articleRecruitment Information
⚠️⚠️⚠️ STG SRG Portal ⚠️⚠️⚠️/Technical article/Speeding up SELECT COUNT, which became slow in MySQL 8.0.

Speeding up SELECT COUNT, which became slow in MySQL 8.0.

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 summarizes how to speed up the SELECT COUNT operation in MySQL 8.0 without upgrading the version.
I hope this is of some help.
 

MySQL8.0 SELECT COUNT slowdown problem

In MySQL 8.0, SELECT COUNT can be extremely slow.
In some cases, it can be tens of times slower than MySQL 5.7.
 
SELECT COUNT results in MySQL 5.7.41
 
SELECT COUNT results in MySQL 8.0.28
This issue has been resolved in MySQL 8.0.37 and later versions (including MySQL 8.4 and MySQL 9.0).
However, there are many environments, such as those using Aurora MySQL Version 3, where upgrading to the latest MySQL version is not yet possible.
 

The root cause of the slowdown in SELECT COUNT

Let's take another look at the MySQL 8.0.37 release notes.
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 the optimizer hint to use secondary index scans. This previously forced clustered (parallel) index scans instead. (Bugs #100597, #112767, #31791868, #35952353)
 

Bug number: 100597

This is in SELECT COUNT,Even with a hint clause, the clustered index (the index of the primary key) is used instead of the secondary index.That was the bug.
The EXPLAIN results make it appear as though the secondary index is being used, but in reality, it is not.Used only when a WHERE clause is added.That was the report.
 

Bug number: 112767

The ability to preload pages for parallel reading threadsIn clustered indexes, loading more pages than necessary causes excessive read I/O, degrading the performance of SELECT COUNT.It was found that this is happening.
 
If you prevent parallel scanning of clustered indexes (PK indexes) using SELECT COUNT,SELECT COUNT slowdown can be avoidedIt seems safe to say that this is the case.
  • Specifying a secondary index is required using a hint clause (such as USE INDEX or FORCE INDEX).
  • The condition in the WHERE clause is mandatory.
 

Verification of speed improvements

 

Verification table (1 million records)

COUNT results in MySQL 5.7

0.37秒
 

COUNT results in MySQL 8.0

7.65秒
 
idx
 
Let's 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 verify whether the index is actually being used and whether data is present in the buffer pool.
 
PRIMARY
 

Adding the condition `id > 0` to the WHERE clause of the COUNT query in MySQL 8.0.

When I added a WHERE clause condition and ran the code, it became slightly faster.
 
PRIMARY
 
Of course, there are no changes to the index statistics or buffer pool.

In MySQL 8.0, add the condition `id > 0` to the WHERE clause of the COUNT statement and specify "idx" in the FORCE INDEX.

Adding FORCE INDEX reduced the time to 0.85 seconds.
 
Using Index for skip scan
 
Let's check the index statistics and buffer pool usage.
idx
 
col_idxカラム
Let's 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秒

      In conclusion

      The SELECT COUNT, which was delayed,There's a chance we can save them somehow.It turns out that there is.
      The problem could be solved by upgrading MySQL 8.0 to the latest version,
      Many people may not be able to use the latest MySQL 8.0 for various reasons, including those related to Aurora MySQL.