Re: Index merge optimization (with OR) and table joins
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]
Re: Index merge optimization (with OR) and table joins
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]
Re: Index merge optimization (with OR) and table joins
(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]
Index merge optimization (with OR) and table joins
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]
