使用ORDER BY时查询速度慢 [英] Slow query when using ORDER BY

问题描述

这是查询(最大的表大约有40,000行)

Here's the query (the largest table has about 40,000 rows)

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

如果运行此命令，它将执行得非常快(大约.05秒).它返回13行.

If I run this, it executes very quickly (.05 seconds roughly). It returns 13 rows.

当我在查询末尾添加ORDER BY子句(按任何列排序)时，查询大约需要10秒钟.

When I add an ORDER BY clause at the end of the query (ordering by any column) the query takes about 10 seconds.

我现在正在生产中使用此数据库，并且一切正常.我所有其他查询都很快.

I'm using this database in production now, and everything is working fine. All my other queries are speedy.

有什么想法吗?我在MySQL的查询浏览器中并从命令行运行了查询.两个地方都用ORDER BY慢了点.

Any ideas of what it could be? I ran the query in MySQL's Query Browser, and from the command line. Both places it was dead slow with the ORDER BY.

Tolgahan ALBAYRAK解决方案有效，但是谁能解释为什么它有效?

Tolgahan ALBAYRAK solution works, but can anyone explain why it works?

推荐答案

也许有帮助:

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

这篇关于使用ORDER BY时查询速度慢的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！