smgdarien

Noted! However in this case, multiple users are reporting the same issue across a variety of builds. Which signifies it’s not a unique issue per site and its characteristics

Most WC stores and owners don’t have the time or resources to each individually troubleshoot or maintain seperate ticket threads. Creating seperate threads for the same issue can become a burden for future users trying to resolve or track the issue as well, as not all threads will show the resolution

in this case, as the issue seems to be similar and commonly reported by a large number of users, I’d recommend focusing the resources available to this thread

That way we can collectively report and keep track of any possible solutions or further findings to resolve the bug

Thank you for understanding!

Hey orlovak,

I’d say this is the same issue, so I wouldn’t stress over creating a new issue. Just because you’re using elementor doesn’t mean it’s not related

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!

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

Hey just wanted to mention you’re not crazy, and having a large database isn’t a valid answer as towards why WooCommerce’s admin order search has such lack lustre performance

Using the ‘All’ sorting method is the issue at the moment, try opting for Order iD, Customer Name/Email or Products to speed things up for now

Hopefully WooCommerce become aware of the massive issue HPOS is causing for stores with a large amount of orders when searching via the wp-admin

To clarify why I disagree with @shameemreza’s response, I am running a store with 300,000 orders on HPOS. I’ve used their CLI tool to remove all legacy orders from the wp_postmeta table. Meaning I do not have any duplicate data (compatability mode isn’t enabled). Currently running a highly available custom store, on a load balanced 5x 8vCPU VPS + dedicated database. Another example, a site running on autoscaled Kubernetes + dedicated database. Both stores runs flawlessly, yet never return wp-admin order searches in less than 12 seconds when the ‘All’ tab is toggled.

@shameemreza mentioning their tests using simluated order data wouldn’t be showing accurate results as ‘real life’ order/item meta and customer addresses being searched using the ‘All’ tab are very different when compared to dummy orders. I have dramatically cleaned my stores meta, down to the bare minimum which should not have been required. Nonetheless, even after cleaning the database up the order search was still slow.

Contacting your hosting provider isn’t going todo anything. This is purely a WooCommerce issue that requires the developers attention to resolve

Same issue here. Reached out to support and they asked me to try adding a PHP snippet to remove searching by the billing details. Didn’t help, so following this thread for any potentional fixes

Currently can only search via Order ID, Customer Email/Name & Products. Using the ‘All’ sorting method is extremly slow ~ and I’m running a load balanced 5x 8vCPU VPS + dedicated database

Thank you!

I want the message to appear, and I’d like the sale price even if the product is out of stock

Regular WC behaviour still shows the price when out of stock, but when a storewide sale is active using Discount Rules, the discounted price does not reflect on sold out products

Same here, when using the ‘All’ tab. Optmised my entire site, DB from 5gb to 1.5gb after using the hpos cleanup cli commands

Still, no luck ??

After you update your SQL mode, clear your analytics cache (WooCommerce > Status > Tools > Clear analytics cache), head to Analytics > Settings, Delete Historical Import, Reimport

In my case I had to uncheck the skip option to see the correct data

Found a solution, it was due to other conflicting Global SQL mode options

Try setting only the options below, my customer list appeared after regenerating the lookup table

• This reply was modified 1 year, 1 month ago by smgdarien.

Followed everything in this topic, using standard WC testing practices (storefront, only WC active), altered the SQL mode as well.

No luck! The table is updated when I import the data manually via Analytics > Settings

It’s a nice fallback cache

When using another varnish plugin (proxy cache) it works like a charm. So I just assumed it was this plugin having issues as the debug log shows only Super Cache trying to purge via HTTPS, failing and retrying on HTTP, and working. It essentially is just causing a lag when waiting for the HTTPS timeout

Again works totally fine when configured in a another plugin using the same settings on HTTP

I’ll stick with proxy cache for now, but if a workaround is found in your plugin not to cause the long delay when waiting for the varnish timeout to retry using HTTP let me know

Yes

Would be great, I’ve switched to Media Cloud for the time being