ORDER BY query very slow - how to optimise?

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
anivad
Forum Commoner
Posts: 80
Joined: Thu Apr 09, 2009 11:16 pm

ORDER BY query very slow - how to optimise?

Post 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!
User avatar
requinix
Spammer :|
Posts: 6617
Joined: Wed Oct 15, 2008 2:35 am
Location: WA, USA

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

Post 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?
anivad
Forum Commoner
Posts: 80
Joined: Thu Apr 09, 2009 11:16 pm

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

Post 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
User avatar
requinix
Spammer :|
Posts: 6617
Joined: Wed Oct 15, 2008 2:35 am
Location: WA, USA

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

Post 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.
anivad
Forum Commoner
Posts: 80
Joined: Thu Apr 09, 2009 11:16 pm

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

Post 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.
User avatar
requinix
Spammer :|
Posts: 6617
Joined: Wed Oct 15, 2008 2:35 am
Location: WA, USA

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

Post by requinix »

Yeah, that's the statement I was thinking of. The one that makes MySQL reevaluate the table and its indexes.
Post Reply