Improving Database Performance Made Easy
TLDR
- Out of the box WooCommerce works well for small to medium size shops. But for large shops, with many products and orders, you might hit performance limits.
- Adding high performance database indexes is easy and in some cases can improve database performance a hundredfold.
Situation
Recently one of our customers contacted us with a performance issue detected on the WooCommerce purchase confirmation page, while the Pixel Manager for WooCommerce was active. The page would take approx 10 seconds to load, which indeed is uncommon and certainly too long. Customers of that shop, who try to purchase a product, might think something is wrong and abort loading the page.
Analysis
Recently we added several new fields to the output of the Pixel Manager on the purchase confirmation page. Those fields output if a customer is a new customer or an existing customer, plus different types of customer lifetime value calculations.
In order to determine the values of those fields the Pixel Manager requires to query two tables of the the database. In comparison to other queries, these queries use more processing power. In our testing environments, some with hundreds of thousands of orders, those queries don't increase the load time of the purchase confirmation page significantly. But, as the example of one customer showed us, there are system configurations around which can't handle those queries very well, and run into a performance bottleneck.
Interestingly that customers database contained 10 times less orders than the biggest shop where we tested the queries. So the database size clearly doesn't matter. It is some combination of database hardware, software and configuration that can lead to that performance bottleneck. Unfortunately those specific factors can't be influenced by the Pixel Manager.
But, there is a solution.
Interested to get updates?
Sign up to our monthly newsletter today.Solution
In our research we found a simple and impressively well working solution to remove that bottleneck. On a shop with hundreds of thousands of orders the solution brought down the slowest query, which took 0.3729 seconds down to 0.0028 ! seconds. That's 133 times faster than before!
How does the solution work and how can it be implemented on a WooCommerce shop?
WordPress sets up indexes on the tables in order to make queries faster. But, there is a lot of room to improve those indexes. Olliver Jones pointed out in his StackOverflow answer how new and improved high-performance indexes can be created on the most commonly used database tables. Simply by adding those new high-performance indexes, those queries can run so much faster.
Olliver also created a simple plugin, called the Index WP MySQL For Speed plugin, which can create those indexes for you. Since WooCommerce uses the same WordPress tables to save orders and order meta data, this solution works equally well for WooCommerce.
