Why is the order page on my WooCommerce store so slow?

Question

Since WooCommerce 8.9 came out, I've noticed a big slowdown when I click on an order page from WooCommerce -> Orders in my dashboard. I have a big store with many orders, and it takes many seconds to show the order page.

It's only a problem with HPOS enabled.

Query Monitor pins the blame on this slow query:

SELECT DISTINCT meta_key
FROM wp_wc_orders_meta_copy
WHERE meta_key NOT LIKE '\\_%'
ORDER BY meta_key ASC
LIMIT 30;

What can I do to speed this up?

Answer 1

The root cause of this performance problem is the following prefix key. It must be scanned in its entirety to satisfy the query, which is very slow when there are many orders.

ALTER TABLE wp_wc_orders_meta ...
ADD KEY meta_key_value (meta_key(191), meta_value(100)), ...

Adding the following key remediates the slowness.

ALTER TABLE wp_wc_orders_meta
ADD KEY slow_ordermeta_workaround(meta_key);

This only works on InnoDB tables with the DYNAMIC row format (most tables in 2024).

If you have wp-cli you can use this command to add the key.

wp db query "ALTER TABLE wp_wc_orders_meta ADD KEY slow_ordermeta_workaround(meta_key)"