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  
(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

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