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~!
Set Search Time - A google chrome extension. When you search only results from the past year (or set time period) are displayed. Helps tremendously when using new technologies to avoid outdated results.
EXPLAIN SELECT `id`,`author`,`topicname`,`forumid`,`replies`,`lasttime`,`lastauthor`,`replies` FROM `forumtopicmain` ORDER BY `lastreply` DESC LIMIT 20;
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
Set Search Time - A google chrome extension. When you search only results from the past year (or set time period) are displayed. Helps tremendously when using new technologies to avoid outdated results.
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.
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE forumtopicmain index PRIMARY lastreply 4 NULL 39933 Using where
Set Search Time - A google chrome extension. When you search only results from the past year (or set time period) are displayed. Helps tremendously when using new technologies to avoid outdated results.
good heads up on that
wondering how that effects query speed... will do it just for my amusement in the morning
Set Search Time - A google chrome extension. When you search only results from the past year (or set time period) are displayed. Helps tremendously when using new technologies to avoid outdated results.