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

1 Views

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

Read More
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?

Post Views: 1

Related posts

Leave a Reply

2 comments

  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.

  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)