ORDER BY query very slow - how to optimise?
Posted: Wed Jun 05, 2013 12:53 pm
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:
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!
Code: Select all
SELECT * FROM f_posts WHERE author='$name' ORDER BY postid DESC LIMIT $x, $yThe 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!