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?

Answer 1

You need change your max_allowed_packet to a higher value in your my.ini configuration file.

This might help:

Packet Too Large

A communication packet is a single SQL statement sent to the MySQL server, a single row that is sent to the client, or a binary log event sent from a master replication server to a slave.

The largest possible packet that can be transmitted to or from a MySQL 5.5 server or client is 1GB.

Answer 2

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)