Slow query when using ORDER BY

Question

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

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

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.

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.

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

Why it works? a sub-query takes the result in a result set, and ordering a result-set is much faster than having the default query execution have count in ordering along the way. — Stijn Sanders, May 19 '09 at 21:00

score 18 · Accepted Answer · answered May 19 '09 at 19:45

18

maybe this helps:

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

answered May 19 '09 at 19:45

Tolgahan Albayrak

3,118
1
25
28

Huh, that works (makes it execute fast). Do you know why though? I've never had to do that before. – May 19 '09 at 19:51
40k isnt that many records; typically have to deal with millions soyour milage may vary but this may help improve the performance further on this as the joins will be done on a reduced dataset. ... FROM (Select * from UserCourse Where UserID = 8810 ) UserCourse – u07ch May 19 '09 at 19:54
tvanfosson, I always thought the ORDER BY was processed after the results were returned. I guess that isn't necessarily the case. I'll have to investigate a bit more. Thanks. – May 19 '09 at 20:07

score 9 · Answer 2 · answered May 19 '09 at 19:45

9

Is the column you're ordering by indexed?

Indexing drastically speeds up ordering and filtering.

answered May 19 '09 at 19:45

ceejayoz

176,543
40
303
368

1

the op mentioned using any column in the order by reduces performance – northpole May 19 '09 at 19:46
4

Any column, including the indexed columns, make it run slow. – May 19 '09 at 19:49

Guy · Answer 3 · 2016-02-22T10:54:30.417

You are selecting from "UserCourse" which I assume is a joining table between courses and users (Many to Many). You should index the column that you need to order by, in the "UserCourse" table.

Suppose you want to "order by CourseID", then you need to index it on UserCourse table.

Ordering by any other column that is not present in the joining table (i.e. UserCourse) may require further denormalization and indexing on the joining table to be optimized for speed; In other words, you need to have a copy of that column in the joining table and index it.

P.S. The answer given by Tolgahan Albayrak, although correct for this question, would not produce the desired result, in cases where one is doing a "LIMIT x" query.

score 2 · Answer 4 · answered Aug 17 '12 at 12:03

Realise answer is too late, however I have just had a similar problem, adding order by increased the query time from seconds to 5 minutes and having tried most other suggestions for speeding it up, noticed that the /tmp files where getting to be 12G for this query. Changed the query such that a varchar(20000) field being returned was "trim("ed and performance dramatically improved (back to seconds). So I guess its worth checking whether you are returning large varchars as part of your query and if so, process them (maybe substring(x, 1, length(x))?? if you dont want to trim them. Query was returning 500k rows and the /tmp file indicated that each row was using about 20k of data.

score 2 · Answer 5 · answered May 19 '09 at 19:47

2

Have you updated the statistics on your database? I ran into something similar on mine where I had 2 identical queries where the only difference was a capital letter and one returned in 1/2 a second and the other took nearly 5 minutes. Updating the statistics resolved the issue

answered May 19 '09 at 19:47

Fry

4,106
9
38
51

Bizarre, I just ran into a similar issue and updating the statistics did fix this. We had to restore a database backup for this environment a couple days ago which probably caused this in the first place. – Wotuu Dec 31 '21 at 10:49

score 1 · Answer 6 · answered Sep 29 '11 at 14:29

Today I was running into a same kind of problem. As soon as I was sorting the resultset by a field from a joined table, the whole query was horribly slow and took more than a hundred seconds.

The server was running MySQL 5.0.51a and by chance I noticed that the same query was running as fast as it should have always done on a server with MySQL 5.1. When comparing the explains for that query I saw that obviously the usage and handling of indexes has changed a lot (at least from 5.0 -> 5.1).

So if you encounter such a problem, maybe your resolution is to simply upgrade your MySQL

score 0 · Answer 7 · edited May 23 '17 at 10:29

0

A similar question was asked before here.

It might help you as well. Basically it describes using composite indexes and how order by works.

edited May 23 '17 at 10:29

Community

1
1

answered May 19 '09 at 19:57

northpole

10,244
7
35
58

Slow query when using ORDER BY

7 Answers7

Linked