3

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?

6
  • 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 '18 at 10:51
  • Hi @Akina can you please help me to understand how this In clause made the process slow?
    – TheDataGuy
    Oct 16 '18 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 '18 at 16:54
  • @Akina - Make your Comment into an Answer; it is likely to be the best Answer.
    – Rick James
    Oct 19 '18 at 4:45
  • @RickJames Done...
    – Akina
    Oct 19 '18 at 4:55
3

WHERE .. IN is slow in most cases.

Use multi-table delete:

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

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

3
  • 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 '18 at 8:09
  • 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 '18 at 4:48

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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