Why is my MySQL query with ORDER BY so slow?

Slowdown of Queries with ORDER BY Clause

MySQL database users have encountered a puzzling performance issue related to the use of the ORDER BY clause. When added to a complex query with multiple joins and subqueries, the query's execution time increases significantly from milliseconds to seconds.

One specific instance involves a query that retrieves data from three tables with approximately 40,000 rows in the largest table. Without the ORDER BY clause, the query performs optimally. However, when any column is used as a sorting criteria, the query slows down by several orders of magnitude to around 10 seconds.

The root cause of this issue lies in the database's optimization mechanism. By default, MySQL attempts to optimize queries by creating temporary tables or using subqueries. When an ORDER BY clause is added, the optimizer miscalculates the size of the temporary table needed and allocates an insufficient amount of memory. Consequently, the query falls back to a less efficient sorting algorithm, which results in the observed slowdown.

One possible workaround is to rewrite the query using a correlated subquery or a CTE (Common Table Expression) to explicitly specify the sorting step. This allows the optimizer to correctly estimate the temporary table size, preventing the slowdown.

The above is the detailed content of Why is my MySQL query with ORDER BY so slow?. For more information, please follow other related articles on the PHP Chinese website!