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
Can anyone help me to understand why this is happening?