ThorstenA
06-25-2011, 01:30 PM
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 1309012555 at 1309012555 ---------------
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)
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 1309012555 at 1309012555 ---------------
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)