Search code examples
mysqlsqlquery-optimizationinnodb

MySql Delete Taking A Long Time

I have a query that is deleting a set of 300 records based on the primary key.

The table is about 250,000 records and four columns (int PK, varchar, date, tinyint), so it should be a pretty manageable size, however, it takes around 2 minutes to delete with no other queries running, which seems pretty excessive based on how few rows it's deleting and the size of the table.

The sql looks like this:

-- this takes less than 1 second
CREATE TEMPORARY TABLE IF NOT EXISTS temp AS (
    SELECT id
    FROM some_other_table
    WHERE ...
    LIMIT 300
);  

-- id is the primary key
-- this takes upwards of 2 minutes
DELETE FROM queue WHERE id in(
    select id from temp
);

The table only has one additional index and has no foreign key relationships. Any ideas as to what I could do to speed this up?

Solution

  • Mysql can't optimize IN very well, use JOIN instead:

    DELETE q.* FROM queue q INNER JOIN temp t ON q.id=t.id