MySQL - Delete with IN Clause taking more time

2018-10-19 05:30:02

In MySQL 5.6 I have around 6 crores of 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

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

  • WHERE .. IN is slow in most cases.

    Use multi-table delete:

    DELETE tbl.*

    FROM tbl, DELETE_ID

    WHERE tbl.id = DELETE_ID.id

    2018-10-19 06:00:00
  • Delete from tbl where EXISTS (SELECT id FROM DELETE_ID WHERE tbl.id = DELETE_ID.id);

    2018-10-19 06:14:18