Performance Analysis of MySQL 5.7 to 8.0 Upgrade and ORDER BY Optimization

A client upgraded MySQL from version 5.7 to 8.0 and noticed a dramatic slowdown in query performance, especially for ORDER BY operations.

The analysis reveals that MySQL 8.0 changed the handling of ORDER BY parameters. The legacy max_length_for_sort_data variable, which controlled whether MySQL used row‑id sorting or full‑field sorting, was deprecated after 8.0.20. Consequently, the optimizer may skip index usage and perform full‑field sorting, leading to longer execution times.

To verify the hypothesis, tests were run on MySQL 5.7.44 and MySQL 8.0.30 using a table with roughly 4 million rows. The table was created with the following statement: 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 queries were executed: one selecting three columns and another selecting all columns. -- 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: In MySQL 5.7 both queries completed in about 1 second. In MySQL 8.0 the three‑column query remained ~1 second, but the all‑columns query took ~4 seconds, and the execution time increased as more columns were selected.

Further testing showed that setting max_length_for_sort_data to a value larger than all column sizes in MySQL 5.7 also caused the slowdown, confirming the role of this parameter.

Conclusion: Starting with MySQL 8.0.20, sorting without an index no longer depends on max_length_for_sort_data ; the optimizer decides based on the total size of selected and ordered columns, which can make wide SELECT statements slower than in 5.7. The recommended mitigation is to add an index on the ORDER BY column.