Yesterday in the #ecommerce channel of the Post Status Slack there was a conversation around the moving of WooCommerce orders and other data out of the
wp_posts table of WordPress. This is (and rightly so) a hot topic of sorts within WooCommerce right now because of the impact it will have to the entire ecosystem.
One small annoyance for me is when claims about how large WooCommerce sites are slow, which I can understand where this comes from but also disagree when someone believes this is always true and unavoidable. So I thought a sort of brain dump from some of the experiences I've had with WC at scale and avoiding slowness could be useful.
I can say with a fair amount of certainty that the size of the database is not the reason the site is slow. This isn't to say that scale and speed is not related because they absolutely are. The larger the site gets, the more important it is that the people building the site are taking care to ensure that performance is being considered in all aspects of the site, even when it means the core of WooCommerce is at fault.
One good example here is post meta, by default the
meta_value of post meta is not indexed. This impacts nearly every area of the site, a few examples:
- Product searches on the front end of the site, because you wanted customers to be able to search by the attributes, SKU, or other meta data of a product
- Order searches in the back end of the site, because you want to be able to look up orders based on the customers email address or other order meta data
- Customer My Account page, because orders are associated to customers in post meta, the only way to build this page out of the box is by querying against the post meta table on a non indexed column
These are three areas of the site that are likely used daily by customers and store owners in a WooCommerce site and create slowness because of the way they are built, and in turn create the impression that a large site is a slow site.
For context, my work at ColourPop we scaled WooCommerce to a point where our MySQL database was over 100GB in size, with just under 200,000,000 rows of post meta (194,596,005 to be exact). This site also on average loaded in 1-2 seconds from the server. New Relic's application response time generally reported around 1.2 seconds in average traffic situations, 1.5-2.5 seconds when traffic sustained 2-5x normal load.
Even in low or normal traffic situations product searches became slower, order searches became un-usable, the my account page was extremely slow and could actually crash the site in very high traffic situations. In all three cases yes, scale created a scenario where WooCommerce was slow, but at the same time all three are avoidable. Product searches could be run through elastic search creating a blazing fast search that also reduces the load on your MySQL database increasing stability. Order searches requires more work and depends on your specific processes but can also be modified to use more efficient methods of finding your customers orders. The My Account page we took from taking 20-30s for an average customer, to less than 5 seconds when loading 300 orders. We did this by creating a helper table index of sorts that was a drop in replacement for the My Account page. After implementing this, we never saw a slow trace in New Relic from the My Account page in normal site conditions again.
All together, the above solutions were less than 10 hours of work. Now that everything is already figured out, it's even less than that to implement. Improving the search of core WordPress is a matter of installing a plugin or connecting to a third party service, order search you can use the
woocommerce_shop_order_search_fields filter to choose which fields are being searched, and an older version of our index plugin can be found on GitHub.
Your site is what makes your site slow.
It sounds like a joke, but more than the hardware powering your site, or the size of your database, or the version of PHP you are running, the software and architecture choices you make in your site will make or break your performance. You can in fact take a large WooCommerce store and make it run faster than the average WooCommerce users site - I've done it. :)