Jump to content


Photo

Bizarre Join Optimization Issues


  • Please log in to reply
3 replies to this topic

#1 GameMusic

GameMusic
  • New Members
  • Pip
  • Newbie
  • 4 posts

Posted 03 September 2006 - 02:54 AM

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.

#2 fenway

fenway
  • Staff Alumni
  • MySQL Si-Fu / PHP Resident Alien
  • 16,199 posts
  • LocationToronto, ON

Posted 03 September 2006 - 03:39 PM

Post the EXPLAIN output.
Seriously... if people don't start reading this before posting, I'm going to consider not answering at all.

#3 GameMusic

GameMusic
  • New Members
  • Pip
  • Newbie
  • 4 posts

Posted 04 September 2006 - 04:32 AM

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

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

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

#4 fenway

fenway
  • Staff Alumni
  • MySQL Si-Fu / PHP Resident Alien
  • 16,199 posts
  • LocationToronto, ON

Posted 04 September 2006 - 02:56 PM

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.
Seriously... if people don't start reading this before posting, I'm going to consider not answering at all.




0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users