MySql Delete Taking A Long Time

Question

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?

Buy a faster hard disk? Use an SSD? Are you sure your temporary table has a primary key? — tadman, Mar 08 '17 at 21:54
hard to tell w/o access to your database. Have you tried to look at query plan? https://dev.mysql.com/doc/refman/5.7/en/explain-extended.html try EXPLAIN and see if you can find anything. Also you may want to try using JOIN instead of IN clause. — Jenya G, Mar 08 '17 at 21:55

score 10 · Accepted Answer · answered Mar 08 '17 at 21:55

10

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

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

answered Mar 08 '17 at 21:55

PeterHe

2,766
1
8
7

Welp, that did it. Took .1 second. Thanks. – maembe Mar 08 '17 at 22:07
Also, a `select` statement using `where in(select...from table)` may run much faster than the corresponding `delete`. – CLAbeel Oct 04 '18 at 14:15

MySql Delete Taking A Long Time

1 Answers1