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