Order seach extremely slow

Hi @schero86,

The issue you’ve described might be due to a large number of orders in your store, or it could be related to server performance.

For better assistance, can you provide more details? Additionally, please can you share a copy of the following:

• System Status Report: Navigate to WooCommerce → Status. Select Get System Report and then Download for Support.
• Fatal Error log: Share a copy of any fatal error log found under WooCommerce → Status → Logs.

You could copy and paste your reply or share it via Mozilla Community Pastebin and send the link here. This will help us investigate further and provide you with a more accurate solution.

Looking forward to hearing from you!

For now i disabled HPOS and everything is ok.
We have 48000 order records

Hi @schero86,

With 48,000 order records, it’s understandable that you might experience some performance issues when HPOS is enabled.

HPOS is designed to improve search performance, but when there are many orders, it might slow things down due to the increased database load.

To further investigate and potentially optimize this, did you test HPOS with comparability mode enabled? Did you notice any difference?

We appreciate your cooperation and patience as we work through this issue.

Yes, used it only with compatibility mode enabled and it was slow.

• This reply was modified 1 year ago by schero86.

Hey @schero86,

Thank you for sharing further updates.

This could still be related to server performance or other factors, such as the configuration of your database.

At this point, I recommend contacting your hosting provider to ensure your server resources are sufficient for handling such a large amount of data. They may be able to assist with optimizing your database for better performance.

Also, please note that when you enable compatibility mode, the system will store your data in two tables (4800X2=9600 orders data ??) to ensure that all plugins and code not supporting HPOS continue functioning correctly.

Moreover, if HPOS doesn’t work well with your site, feel free to keep using it without HPOS.

Rest assured, I’m still trying to replicate this issue on my testing site, which contains over 48000 order records (generated using wc-smooth-generator ). So far, I’ve only activated WooCommerce with the Storefront theme and haven’t encountered any loading speed issues yet.

I hope this provides some clarity on the matter. If you have any other questions or need further assistance, please don’t hesitate to ask.

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

Hi @smgdarien,

I understand your frustration with the current search performance when using HPOS, especially with a large number of orders. It’s clear that you’ve made significant efforts to optimize your store and database, and we appreciate your insights.

By the way, it looks like team is already aware of this issue:

• https://github.com/woocommerce/woocommerce/issues/45721
• https://github.com/woocommerce/woocommerce/issues/41317

At the time being, we can’t give you an estimate for when the team will look into or fix the issue, as bug reports are prioritized based on a few criteria. Please subscribe to the GitHub issue if you’d like to receive updates.

Thank you for your continued support of WooCommerce!