SQL Query to Remove Old WooCommerce Order Data for Backend Speed

SQL Database Query to Remove Old WooCommerce Order Data for Backend Speed

I was recently working on a site which was experiencing very slow backend loading times, especially within WooCommerce when looking at customers or orders. The site had been running for a number of years and had over 500,000 orders still saved.

By default, a WooCommerce order will add approximately 40 rows to the postmeta tableEvery order writes its meta — totals, addresses, line items — as individual rows in wp_postmeta. They add up fast. – so if your website gets 10 orders per day, you’re automatically adding 146,000 rows per year – and the larger the postmeta table is, the longer a query to the table will take to execute.

As you can imagine, with hundreds of thousands of orders – or tens of millions of rows – this became an issue.

WooCommerce released"High Performance Order Tables" as a way of combatting this issue, but another simple method is to regularly audit the order table. Do you need to keep orders that are 5-10 years old? Is your website the best place to store them?

How?

It took me a while to find a solution for this, but I finally stumbled across this stackoverflow of the same question being asked a number of years ago. I don’t usually post articles like this, but it worked perfectly (and instantly) for me, so I thought I’d share.

DISCLAIMER : PLEASE TAKE A BACKUP BEFORE RUNNING ANY SQL QUERY LIKE THIS. JUST BECAUSE THIS QUERY WORKED FOR ME, DOESN’T GUARANTEE IT WILL WORK FOR YOU, AND I ACCEPT NO RESPONSIBILITY FOR DATA LOSS.

You may need to change the date (I have it set to orders pre-2017), or the table prefix (I’m using the standard wp_) for this to work for you. This is the sort of thing my care plans handle each month.

DELETE
FROM    wp_woocommerce_order_itemmeta
WHERE   order_item_id IN (
    SELECT  order_item_id
    FROM    wp_woocommerce_order_items
    WHERE   order_id IN (
        SELECT  ID
        FROM    wp_posts
        WHERE   post_date <= '2017-01-01'
    )
);

DELETE
FROM    wp_woocommerce_order_items
WHERE   order_id IN (
    SELECT  ID
    FROM    wp_posts
    WHERE   post_date <= '2017-01-01'
);

DELETE
FROM    wp_comments
WHERE   comment_type = 'order_note'
AND     comment_post_ID IN (
    SELECT  ID
        FROM    wp_posts
        WHERE   post_date <= '2017-01-01'
);

DELETE
FROM    wp_postmeta
WHERE   post_id IN (
    SELECT  ID
    FROM    wp_posts
    WHERE   post_type = 'shop_order'
    AND     post_date <= '2017-01-01'
);

DELETE
FROM    wp_posts
WHERE   post_type = 'shop_order'
AND     post_date <= '2017-01-01';