Page 1 of 1
help optimising a query
Posted: Wed Oct 03, 2007 6:29 pm
by s.dot
Code: Select all
SELECT `id`,`author`,`topicname`,`forumid`,`replies`,`lasttime`,`lastauthor`,`replies` FROM `forumtopicmain` WHERE `forumid` != 27 ORDER BY `lastreply` DESC LIMIT 20
This query shouldn't take .97 seconds, seeing as how there's only ~40,000 rows in the database. I can pull 20 records from a table with over 1 million records in .07 seconds.. so something is wrong here.
I have indexes on id (auto increment primary key), forumid, and author.
EDIT| If I take out the WHERE `forumid` != 27.. I get "Query took 0.0601".. so that's obviously the slow point of the query. But I have an index on `forumid`.

I thought indexes were the magical cure-all to slow queries~!
Posted: Wed Oct 03, 2007 6:55 pm
by Begby
do an EXPLAIN on that query and post the results back here
Posted: Wed Oct 03, 2007 7:07 pm
by s.dot
Forgive me, I'm not used to using explain, and I'm doing it in phpmyadmin.
Code: Select all
EXPLAIN SELECT `id`,`author`,`topicname`,`forumid`,`replies`,`lasttime`,`lastauthor`,`replies` FROM `forumtopicmain` ORDER BY `lastreply` DESC LIMIT 20;
With this query I get:
Code: Select all
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE forumtopicmain ALL NULL NULL NULL NULL 39933 Using filesort
When I include the where clause..
Code: Select all
EXPLAIN SELECT `id` , `author` , `topicname` , `forumid` , `replies` , `lasttime` , `lastauthor` , `replies` FROM `forumtopicmain` WHERE `id` !=27 ORDER BY `lastreply` DESC LIMIT 20
I get:
Code: Select all
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE forumtopicmain ALL PRIMARY NULL NULL NULL 39933 Using where; Using filesort
Posted: Wed Oct 03, 2007 7:36 pm
by Begby
Where it says possible_keys those are the indexes that are available for the query, then next column which is key is the actual key used. Each part of a query can only use one key.
The select_type is important as well, this is where it says how its doing the search. Simple = bad.
In this case it is probably copying to a temp table ordering by the order by field, then it is dropping the records != 27. This will result in every single record being read looking for id 27.
Try adding a composite key for id, lastreply, or just an index for lastreply as that might prevent a temp table from being made. Or you can try using force index but that may not help much.
Edit: Oh yeah, that rows column on explain are how many rows were read to solve the query. I think the filesort indicates a temp table being made.
Posted: Wed Oct 03, 2007 7:58 pm
by s.dot
Wow, nice. Adding an index on `lastreply` dropped it down to "Query took 0.0012 sec". That's simply amazing.
Thanks begby!
EDIT| And, you were right. It's not using filesort now.
Code: Select all
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE forumtopicmain index PRIMARY lastreply 4 NULL 39933 Using where
Posted: Wed Oct 03, 2007 10:03 pm
by VladSun
Just a little notice: you select `replies` twice

Posted: Wed Oct 03, 2007 11:58 pm
by s.dot
good heads up on that
wondering how that effects query speed... will do it just for my amusement in the morning