Search code examples
mysqlinnodb

Very slow MySQL delete

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?

Solution

  • 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