Jump to content

Archived

This topic is now archived and is closed to further replies.

GameMusic

Bizarre Join Optimization Issues

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.

Share this post


Link to post
Share on other sites
Post the EXPLAIN output.

Share this post


Link to post
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

Share this post


Link to post
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.

Share this post


Link to post
Share on other sites

×

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.