List:General Discussion« Previous MessageNext Message »
From:sheeri kritzer Date:May 4 2006 7:52pm
Subject:Re: Index merge optimization (with OR) and table joins
View as plain text  
On 5/4/06, sheeri kritzer <awfief@stripped> wrote:
> (again, apologies for the lateness...)
>
> MySQL has a cost-based optimizer.  If it's deciding that a full-table
> scan is appropriate, there's a reason.  If more than 30% (approx) of
> the table would be returned in a range query, the optimizer reasons
> that it's LESS expensive to just do a full table scan.  Otherwise, if
> say you're returning 50% of the rows, you have to find the pointer to
> the row using the index, then go to the row.  Doing a table scan
> eliminates needing that extra step of the index.
>
> Next time full SHOW CREATE TABLE statements would be useful.
>
> -Sheeri
>
> On 4/11/06, Stuart Brooks <stuartb@stripped> wrote:
> > 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
> >
> >
> >
> > --
> > MySQL General Mailing List
> > For list archives: http://lists.mysql.com/mysql
> > To unsubscribe:    http://lists.mysql.com/mysql?unsub=1
> >
> >
>
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