MySQL - Delete with IN Clause taking more time 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?
WHERE .. IN is slow in most cases. 

Use multi-table delete:

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