使用 ORDER BY 时查询速度慢

2021-11-20 00:00:00 mysql sql-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

如果我运行它，它执行得非常快(大约 0.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

相关文章