MySQL 5.7 → 8.0 Upgrade: Performance Degradation Analysis and Optimization

1 Background and Analysis

A customer upgraded from MySQL 5.7 to 8.0 and observed a significant slowdown in query performance. The slowdown was traced to changes in MySQL 8.0’s ORDER BY‑related parameters that caused the optimizer to become ineffective.

Analysis Process

The slow‑log and table schema were examined. The problematic SQL was:

select * from xx where xx order by xx limit xx

The execution plan showed that the ORDER BY column lacked an index; adding an index could reduce execution time from four seconds to milliseconds.

In MySQL 5.7 the same query took about one second, while in MySQL 8.0 the same query took four seconds when selecting all columns. Profiling revealed that MySQL 5.7 spent most time in the Creating sort index phase, whereas MySQL 8.0 spent the time in the execution phase.

MySQL documentation indicates that versions prior to 8.0.20 used the max_length_for_sort_data parameter to decide between row‑id sorting and full‑field sorting. After 8.0.20 this parameter was deprecated, and sorting is now decided dynamically based on the size of the fields involved.

2 Verification Test

The test used MySQL 5.7.44 and MySQL 8.0.30. Data preparation involved creating a table with 4 million rows:

CREATE TABLE `t` (
  `id` int NOT NULL AUTO_INCREMENT,
  `create_date` datetime DEFAULT NULL,
  `status` int DEFAULT NULL,
  `col1` varchar(50) DEFAULT NULL,
  ...
  PRIMARY KEY (`id`)
) ENGINE=InnoDB;

Two SELECT statements were executed on both versions:

-- Query three columns
select id, create_date, status from t where status=1 order by create_date desc limit 1;

-- Query all columns
select * from t where status=1 order by create_date desc limit 1;

Results:

MySQL 5.7: both queries ~1 second.

MySQL 8.0: three‑column query ~1 second, all‑column query ~4 seconds.

Increasing the number of selected fields in MySQL 8.0 further increased execution time. Setting max_length_for_sort_data to a value larger than all column sizes in MySQL 5.7 also caused the query to slow down.

3 Conclusion and Optimization

From MySQL 8.0.20 onward, the optimizer no longer relies on max_length_for_sort_data for unordered sorts; it dynamically decides based on field size, which can make queries with many columns slower than in 5.7.

The most effective solution is to create an index on the ORDER BY column.

References

[1] order‑by‑optimization