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