Page 1 of 1

ORDER BY query very slow - how to optimise?

Posted: Wed Jun 05, 2013 12:53 pm
by anivad
On my site's forum there's a posting history page to see all the forum posts that a user has made. According to my webhost, this is using up a lot of server resources, but I don't know how to optimise it:

Code: Select all

SELECT * FROM f_posts WHERE author='$name' ORDER BY postid DESC LIMIT $x, $y
The query takes 0.1253 sec (and is even longer if I remove the LIMIT); even if I take out the ORDER BY, it's still a whomping 0.0144 seconds for that simple SELECT...WHERE query. :/

The author and postid columns have both been indexed. postid is the primary key.
The table currently has about 12,400 rows.
$x and $y change depending on the page number (each page shows 15 results).

If I run an EXPLAIN on the first full query, it goes:

id: 1
select_type: SIMPLE
table: f_posts
type: index
possible_keys: author
key: PRIMARY
key_len: 4
ref: NULL
rows : 96
Extra: Using where

What could be causing the problem, and how do I fix this? Thanks!

Re: ORDER BY query very slow - how to optimise?

Posted: Wed Jun 05, 2013 1:23 pm
by requinix
Have you done an ANALYZE recently?

Code: Select all

ANALYZE TABLE f_posts
If that doesn't help then what's the output of the EXPLAIN when you don't include the ORDER BY?

Re: ORDER BY query very slow - how to optimise?

Posted: Wed Jun 05, 2013 1:48 pm
by anivad
I realised that other normally-quick queries are also running much slower than normal, so timings are inflated. But apparently that's due to some of the SELECT * FROM f_post queries stressing out the database in the first place.

I did an ANALYZE, and it claims to be OK.
Msg_type: status
Msg_text: OK

Without ORDER BY (using same value for 'author':
id: 1
select_type: SIMPLE
table: f_posts
type: ref
possible_keys: author
key: author
key_len: 27
ref: const
rows : 1934
Extra: Using where

Re: ORDER BY query very slow - how to optimise?

Posted: Wed Jun 05, 2013 2:58 pm
by requinix
How's the original query now after the ANALYZE?

Meanwhile it doesn't mind using the author index just by itself, though a const query should be really fast.
Try suggesting the index

Code: Select all

SELECT * FROM f_posts USE INDEX (author) WHERE author='$name' ORDER BY postid DESC LIMIT $x, $y
Might have to add postid in there too if MySQL doesn't try to use it.

Re: ORDER BY query very slow - how to optimise?

Posted: Wed Jun 05, 2013 3:11 pm
by anivad
It's still really slow -> 0.0293 seconds. It's about five times faster, though!

Ok, I just did OPTIMIZE TABLE, and now it's down to 0.0009 seconds. whoa. I wasn't previously aware that there was such a thing.
I'm not sure if that solves the problem, though, or if it's just a temporary fix.

Re: ORDER BY query very slow - how to optimise?

Posted: Wed Jun 05, 2013 3:16 pm
by requinix
Yeah, that's the statement I was thinking of. The one that makes MySQL reevaluate the table and its indexes.