WooCommerce performance tips

Recently I gave a talk at Wordcamp Rome 2018 about “Using WordPress in a large business” where I touched the key challenges addressed in managing a high traffic e-commerce website powered by WooCommerce and WordPress.

One of the topics was about considering your database “like a garden”, keeping it from overgrowing and maintaining it clean. As the number of orders increase, the impact on database performance can be devastating.

We have addressed this issue drastically, importing orders on a back-end system and deleting them once we verify that the have been acquired correctly.

This might not be a viable solution if you don’t have a back-end system, so it might be useful to periodically delete orders. With a large number of orders this might not be a quick task to do manually. You might find it useful to send your orders to trash with the following MySQL script and emptying trash using the WordPress Admin.

1
2
3
4
5
6
7
###
### Move orders older than 30 days ago TO trash
###
UPDATE wp_posts SET post_status = 'trash'
WHERE post_type = 'shop_order'
AND post_status = 'wc-completed'
AND post_date < DATE_ADD(now(),INTERVAL -30 DAY);

The above procedure is subject to TIMEOUT errors depending on the number of records and your PHP.ini timeout settings (max_execution_time).

If this is the case, you can delete them directly with the following MySQL commands (please double check them with the most current documentation and ALWAYS backup first).

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
###
### DELETE WooCommerce orders relating TO more than 24 hours ago
###
### DELETE ORDER records IN wp_woocommerce_order_itemmeta
DELETE FROM wp_woocommerce_order_itemmeta
WHERE order_item_id IN (
SELECT ID FROM wp_posts
WHERE post_type = 'shop_order'
AND post_date < DATE_ADD(now(),INTERVAL -1 DAY))

### DELETE ORDER records IN wp_woocommerce_order_items
DELETE FROM wp_woocommerce_order_items
WHERE order_id IN (
SELECT ID FROM wp_posts
WHERE post_type = 'shop_order'
AND post_date < DATE_ADD(now(),INTERVAL -1 DAY))

### DELETE ORDER records IN wp_comments
DELETE FROM wp_comments
WHERE comment_type = 'order_note'
AND comment_post_id IN (
SELECT ID FROM wp_posts
WHERE post_type = 'shop_order'
AND post_date < DATE_ADD(now(),INTERVAL -1 DAY))

### DELETE ORDER records IN wp_postmeta
DELETE FROM wp_postmeta
WHERE post_id IN (
SELECT ID FROM wp_posts
WHERE post_type = 'shop_order'
AND post_date < DATE_ADD(now(),INTERVAL -1 DAY))

### DELETE ORDER records IN wp_posts (Must be done AS LAST)
DELETE FROM wp_posts
WHERE post_type = 'shop_order'
AND post_date < DATE_ADD(now(),INTERVAL -1 DAY))

An other option that would help is storing WooCommerce order data in a custom table for improved performance. (https://github.com/liquidweb/woocommerce-custom-orders-table). This would avoid clutter in wp_posts and wp_postmeta in the first place and limiting performance impacts to WooCommerce.

You might find a similar topic, “Slow WooCommerce with Many Products” in this very well written article by Daniel Auener   (http://www.danielauener.com/slow-woocommerce-many-products/).

Leave a reply:

Your email address will not be published.

This site uses Akismet to reduce spam. Learn how your comment data is processed.