My journey fixing WooCommerce slow speed with many variations – part 1

Ok, so I love WooCommerce, and I love speed, but this one has been on the back burner for far too long. Almost all of the refund requests for Scalability Pro that I get are because users were hoping to see their sites with 1000s or 10000s or even 100,000s of variations per product speeded up.

If you’ve used the WooCommerce variations system manually, you might wonder how anyone could have 100,000+ variations since it gets so unwieldy and slow to use the editor above even 50 variations. The answer is imports. WP All Import makes it easy to import variations to your site from your datafeed you generated elsewhere.

Understanding why variations are slow

The database structure used matters, and the mechanism WooCommerce uses to fetch the data matters too. Here’s an example using a sofa:

• Parent product: Deluxe ABC 2 seater sofa
• Variation 1: Material (Leather, cloth, linen)
• Variation 2: Colour (Blue, black, green, etc – imagine 30 colours)
• Variation 3: Finish (Walnut, Birch etc – imagine 20 of these)
• Variation 4: Size (measured in inches or centimetres, let’s say 3 options here)

Ok – so to calculate the total number of variations the above 4 options create, we multiple the number of options by each other. So – 3 x 30 x 20 x 3 = 5400 variations just from these 4 options.

WooCommerce actually creates all 5400 products as rows in your wp_posts table in the database and sets the post_parent to the ID of the parent product.

Now, technically, reading 5,400 products from a database should not be slow, but that’s not quite what WooCommerce does – it reads them one-by-one – and there’s a reason for it.

Why does WooCommerce read product variations one at a time?

The reason for this seems to be because of possible PHP filters. It’s possible for anyone to add extra PHP code to WooCommerce hooks and filters, and some of these hooks may, for example, exclude some of the variations based on the user, or they may adjust the price based on the country or any myriad of things.

Additionally, you may have filters enabled – e.g. just show me ‘blue’ couches, so WooCommerce needs to check each of the variations and filter them out so that it can show the correct price range and decide whether it should show the parent product at all.

So – if you load your archive page containing this sofa product above, using Query Monitor, you’ll see two important things.

Firstly – you’ll see a query that fetches 5,400 rows. This query will be fast, e.g 0.05 seconds or something, because that’s how fast databases normally are.

Then, if you group the queries by ‘component’, you’ll see WooCommerce making upwards of 5,400 SQL queries. That’s one query for the parent product, then one each for each child plus any other standard queries made by WooCommerce. On top of this, after each query it is then parsing that data using PHP and figuring out what should be displayed on the page.

What solutions are available?

The first, and most obvious solution is to avoid using product variations in this way. That may sound silly, but if it’s possible for you to adjust your product to use product rules instead of product variations then you will get your fast performance back.

For example, with 3 sizes: small, medium and large, we could have product rules of:

• Small (no change)
• Medium (+ £5)
• Large (+ £7)

So – if someone chooses a medium t-shirt, £5 will be added to the price. That’s far simpler than product variations and far faster too.

Ok, but most of you don’t have this option – all of your prices are unique – so what to do? Caching?

Caching can help – if proper object caching were used, WooCommerce could remember the final product displayed for this variation + this combo of search filters. But, there are a *lot* of products and a *lot* of possible search filter combinations, so this would result in a massive object cache which most of you would not be able to use on your teeny tiny servers.

The ultimate solution – if we ignore the hooks and filters – would be to alter the SQL query based on the search parameters and then filter in the database and use the database to tell us the price range. Again – this would skip any filters that were added to prices, although we could theoretically then run the filters on the final 2 prices (min and max price range).

An alternative solution, presuming it’s only the price range that variations care about, would be to remove the range altogether. I’m not convinced it’s a massive issue for consumers to see the top-end price range and in fact I think they’re used to seeing things like ‘Prices from £1,500’. The only way this could work, without altering the SQL query to filter based on the search query and category and attribute filters, would be to store the price-from against the parent product and then accept that sometimes this cheapest price would be wrong.

For example, if you have a t-shirt with red (£10), blue (£15), white (£20) for some reason, but then the user searched for ‘blue’ the ‘price from’ would still say £10. That’s not really ideal, BUT when the user clicks through to the product detail page the filters are no longer active anyway and the ‘red’ shirt would still be a selectable option, so it’d probably be understood by the users. This approach would be by far the quickest and easiest to implement.

What else do variations need?

I don’t use them much myself – it tends to be affiliated products I use which don’t have variations – but from all the client work I’ve done and all the sites I’ve seen and all the archives I’ve seen, the only thing I think it needs is the price range plus potentially filters on those prices.

It would be great if you could all answer in the comments below and help me flesh this out so I can fix WooCommerce product variations speed once and for all.

When it comes to the actual product detail page, that’s a lot easier to optimise through the use of Ajax and post meta values – the min and max price of the product do not change when there are no filters active, so we can just store them at admin-time and display on front end, and then changing the price per variation happens through speedy ajax callbacks.

Anyway, let me know your thoughts – I’d like to get moving on getting this fixed as it’s been a major bugbear for a while now.