Re: Should I be able to DELETE FROM ... WHERE .. IN ( ... ) with multiple a column key?
As I know, IN sometimes invoke unmormal index. On Thu, May 28, 2009 at 1:15 AM, Baron Schwartz ba...@xaprb.com wrote: Simon, On Wed, May 27, 2009 at 11:23 AM, Simon J Mudd sjm...@pobox.com wrote: per...@elem.com (Perrin Harkins) writes: On Wed, May 27, 2009 at 6:43 AM, Simon J Mudd sjm...@pobox.com wrote: So is the format of the DELETE FROM .. WHERE ... IN ( ... ) clause I propose valid and SHOULD the optimiser recognise this and be expected to just find the 2 rows by searching on the primary key? Not according to the docs: http://dev.mysql.com/doc/refman/5.1/en/comparison-operators.html#function_in I'm not sure that the reference makes anything clear. The statements are wrote ARE valid SQL and even though containing mulitiple column values ARE constants. Problem is I'm finding it hard to find a definitive reference to something like this. I'll have to check my Joe Celko books to see if he mentions ths. Nothing's wrong with the SQL -- it's just that MySQL doesn't optimize this type of query well. See http://code.openark.org/blog/mysql/mysql-not-being-able-to-utilize-a-compound-index Regards Baron -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=yueliangdao0...@gmail.com -- David Yeung, MySQL Senior Support Engineer, Sun Gold Partner. My Blog:http://yueliangdao0608.cublog.cn Comanpy: http://www.actionsky.com
Re: Should I be able to DELETE FROM ... WHERE .. IN ( ... ) with multiple a column key?
On Wed, May 27, 2009 at 6:43 AM, Simon J Mudd sjm...@pobox.com wrote: So is the format of the DELETE FROM .. WHERE ... IN ( ... ) clause I propose valid and SHOULD the optimiser recognise this and be expected to just find the 2 rows by searching on the primary key? Not according to the docs: http://dev.mysql.com/doc/refman/5.1/en/comparison-operators.html#function_in - Perrin -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Should I be able to DELETE FROM ... WHERE .. IN ( ... ) with multiple a column key?
per...@elem.com (Perrin Harkins) writes: On Wed, May 27, 2009 at 6:43 AM, Simon J Mudd sjm...@pobox.com wrote: So is the format of the DELETE FROM .. WHERE ... IN ( ... ) clause I propose valid and SHOULD the optimiser recognise this and be expected to just find the 2 rows by searching on the primary key? Not according to the docs: http://dev.mysql.com/doc/refman/5.1/en/comparison-operators.html#function_in I'm not sure that the reference makes anything clear. The statements are wrote ARE valid SQL and even though containing mulitiple column values ARE constants. Problem is I'm finding it hard to find a definitive reference to something like this. I'll have to check my Joe Celko books to see if he mentions ths. Simon -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Should I be able to DELETE FROM ... WHERE .. IN ( ... ) with multiple a column key?
Simon, On Wed, May 27, 2009 at 11:23 AM, Simon J Mudd sjm...@pobox.com wrote: per...@elem.com (Perrin Harkins) writes: On Wed, May 27, 2009 at 6:43 AM, Simon J Mudd sjm...@pobox.com wrote: So is the format of the DELETE FROM .. WHERE ... IN ( ... ) clause I propose valid and SHOULD the optimiser recognise this and be expected to just find the 2 rows by searching on the primary key? Not according to the docs: http://dev.mysql.com/doc/refman/5.1/en/comparison-operators.html#function_in I'm not sure that the reference makes anything clear. The statements are wrote ARE valid SQL and even though containing mulitiple column values ARE constants. Problem is I'm finding it hard to find a definitive reference to something like this. I'll have to check my Joe Celko books to see if he mentions ths. Nothing's wrong with the SQL -- it's just that MySQL doesn't optimize this type of query well. See http://code.openark.org/blog/mysql/mysql-not-being-able-to-utilize-a-compound-index Regards Baron -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
