List:General Discussion« Previous MessageNext Message »
From:Stuart Brooks Date:May 23 2006 2:24pm
Subject:Re: Index merge optimization (with OR) and table joins
View as plain text  
Thanks for the reply, it's much appreciated. I'll have another look at
the tables, although my criteria were such that the query should have
only returned a tiny fraction (<0.1%) of the rows (which is why I was
confused). If I get any closer to a solution I'll try and post with a
bit more info.

And next time I'll try and remember to send the SHOW CREATE TABLE
statements as well:)

Kind regards
 Stuart


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