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.

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?

  • 1
    WHERE .. IN is slow in most cases. Use multi-table delete delete tbl.* from tbl, DELETE_ID where tbl.id = DELETE_ID.id.
    – Akina
    Oct 16, 2018 at 10:51
  • Hi @Akina can you please help me to understand how this In clause made the process slow?
    – TheDataGuy
    Oct 16, 2018 at 11:22
  • In general most RDBMS have terrible performance for IN clauses. The optimizer may rewrite it to (or ID = 1 or ID =2 or ID =3 or ID =....). Here's one link discussing it: spin.atomicobject.com/2011/03/25/mysql-in-query-performance
    – Kevin Bott
    Oct 16, 2018 at 16:54
  • @Akina - Make your Comment into an Answer; it is likely to be the best Answer.
    – Rick James
    Oct 19, 2018 at 4:45
  • @RickJames Done...
    – Akina
    Oct 19, 2018 at 4:55

2 Answers 2


WHERE .. IN is slow in most cases.

Use multi-table delete:

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

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

  • 1
    Hi, Welcome to the site. Can you explain to the OP why his construction is slow and why this one should be faster? Thanks
    – Tom V
    Oct 17, 2018 at 8:09
  • stackoverflow.com/questions/2065329/…
    – Hari
    Oct 18, 2018 at 6:08
  • I don't think that reference applies in that case. There it is talking about having to scan to see if it exists and stopping short ("semi-join"). Here it is not the speed of the EXISTS, but the fact that it involves a 'correlated' subquery and will perform the EXISTS for each and every row in tbl.
    – Rick James
    Oct 19, 2018 at 4:48

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.