Why is my SQL query slow with ORDER BY, and how can I fix it?

Slow Performance When Using ORDER BY in a Complex Query

In a complex query with multiple joins and subqueries, adding an ORDER BY clause can lead to significantly slower execution times. This issue has been reported in a query that contains four tables and returns just 13 rows. The query runs swiftly without the ORDER BY clause but becomes sluggish when it is added.

Solution

To resolve this performance issue, it is recommended to wrap the query in a subquery and apply the ORDER BY clause to the outer query. Here's the modified query:

SELECT *
FROM (
    SELECT
    Course.CourseID,
    Course.Description,
    UserCourse.UserID,
    UserCourse.TimeAllowed,
    UserCourse.CreatedOn,
    UserCourse.PassedOn,
    UserCourse.IssuedOn,
    C.LessonCnt
    FROM
    UserCourse
    INNER JOIN
    Course
    USING(CourseID)
    INNER JOIN
    (
        SELECT CourseID, COUNT(*) AS LessonCnt FROM CourseSection GROUP BY CourseID
    ) C
    USING(CourseID)
    WHERE 
    UserCourse.UserID = 8810
)
ORDER BY CourseID;

Explanation

The reason this solution works is that it allows the optimizer to evaluate the subquery independently of the ORDER BY operation. This results in a more efficient execution plan that is not impacted by the slow ORDER BY operation.

The above is the detailed content of Why is my SQL query slow with ORDER BY, and how can I fix it?. For more information, please follow other related articles on the PHP Chinese website!