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