MySQL - Delete with IN Clause taking more time

Question

In MySQL 5.6 I have around 60 million rows. I wanted to remove rows based on Id. Here Id is the primary key.

One strange behavior I had today.

delete from tbl where id=1;

Its executed in milliseconds.

Then I did this.

CREATE TEMPORARY TABLE DELETE_ID (ID int);
Insert into DELETE_ID select id from (subery1(subquery2(subquery3)));

The DELETE_ID has around 150 ID.

Delete from tbl where id in (select id from DELETE_ID);

Its taking more than 30mins, but deleting a single row.

There were no locks or deadlock.

In innodb_trx the status was sometimes fetching rows sometimes unlocking rows.

Can anyone help me to understand why this is happening?

Answer 1

WHERE .. IN is slow in most cases.

Use multi-table delete:

DELETE tbl.* 
FROM tbl, DELETE_ID 
WHERE tbl.id = DELETE_ID.id

Answer 2

Delete from tbl where EXISTS (SELECT id FROM DELETE_ID WHERE tbl.id = DELETE_ID.id);