1

Using MariaDB 10.4.12, I am trying to execute a DELETE statement that predicates on a subquery:

DELETE FROM `mydb1`.`mytable1` 
WHERE `my_id` IN (
    SELECT `TABLE_ID` FROM `TABLES_CT` 
    WHERE `TABLE_NAME` = 'tableName'AND `TABLE_ACTION` IN ('D', 'U')
);

When I execute this, it times out after 30 seconds. If i look at information_schema.innodb_lockwaits and innodb_trx, the same DELETE statement is blocking itself on two different threads.

If i run it as a SELECT, it executes in 1 millisecond.

Any ideas?

Barmar
  • 741,623
  • 53
  • 500
  • 612
user8723089
  • 23
  • 3

1 Answers1

2

MySQL tends to optimize WHERE IN poorly. Use the equivalent JOIN intead.

DELETE t1 FROM mydb1.mytable1 AS t1
JOIN TABLES_CT AS t2 ON t1.my_id = t2.TABLE_ID
WHERE t2.TABLE_NAME = 'tableName' AND t2.TABLE_ACTION IN ('D', 'U')
Barmar
  • 741,623
  • 53
  • 500
  • 612