-1

I would like to know why my DELETE on a MySQL 5.5 myisam table is sooo slow (i had to cancel it)

This SQL takes under a second:

SELECT id FROM backup
WHERE id > 0 
      AND userId NOT IN 
      (SELECT id FROM user WHERE state > 0) 
ORDER BY id ASC LIMIT 100;

This identical DELETE did not finish afte 2 Minutes (i canceled it)

DELETE FROM backup
WHERE id > 0 
      AND userId NOT IN 
      (SELECT id FROM user WHERE state > 0) 
ORDER BY id ASC LIMIT 100;

But the following DELETE - querying the SAME ROWS (just now selected by their primary key directly) - took under a second to complete!

DELETE FROM backup WHERE id IN (12, 33, 34, [... 100 ids...])

EDIT I also tried this modification using a sub-subquery - i canceld it after a minute...

DELETE FROM backup WHERE id IN (
    SELECT ID FROM (
        SELECT id FROM backup WHERE id > 0 
        AND userId NOT IN (SELECT id FROM user WHERE state > 0) 
        ORDER BY id ASC LIMIT 100
    ) a 
);

Using INNER JOIN does not work with DELETE as i use ORDER BY.

Any idea?

elegon
  • 133
  • 9
  • 2
    Possible duplicate of [Performance of sub-query in IN clause with large tables in delete query](https://stackoverflow.com/questions/39634970/performance-of-sub-query-in-in-clause-with-large-tables-in-delete-query) – lxg Feb 22 '18 at 16:01
  • … or [Very slow delete on mysql base with subquery](https://stackoverflow.com/questions/7361174/very-slow-delete-on-mysql-base-with-subquery) – lxg Feb 22 '18 at 16:02
  • thank you, but i tried that - it did not help. see my edit... – elegon Feb 22 '18 at 19:17

1 Answers1

0

ok, the problem is in fact the subquery - respectively the ORDER BY and LIMIT statements.

when i omit order by and limit and use the following query, it is as fast as expected (under a second):

DELETE backup FROM backup 
INNER JOIN user ON user.id = backup.userId
WHERE backup.id > 0 AND backup.id < 276 AND (state = 0 OR state IS NULL);
elegon
  • 133
  • 9