Fixing Slow Queries After MySQL 5.7 to 8.0 Upgrade

Recently, a client upgraded their MySQL database from version 5.7 to 8.0, but noticed a significant slowdown in query performance after the upgrade. After preliminary checks, it was found that changes to some parameters in MySQL 8.0 might have affected the optimizer. Join me in analyzing this fault and I’d suggest readers who’ve done similar upgrades check their systems.

Fault Analysis Process

First, I checked the slow query logs and corresponding table structures. A slow SQL query was identified: select * from xx where xx order by xx limit xx. The execution plan showed that the fields used in the ORDER BY clause didn’t have an index. Adding an index could reduce the query time from around 4 seconds to milliseconds.

But in MySQL 5.7 prior to the upgrade, even without an index on this field, the query only took about 1 second. This discrepancy needed to be investigated. By comparing the execution plans before and after the upgrade using profiling and tracing methods, it became clear that there was a significant difference in the profiling data. While MySQL 5.7 spent most of its time in the Creating sort index phase, MySQL 8.0’s time was consumed during the execution phase.

Furthermore, in MySQL 8.0, the time taken for SELECT operations with a few fields was around 1 second. As the number of selected fields increased, the time grew longer, reaching 4 seconds when selecting all fields (select *), whereas MySQL 5.7 maintained a time of around 1 second regardless of the number of fields.

We can infer that the slowdown is primarily due to the sorting process, and we need to understand what changes have been made to the sorting mechanism in MySQL 8.0.

According to the MySQL official documentation, in versions before MySQL 8.0.20, the sorting mechanism was related to the max_length_for_sort_data parameter. If the size of the rows to be sorted exceeds the value set for this parameter (in bytes), the sorting would be based on the row_id. Otherwise, it would be a full-field sort. Through testing, it was found that in MySQL 5.7, if the parameter value is set larger than the size of all columns, a select * query would also take about 4 seconds.

In MySQL 8.0.20 and later versions, the max_length_for_sort_data parameter has been deprecated and no longer takes effect. This is a significant change that could explain the performance difference.

After the analysis, we need to validate our findings.

Verification Test

The specific versions involved in this upgrade are MySQL 5.7.44 and MySQL 8.0.30. According to the analysis, MySQL 5.7.44 would be affected by the max_length_for_sort_data parameter in sorting queries, while MySQL 8.0.30 would not.

Data Preparation

A table was created in both MySQL 5.7 and 8.0 databases and populated with 4 million rows of data.

1CREATE TABLE `t` (
2  `id` int NOT NULL AUTO_INCREMENT,
3  `create_date` datetime DEFAULT NULL,
4  `status` int DEFAULT NULL,
5  `col1` varchar(50) DEFAULT NULL,
6  `col2` varchar(50) DEFAULT NULL,
7  `col3` varchar(50) DEFAULT NULL,
8  `col4` varchar(50) DEFAULT NULL,
9  `col5` varchar(50) DEFAULT NULL,
10  `col6` varchar(50) DEFAULT NULL,
11  `col7` varchar(50) DEFAULT NULL,
12  `col8` varchar(50) DEFAULT NULL,
13  `col9` varchar(50) DEFAULT NULL,
14  `col10` varchar(50) DEFAULT NULL,
15  `col11` varchar(255) DEFAULT NULL,
16  `col12` varchar(255) DEFAULT NULL,
17  `col13` varchar(255) DEFAULT NULL,
18  `col14` varchar(255) DEFAULT NULL,
19  `col15` varchar(255) DEFAULT NULL,
20  `col16` varchar(255) DEFAULT NULL,
21  `col17` varchar(255) DEFAULT NULL,
22  `col18` varchar(255) DEFAULT NULL,
23  `col19` varchar(255) DEFAULT NULL,
24  `col20` varchar(255) DEFAULT NULL,
25  PRIMARY KEY (`id`)
26) ENGINE=InnoDB;
27
28-- The data insertion process is omitted.
29
30select count(*) from `t`;
31+-------------+
32|  count( * ) |
33+-------------+
34|     4194304 |
35+-------------+
361 row in set (0.11 sec)

‍

In both MySQL 5.7 and 8.0 environments (with consistent parameter configurations), two types of SELECT statements were executed: querying three fields and querying all fields.

1-- Query three fields
2select id,create_date,status from t  where status=1 order by create_date desc limit 1;
3
4-- Query all fields
5select * from t  where status=1 order by create_date desc limit 1;

‍

In MySQL 5.7, both SELECT statements took about 1 second to execute.

In MySQL 8.0, querying three fields took around 1 second, while querying all fields took about 4 seconds. The more fields queried in MySQL 8.0.30, the longer the time taken.

If the max_length_for_sort_data parameter value is set larger than the size of all columns in MySQL 5.7.44, the query time will also slow down (full-field sorting).

Conclusion

In MySQL 8.0.20 and later versions, the sorting mechanism for unindexed queries has changed. It no longer determines the sorting method based on the max_length_for_sort_data parameter but instead dynamically sorts based on the size of the fields being queried and the sorting fields. This can result in slower performance than MySQL 5.7 when querying a large number of columns.

The best solution is to add an index to the sorting field.

‍