使用 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
相关文章