Very slow MySQL delete

user_2263119 0
Viewed 200

So I'm trying to delete a number of rows from a reasonably large mysql(innodb)table.

The query i'm trying to use do this is as follows:

delete from item where id in (select id from items_to_be_deleted);

item is a 70'000'000 row table, and items_to_be_deleted is a 1'000'000 row table.

My query just never seems to finish, even if i add an incredibly small limit to it. (delete from item where id in (select id from items_to_be_deleted) LIMIT 10;

If i run select id from items_to_be_deleted it returns nearly instantaneously, it is just a table with a primary key (id) and another varchar field.

Whats wrong with my query that it is taking so long / never seems to finish?

innodb mysql
4 Answers

The IN clause work as a iteration of OR clause so you could avoid this using an inner join based on the same subquery used for the IN clause

delete item
from item
inner join  (
  select id 
  from items_to_be_deleted
) t on t.id  = item.id
user_35223120

How about JOIN?

DELETE i
FROM Item i
INNER JOIN items_to_be_deleted i2 ON i.ID = i2.ID
user_46093600

Other way can be to use EXISTS operator with dependent subquery: 

DELETE i FROM item i 
WHERE EXISTS (
   SELECT 1 FROM items_to_be_deleted WHERE id = i.id
)
user_21104960

When deleting a million rows, do it in chunks. Otherwise, the building of the undo log will be a killer. It must save all million previous rows, and later really toss them.

Since it is only a small percentage of the table I won't recommend copying the rows to keep into a new table.

Multiple ways to make a DELETE fast: http://localhost/rjweb/mysql/doc.php/deletebig

One thing to try to achieve, since there are 69 rows to keep for every 1 to toss: Try to have the 1M-row table drive the query. Most solutions involve scanning through all 70M rows, checking each one against the smaller table.

user_17668310
Related