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