slow query for thread.forumid. help me optimize

[ThorstenA]

This query is slow (0.15 seconds) despite of having indexes everywhere.

SELECT SQL_NO_CACHE post.postid  
FROM post 
INNER JOIN thread ON(thread.threadid=post.threadid)
INNER JOIN attachment ON (post.postid = attachment.contentid)
WHERE thread.forumid = 1

These both queries are fast when removing either forumid=1 or the attachment table. Why is that? How can I rewrite my #1 query to make it faster?

SELECT SQL_NO_CACHE post.postid  
FROM post 
INNER JOIN thread ON(thread.threadid=post.threadid)
INNER JOIN attachment ON (post.postid = attachment.contentid)

SELECT SQL_NO_CACHE post.postid  
FROM post 
INNER JOIN thread ON(thread.threadid=post.threadid)
WHERE thread.forumid = 1

--------------- Added [DATE]1309012555[/DATE] at [TIME]1309012555[/TIME] ---------------

Found the solution. This is extremely fast. The forumid restriction in the inner join thread seems to be very good.

SELECT SQL_NO_CACHE post.postid  
FROM post 
INNER JOIN (SELECT threadid FROM thread WHERE forumid=1) AS thread
ON(thread.threadid=post.threadid)
INNER JOIN attachment ON (post.postid = attachment.contentid)

[Pandemikk]

Glad you found the solution. Before you were selecting all threads and then applying the forumid restriction!

Happy coding.

p.s. you don't need those parenthesis for the on clause, in case you didn't know.