help optimising a query

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
User avatar
s.dot
Tranquility In Moderation
Posts: 5001
Joined: Sun Feb 06, 2005 7:18 pm
Location: Indiana

help optimising a query

Post 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~!
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.
Begby
Forum Regular
Posts: 575
Joined: Wed Dec 13, 2006 10:28 am

Post by Begby »

do an EXPLAIN on that query and post the results back here
User avatar
s.dot
Tranquility In Moderation
Posts: 5001
Joined: Sun Feb 06, 2005 7:18 pm
Location: Indiana

Post 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
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.
Begby
Forum Regular
Posts: 575
Joined: Wed Dec 13, 2006 10:28 am

Post 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.
User avatar
s.dot
Tranquility In Moderation
Posts: 5001
Joined: Sun Feb 06, 2005 7:18 pm
Location: Indiana

Post 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
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.
User avatar
VladSun
DevNet Master
Posts: 4313
Joined: Wed Jun 27, 2007 9:44 am
Location: Sofia, Bulgaria

Post by VladSun »

Just a little notice: you select `replies` twice :)
There are 10 types of people in this world, those who understand binary and those who don't
User avatar
s.dot
Tranquility In Moderation
Posts: 5001
Joined: Sun Feb 06, 2005 7:18 pm
Location: Indiana

Post by s.dot »

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.
Post Reply