使用IN语句缓慢mysql删除查询

I am doing a delete query which should run pretty much instantly

我正在做一个删除查询,它应该立即运行

DELETE FROM
  TableA
WHERE
  TableA.SomeID IN (SELECT DISTINCT TableB.SomeID FROM TableB WHERE TableB.Something = Blah)

If I execute the query inside the IN statement on its own then it runs instantly. If I then comma separate the results of that and paste them into the outer query then that query runs instantly. However, when I run them together it takes 24 seconds. It's like the inner query is getting executed once per row. I also tried modifying it to use an EXISTS statement but that was also slow. Do I need to resort to a temp table for this?

如果我自己在IN语句中执行查询,那么它会立即运行。如果我然后用逗号分隔它的结果并将它们粘贴到外部查询中,则该查询立即运行。但是,当我一起运行它需要24秒。这就像内部查询每行执行一次。我也尝试修改它以使用EXISTS语句,但这也很慢。我需要求助于临时表吗?

2 个解决方案

#1

Nested subquery will be slow. Try using a JOIN instead:

嵌套子查询会很慢。请尝试使用JOIN:

DELETE A
FROM TableA AS A
INNER JOIN TableB AS B
    ON A.SomeID = B.SomeID 
WHERE B.Something = Blah

Also, ensure that TableA.SomeID and TableB.SomeID both have an index on them, to maximize the performance of the join.

此外,请确保TableA.SomeID和TableB.SomeID都具有索引,以最大化连接的性能。

2 个解决方案

#1

更多相关文章

随机推荐