Bizarre Join Optimization Issues

[GameMusic]

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.

[fenway]

Post the EXPLAIN output.

[GameMusic]

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

[fenway]

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.