Tech and Life

    Home     Archive     Projects     Contact

Fix Wordpress WooCommerce Slow SUM Queries

The query below tends to be very slow in Wordpress when WooCommerce has a lot of data. In some cases it takes up to 10 seconds.

SELECT SUM(meta2.meta_value)
FROM wp_posts as posts
LEFT JOIN wp_postmeta AS meta ON posts.ID = meta.post_id
LEFT JOIN wp_postmeta AS meta2 ON posts.ID = meta2.post_id
WHERE   meta.meta_key       = '_customer_user'
AND     meta.meta_value     = '12394'
AND     posts.post_type     = 'shop_order'
AND     posts.post_status   IN ( 'wc-processing','wc-completed' )
AND     meta2.meta_key      = '_order_total';

An easy fix is adding an index to the column meta_value on the table wp_postmeta:

ALTER TABLE wp_postmeta ADD key(meta_value(100));

After applying this index, queries tend to be 0.00x seconds.

Hope this helps!

If you liked this post, you can share it with your followers!