On 5/4/06, sheeri kritzer <[EMAIL PROTECTED]> 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 <[EMAIL PROTECTED]> 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/[EMAIL PROTECTED]
>
>
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
