[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)