search

OllieJones / fast-woo-order-lookup

WooCommerce / WordPress plugin to speed up searches for orders
GNU General Public License v2.0
13 stars 1 forks source link

WooCommerce 8.8.0 got a ludicrously slow query to get non-private meta_key values. #8

Closed OllieJones closed 6 months ago

OllieJones commented 6 months ago

A performance regression was reported on WooCommerce 8.8.0. https://github.com/woocommerce/woocommerce/issues/47212 . The root cause of the problem is a full-table scan to satisfy this query from the wp_wc_orders_meta table. That can take minutes on stores with lots of orders. Which of course drives store owners crazy. Can't look at customer orders? Well then how do you get your work done?

The bad query.

SELECT DISTINCT meta_key 
 FROM $ordermeta 
 WHERE meta_key NOT LIKE '\\_%'
 ORDER BY meta_key ASC

The query that will be compatible with what goes into 9.0.0, and should go into this plugin immediately.

SELECT DISTINCT meta_key 
 FROM $ordermeta 
 WHERE meta_key NOT LIKE '\\_%'
  AND meta_key NOT BETWEEN '_a' AND '_z'
  AND meta_key <> ''  ORDER BY meta_key ASC

This is pretty good when the meta_key column has a prefix index. It's GREAT if the prefix index can replaced by a non-prefix index.

There are two fixes scheduled. The one scheduled for 8.9.0 cleans up some logic but does not resolve the performance problem.

OllieJones commented 6 months ago

This fix is now supported. Read about how to use it here. https://www.plumislandmedia.net/wordpress-plugins/fast-woo-order-lookup/

This plugin is on the plugin repo review queue. So it should be available on the repo sometime within a month or two.

OllieJones commented 6 months ago

That problem was originally reported here. https://wordpress.org/support/topic/slow-loading-on-order-edit-page/