MySQL query timing out: (70100): Query execution was interrupted

Question

I want to execute a MySQL query in order to delete all `Wp_posts` table rows which `post_parent` is a `Wp_posts` row with `post_type` set to product; So I do ``` INSERT INTO temp (SELECT DISTINCT id FROM wp_posts WHERE post_type = "product") ``` (Inserts 4k rows) ``` DELETE FROM wp_posts WHERE post_parent IN (SELECT tid FROM temp) ``` .. After around 100 seconds, it returns ERROR 1317 (70100): Query execution was interrupted What could be making this query so bloody slow?

Accepted Answer

Some versions of MySQL implement `in` with a subquery in a very inefficient manner. Change it to a correlated `exists` clause:

```
DELETE FROM wp_posts
    WHERE exists (SELECT 1 FROM temp where temp.tid = wp_posts.post_parent)
```

MySQL query timing out: (70100): Query execution was interrupted

Solution

Problem