Jump to content

Bizarre Join Optimization Issues


GameMusic

Recommended Posts

SELECT t1.*
FROM boardposts t1
INNER JOIN boardposts t2 ON ( t2.topic =766
AND t2.replyTo = 47069 )
WHERE t1.topic = 766
AND (t1.replyTo = t2.id OR t1.id = t2.id)
ORDER BY t1.addDate
LIMIT 0 , 20

I'm selecting a post and each reply to that post, and replies to those replies.  This query runs slowly (3 to 4 seconds) on a huge topic (12,000 replies) when selecting the first post.

But the weird thing is, if I try to find the COUNT(*) or COUNT(t1.*) (for the purpose of pagination) the query runs a LONG time and the entire boardposts table is locked until I stop the process.  This doesn't make sense to me because I'd assume an internal counter would be faster than returning the data.
Link to comment
Share on other sites

EXPLAIN SELECT COUNT( * )
FROM boardposts t1
INNER JOIN boardposts t2 ON ( t2.topic =766
AND t2.replyTo =47069 )
WHERE t1.topic =766
AND (
t1.replyTo = t2.id
OR t1.id = t2.id
)
ORDER BY t1.addDate
LIMIT 0 , 20

[code]id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t1 ref id_2,byTopic,byReplyTo byTopic 4 const 12943 Using where; Using filesort
1 SIMPLE t2 ref id_2,byTopic,byReplyTo byReplyTo 4 const 7185 Using where

1  SIMPLE  t1  ref  id_2,byTopic,byReplyTo  byTopic  4  const  12943  Using where
1 SIMPLE t2 ref id_2,byTopic,byReplyTo byReplyTo 4 const 7185 Using where[/code]

The first 2 are the data query, and the second 2 are the count query

topic = id of the topic
replyTo = id of the post that post is a reply to
Link to comment
Share on other sites

Ok, a few things -- first, if you can create an (topic,replyTo), you'll be able to take advantage of this index for the JOIN condition, which is important.  Second, it's going to hard to get MySQL to use a proper index with AND/OR clauses for t1.  Third, I'm not really sure why the COUNT() would be slower, unless you have no limit clause.
Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.