MySQL Forums
Forum List  »  Optimizer & Parser

Index merge optimization (with OR) and table joins
Posted by: Stuart Brooks
Date: April 07, 2006 06:56AM

Hi,

I have been having a hassle getting the index_merge to work as expected when I am joining 2 tables on MySQL 5.0.19. The following example should make it clear:

Table A
key1 (primary key)
key2
some_data

Table B
key1 (indexed)
key2 (indexed)
more_data

SELECT a.key1,a.key2,b.more_data
FROM A as a JOIN B as b ON ((a.key1=b.key1) OR (a.key2=b.key2))
WHERE (a.key1=10);

This works as expected. An EXPLAIN yields :
a; const; PRIMARY
b; index_merge; key1,key2

However if I make the WHERE clause a range (or remove it altogether):

SELECT a.key1,a.key2,b.more_data
FROM A as a JOIN B as b ON ((a.key1=b.key1) OR (a.key2=b.key2))
WHERE (a.key1<10) #optional

I end up with...
a; range; PRIMARY
b; ALL; none

which is a brute force attack on table b. Am I missing something here, I would have expected it to use an index merge on table b in both cases. Is there a way to force it to use the index merge?

Regards
Stuart

Options: ReplyQuote


Subject
Views
Written By
Posted
Index merge optimization (with OR) and table joins
2435
April 07, 2006 06:56AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.