Should I be able to DELETE FROM ... WHERE .. IN ( ... ) with multiple a column key?

This is in 5.0.68 and 5.1.34.

I'm trying to cleanup some old data in a table which looks like the following:

CREATE TABLE `transaction_history` (
`customer_id` int(10) unsigned NOT NULL default '0',
`transaction_id` int(10) unsigned NOT NULL default '0',
`first_timestamp` datetime NOT NULL default '0000-00-00 00:00:00',
`last_timestamp` datetime NOT NULL default '0000-00-00 00:00:00',
PRIMARY KEY (`transaction_id`,`first_timestamp`,`customer_id`),
KEY `customer_id` (`customer_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1

mysql> SELECT * FROM transaction_history LIMIT 10;
+-------------+----------------+---------------------+------ ---------------+
| customer_id | transaction_id | first_timestamp | last_timestamp |
+-------------+----------------+---------------------+------ ---------------+
| 100000 | 31536827 | 2009-01-22 13:25:26 | 2009-01-22 13:40:21 |
| 306636 | 31536827 | 2009-01-22 13:43:56 | 2009-01-22 13:44:02 |
| 100000 | 31536827 | 2009-01-22 13:50:24 | 2009-01-22 13:50:46 |
| 306636 | 31536827 | 2009-01-22 13:50:53 | 2009-01-22 13:59:13 |
| 304142 | 31536827 | 2009-01-22 14:53:00 | 2009-01-22 14:53:00 |
| 306636 | 31536827 | 2009-01-22 15:03:59 | 2009-01-22 15:03:59 |
| 100000 | 31536827 | 2009-01-22 15:06:15 | 2009-01-22 15:09:01 |
| 306636 | 31536827 | 2009-01-22 15:09:41 | 2009-01-22 15:10:32 |
| 100000 | 31536827 | 2009-01-22 15:10:42 | 2009-01-22 15:19:48 |
| 306636 | 31536827 | 2009-01-22 15:30:41 | 2009-01-22 16:01:28 |
+-------------+----------------+---------------------+------ ---------------+
10 rows IN set (0.02 sec)

I need to identify the rows to be deleted and was planning on doing something like:

mysql> EXPLAIN SELECT * FROM transaction_history WHERE (`transaction_id`,`first_timestamp`,`customer_id`) IN ( ( 31536827, '2009-01-22 13:25:26', 100000 ), ( 31536827, '2009-01-22 13:43:56', 306636 ) );
+----+-------------+-----------------------+------+--------- ------+------+---------+------+----------+-------------+
| id | SELECT_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-----------------------+------+--------- ------+------+---------+------+----------+-------------+
| 1 | SIMPLE | transaction_history | ALL | NULL | NULL | NULL | NULL | 73181118 | Using WHERE |
+----+-------------+-----------------------+------+--------- ------+------+---------+------+----------+-------------+
1 row IN set (0.00 sec)

As you can see MySQL is ignoring or not recognising the primary key
"definition" in the where clause and thus planning on doing a table scan.

The simple approach is recognised correctly:

mysql> EXPLAIN SELECT * FROM transaction_history WHERE (`transaction_id`,`first_timestamp`,`customer_id`) = ( 31536827, '2009-01-22 13:25:26', 100000 ) OR (`transaction_id`,`first_timestamp`,`customer_id`) = ( 31536827, '2009-01-22 13:43:56', 306636 );
+----+-------------+-----------------------+-------+-------- --------------+---------+---------+------+------+----------- --+
| id | SELECT_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-----------------------+-------+-------- --------------+---------+---------+------+------+----------- --+
| 1 | SIMPLE | transaction_history | range | PRIMARY,customer_id | PRIMARY | 16 | NULL | 2 | Using WHERE |
+----+-------------+-----------------------+-------+-------- --------------+---------+---------+------+------+----------- --+
1 row IN set (0.02 sec)

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?

Simon

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org

Re: Should I be able to DELETE FROM ... WHERE .. IN ( ... ) with

On Wed, May 27, 2009 at 6:43 AM, Simon J Mudd wrote:
> So is the format of the DELETE FROM .. WHERE ... IN ( ... ) =A0clause 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_i=
n

- Perrin

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=3Dgcdmg-mysql-2@m.gmane.o rg

Re: Should I be able to DELETE FROM ... WHERE .. IN ( ... ) with multiple a column key?

perrin@elem.com (Perrin Harkins) writes:

> On Wed, May 27, 2009 at 6:43 AM, Simon J Mudd wrote:
> > So is the format of the DELETE FROM .. WHERE ... IN ( ... )  claus=
e I
> > propose valid and SHOULD the optimiser recognise this and be expected
> > to just find the 2 rows by searching on the primary key?
>=20
> 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=3Dgcdmg-mysql-2@m.gmane.o rg

Re: Should I be able to DELETE FROM ... WHERE .. IN ( ... ) with

Simon,

On Wed, May 27, 2009 at 11:23 AM, Simon J Mudd wrote:
> perrin@elem.com (Perrin Harkins) writes:
>
>> On Wed, May 27, 2009 at 6:43 AM, Simon J Mudd wrote:
>> > So is the format of the DELETE FROM .. WHERE ... IN ( ... ) =A0clause =
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#functio=
n_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 somethin=
g
> like this. I'll have to check my Joe Celko books to see if he mentions th=
s.

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-u tilize-a-co=
mpound-index

Regards
Baron

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=3Dgcdmg-mysql-2@m.gmane.o rg

Re: Should I be able to DELETE FROM ... WHERE .. IN ( ... ) with

--0016363b85b0a5b884046b313ae4
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit

As I know, IN sometimes invoke unmormal index.

On Thu, May 28, 2009 at 1:15 AM, Baron Schwartz wrote:

> Simon,
>
> On Wed, May 27, 2009 at 11:23 AM, Simon J Mudd wrote:
> > perrin@elem.com (Perrin Harkins) writes:
> >
> >> On Wed, May 27, 2009 at 6:43 AM, Simon J Mudd 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-u tilize-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=yueliangdao0608@gmail.com
>
>


--
David Yeung,
MySQL Senior Support Engineer,
Sun Gold Partner.
My Blog:http://yueliangdao0608.cublog.cn
Comanpy: http://www.actionsky.com

--0016363b85b0a5b884046b313ae4--