Welcome to Sydney Business Web Technical Solutions Problem 3 - Excessive Search Times for Orders
Every day, we solve problems for eCommerce website owners. We had a think about how we might use this activity to help others, and came up with this idea: Every week or three, we'll take the trickiest problem and publish our solution.
Important! - Some of these solututions involve adding code to your website (WordPress and Woocommerce mostly), so please ALWAYS be careful. We are not in any way responsible, directly or indirectly for any impact or consequences our code or advice has on your website, nor are we liable for any damage arising from such use.
Always back up your website before changing or adding code and/or editing the database, This is critically important!!!
PROBLEM 3: ExcESSIVE SEARCH TIME ON THE WOOCOMMERCE BACK END
My Journey in Optimizing WooCommerce Order Search Time
Recently, I faced a significant challenge with my WooCommerce site which had over 139,000 orders. The issue was alarming: searching for orders, even though the website is on my fast VPS server took up to 30 seconds, a delay unacceptable for any efficient online business.
Initial Attempt with Smart Manager Plugin
I started by trying the Smart Manager plugin, aiming to archive orders older than 13 months to declutter the database. While Smart Manager is a powerful tool, it wasn't efficient enough for the large volume of data I was handling. Just placing the orders in sequence timed out.
Exploring WP-CLI and SSH
Next, I explored using WP-CLI through an SSH connection. Despite the potential of this method, the size of the data made the process impractical. The WP-CLI commands were correct, but executing them for such a large number of orders was simply too time-consuming and cumbersome. Using this method requires technical competence - use of a PUTTY Terminal and a solid understanding of command line syntax. We're pretty good at this, but manipulating so many orders caused the terminal to hang up usually. We were trying to get all orders older than 12 months exported on a data sheet (CVSV) before exporting them before deleteing them- no joy.
Cache Optimization
We installed REDIS object cache - this made no discernible difference which is not surprising because a cache only saves data structures that are frequently called. We left it installed though for gradual improvements.
Success with SQL Queries
Finally, I turned to direct SQL queries via phpMyAdmin. This approach proved to be a game-changer. By running a series of SQL queries, I was able to efficiently export all orders older than 12 months (around 100,000 of them), delete old order data from various tables in the database, and delete the actual orders, thereby significantly reducing search times from 30 seconds to an impressive 5 seconds. NEVER TRY THIS UNLESS YOU HAVE BACKED UP YOUR DATABASE.
Conclusion
This experience reminded me and my team the importance of regular database maintenance and optimization in WooCommerce. Direct SQL queries, when executed correctly, can vastly improve site performance, proving to be a more effective solution in this case than plugins or WP-CLI for handling large datasets. PLUGINS CANNOT HELP YOU ON THESE ALRGE SCALE ISSUES.
ALWAYS DELETE THE META DATA FOR ORDERS, NOT JUST THE ORDERS!Results
Our problem was that a long-running Woocommerce website with 130,000 orders was taking 30s to search for an order. Cache optimization achieved little. Using Command Line Tools (CLi) with SSH access was not practical. Direct operation onf the SQL database was quick and efficient. Se successfully exported the old orders to a CSV archive page ans reduce search time to 5s or less.
Need help? -
CONTACT SYDNEY BUSINESS WEB NOW!
