List:General Discussion« Previous MessageNext Message »
From:Stuart Brooks Date:April 11 2006 10:25am
Subject:Index merge optimization (with OR) and table joins
View as plain text  
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


Thread
A complex JOIN scenarioKim Christensen11 Apr
  • Re: A complex JOIN scenarioMartijn Tonies11 Apr
    • Re: A complex JOIN scenarioKim Christensen11 Apr
  • Re: A complex JOIN scenarioMartijn Tonies11 Apr
    • Re: A complex JOIN scenarioKim Christensen11 Apr
  • Index merge optimization (with OR) and table joinsStuart Brooks11 Apr
    • Re: Index merge optimization (with OR) and table joinssheeri kritzer4 May
      • Re: Index merge optimization (with OR) and table joinssheeri kritzer4 May
  • Re: A complex JOIN scenarioMartijn Tonies11 Apr
    • Re: A complex JOIN scenarioKim Christensen11 Apr
  • Re: A complex JOIN scenarioSantino11 Apr
  • Re: Index merge optimization (with OR) and table joinsStuart Brooks23 May
Re: A complex JOIN scenarioKim Christensen11 Apr
  • Innodb import tuning on Sun T2000Russell Horn7 Jul