Woo Tables

Thanks for the reminder!

Some of those HPOS tables exhibit suboptimal key design. But here’s the thing: keys should be designed to match the tough-to-satisfy queries. That’s hard to do without knowing which queries are slow. That’s the reason we went to the trouble of building the Monitor feature in the plugin: it captures queries and shows us the slowest ones.

I don’t have access, sad to say, to a WooCommerce store with many records in HPOS. If you do, could you please capture a monitor while doing operations using those tables (checkout, maybe, or fulfilment)? Then upload the monitor. Don’t forget to reply to this topic and let me know the upload id.

That way we can do a decent job of key design. (A lot of people will use this plugin, and we don’t want to mess up the key design.)

• This reply was modified 8 months, 2 weeks ago by OllieJones.

Sorry for the delay in response, I’ve been on a mission to find a solution!

I noticed this plugin wasn’t working super well on my site with a 5gb database. I don’t think this was a plugin issue, the DB desperately needed to be cleaned up

After clearing a ton of entries that were old/unused, swapping to HPOS & removed the legacy shop_orders which lowered the wp_postmeta from 11m rows to 2m I still saw poor performance when searching for orders in the wp-admin

WC added a new sorting filter to the wp-admin > orders page with the options of ‘ID, Email, Name, Products, All’ each option works super well apart from ‘All’ which still has the same behaviour when searching orders from the wp_postmeta using a wildcard. I believe you’re aware of from your fast woo order search plugin (thank you!). Unfortunetly the plugin no longer works with the new sorting filter ??

Below is a query I caught which is slow. I believe it’s due to shipping/billing addresses being searched? Apparently Woo mentioned adding FTS indexes for customer addresses to speed searches up but yet to see any progress on this

SELECT?wp_wc_orders.id?FROM wp_wc_orders LEFT JOIN wp_woocommerce_order_items AS search_query_items ON search_query_items.order_id =?wp_wc_orders.id?WHERE 1=1 AND (wp_wc_orders.status IN (‘wc-pending’,’wc-processing’,’wc-packing’,’wc-on-hold’,’wc-completed’,’wc-cancelled’,’wc-refunded’,’wc-failed’)) AND (wp_wc_orders.type = ‘shop_order’) AND ( ( wp_wc_orders.billing_email LIKE ‘Darien%’ OR wp_wc_orders.id IN (?
SELECT search_query_meta.order_id
FROM wp_wc_orders_meta as search_query_meta
WHERE search_query_meta.meta_key IN ( ‘_billing_address_index’,’_shipping_address_index’ )
AND search_query_meta.meta_value LIKE ‘%Darien%’
GROUP BY search_query_meta.order_id
?) ?OR search_query_items.order_item_name LIKE ‘%Darien%’ ) ) GROUP BY?wp_wc_orders.id?ORDER BY wp_wc_orders.date_created_gmt DESC LIMIT 0, 20

Oh, sigh. I’ve seen this before from another user with a large store. I guess this happens when using the search box on the orders page.

I put together a little unpublished plugin to try to remediate this problem.

https://www.plumislandmedia.net/wp-content/uploads/2023/11/fast-woo-order-lookup.0.1.2.zip https://github.com/OllieJones/fast-woo-order-lookup/blob/main/fast-woo-order-lookup.php

But this works by getting rid of the unanchored (col LIKE '%Darien%') searches, therefore when you use it the search box doesn’t find as much stuff as you might like. So, pretty useless.

A word-by-word search, a la Relevanssi, or maybe FULLTEXT, is the way to go here, as you mentioned.

(Aside: PostgreSQL offers a feature called “trigram indexes” specifically to speed up those unanchored searches. But porting WordPress to that DBMS is really hard.)

Emphasis on that sigh! I’ve tried elasticpress but for some reason ep + redis did not play well with eachother on this site

I tried the plugin but it seems like it still tries to use the wildcard with the new WC search, queries here https://ibb.co/yPyND2z

I believe Woo are looking into this, I’ve been following a few github requests. However, super slow progress as it’s definetly not a priority for them which is ashame. I have no idea how high volume Woo stores are currently searching orders! A 14s wait time for results is crazy

I’ll look into Relevanssi, thank you!

Yeah. The way they store those shipping addresses as metadata in wp_wc_orders_meta means there won’t be an easy FULLTEXT search index fix, because there’s a ton of other crapola (that’s a technical term) in that meta_value column of that table. A FULLTEXT index on it will have too much non-text stuff in it to be useful.

This situation is unacceptable. I’m updating that plugin I mentioned to try to make it do trigram searches. I’ll do my best to make it work with and without HPOS.

Hello, again.

I’ve updated that plugin to do trigram searches, to accelerate search operations on the WooCommerce order page. Here’s the zip file for a test release of the plugin

https://www.plumislandmedia.net/wp-content/uploads/2024/03/fast-woo-order-lookup.0.2.1.zip

Here’s the source code. https://github.com/OllieJones/fast-woo-order-lookup

It would be very helpful if you could try it out for me on your installation. Note: you should activate this plugin with wp-cli if you possibly can, because it creates a lookup table during activation: wp plugin activate fast-woo-order-lookup. If you activate it from the dashboard’s plugin page it may time out.