My journey fixing WooCommerce slow speed with many variations – part 1
- Installing Xdebug remotely to debug WordPress and gather profile traces - November 23, 2022
- Speeding up WooCommerce 7 (wp-admin and imports) - November 4, 2022
- More speed boosts for wp-admin and imports with Scalability Pro upgrade - October 12, 2022
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.
Table of Contents
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.
July 6, 2019 @ 6:48 pm
One possible solution is just using Attributes, depending on the business data management needs:
July 6, 2019 @ 7:03 pm
Yeah attributes are useful IF the price doesn’t change per variation, but I have many clients who have real need of the price changing per option-combo.
July 8, 2019 @ 10:03 am
thanks for that interesting post. We have a ticket open so I guess we`ll be communicating through that.
July 11, 2019 @ 2:01 pm
This has been the major pain on a site I’ve been working on (10000+ products, some with 1000+ messy variations migrated from long running Magento shop) and got to similar conclusion. For my case it would be enough to have “prices starting from” shown on archive pages as transforming product data to use product attributes/rules would take a lot of manual effort.
Thank’s for the post and your continuing efforts!
July 12, 2019 @ 2:09 pm
Ok cool – and what about the scenario where there is a blue (£10), red (£15) and green (£20) variation and the user searches for red?
I am fairly certain I can actually filter this quickly at the DB level and get the prices from and eliminate the need for the per-variant code, and in that case, I’ll be able to show: £15.
But would you want the ‘red’ variation image to show or the parent product variation image?
And would you want it to show price: £15 or ‘Prices from £10’ in this case too?
July 12, 2019 @ 3:55 pm
In this case showing the starting price “Prices starting from £10” would well do it already.
If this was implemented though, I’m having some doubts how accurately the search could pick up the right variation price as with the color example, and if it would have much actual benefit for the customer (store largely selling clothes). Looking at search statistics there’s barely any searches (<1%) for a color, or size for that matter that could narrow down the search like this. Also, often if there's a color option there's a size option too so it might add complexity to this logic.
The shop does not have separate images for variations so the parent images are always used.
December 30, 2019 @ 11:47 am
Wow, finally a post about someone who has the same problem as I have.
I had no idea that the slowness of my site was due to the variable products.
Almost all my products are variable products (I sell everything in 6 finishes) and my stock management is far more important than the prices.
January 27, 2020 @ 11:29 am
yah.. understanding why variations are slow.. you already add more things .. and absolutely correct you are! your content is really expressed your merit .. this post help to search the topic.. thanks for your information.. your example is more helpful to clear the topic.
March 24, 2020 @ 3:18 pm
I am in the same boat. I have wp-all-import plughin with 55,000 variations on a product which just drowns any server I have put it on. I currently have upgraded to an XL VPS – still crashing. So please tell me about “The ultimate solution”…sql query…kindly elaborate in laymans terms
April 6, 2020 @ 4:04 pm
Is it possible for you to change any of your variations to product options? e.g. if there are any variations which don’t alter the price at all, they should be made product optoins, and if you have variations that change the price by a consistent amount across all other variations then they should be product options too. E.g option 1 +£0, Option 2 + £15, Option 3 + £30 etc
April 6, 2020 @ 7:29 pm
I don’t think so, these products are all unique and have different prices. Well they are the same product but all different variations of customisation.
April 6, 2020 @ 8:08 pm
Ok, then so far, the best approach to speeding these up is to add Redis to your site and the Redis plugin by Till Krus. My upgrade to fix this further is not yet ready.
April 6, 2020 @ 9:06 pm
Do you mind sharing a link to this redis plugin so I can ensure we are talking about the same thing? Also, will this resolve my issue then?
April 7, 2020 @ 9:47 pm
You can also use W3 Total Cache – it’s in the Object Cache settings page there. In fact, W3 Total Cache gives a bit more control but the one above I use a lot.
April 3, 2020 @ 4:25 am
using woocommerce composite products will be better at handling this no?
April 3, 2020 @ 5:10 am
Yes – if it’s possible with your config. Product Options can help too. Variations are really intended for anything that changes the price in a unique way. So – if you ave product options that when enabled add £10 consistently for option A, +£30 for option B etc then options are a better choice than variations.
Variations give the advantage that the specific combination of options can have its own totally unique price, but the major downside that they really slow down woo.
I’ll get back to working on the fix for this soon – got other updates coming out shortly.
April 14, 2020 @ 9:03 am
Hi Dave! great website with great information and plugins. This article drew my attention particularly as I‘m having the exact problem described above.
I wouldn‘t mind prices stated „from 100$“ at all, even when filters are active. The load time is just not acceptable if one has over 200 variations.
When do you think the plugin to fix this will be ready for sale?
April 14, 2020 @ 9:55 pm
Faster Woo Widgets already improves this quite a bit, as does Scalability Pro, and also adding an object cache helps.
I’m not sure when I’ll get the upgrade to fix product variations permanently, but it’ll definitely be this year.
August 19, 2022 @ 10:14 am
Hi! Is there a fix for this yet?
August 21, 2022 @ 3:55 am
Hi, yes, to be fair it’s been fixed for a while. Just when you’re logged out only, so when you’re looking at status = published rather than status in (published, private) then it’s quite a bit faster with scalability pro.
I’m working on some case studies to demo this difference, will publish soon. In any case, if it doesn’t help you enough we have a 60-day money back guarantee.
April 28, 2020 @ 8:30 am
Thanks for this. My search for speeding up woocommerce continues :-).
I am using variations for size and colour of clothing. Each size and colour of a T-shirt has a unique sku and image which is needed for ordering from our supplier.
The front end site using cache for non logged in users is very fast, however caching is not enabled for logged in users so the site runs like a dog for them. When I login to my Admin panel it is also severely slow.
Would you have you any advice on this?
April 28, 2020 @ 9:31 pm
Make sure you have an object cache in use – those can be used by logged in and logged out users and help a lot with product variations.
May 3, 2020 @ 4:14 pm
Also – with variations, there are plugins and theme features I’ve seen a lot where the code loops through each product variation on the archive page. For example – if you have functionality to change ‘ON SALE’ to ‘-20%’ or similar then that is very frequently badly coded.
I’ll be adding some functionality to Scalability Pro to scan the plugin and theme codebase to identify potential causes of slowdown.
June 3, 2020 @ 12:21 pm
I’m currently running a prize draw website and we use product variations for ticket numbers, which means the pages for prizes with over 200 ticket numbers run very slowly. I’ve seen on the Trello board under the current backlog there is “scalability pro – speed boost for product variations”. Do you think once looked into this would help with variations so large (up to 800). Or any advise as to whether there is any sort of other way round this to boost the speed for these pages?
June 3, 2020 @ 4:22 pm
Do you have Redis object cache installed and configured? That’s the number 1 thing to boost variation speeds currently.
December 20, 2020 @ 1:48 am
Any new headway on Scalability Pro? I recently purchased it and am having the same issue. Currently I have about 800 different variations on a particular product and my page takes ~10 seconds to load. I am Scalability Pro and still not fully happy with the speed.
I am using Product Options for products that allow it due to pricing but unfortunately we do have some products that really need the pricing flexibility of variable products because certain options do not have fixed price mark up depending on variations.
This is my example page where I am running into the issue currently: https://deltakon.com/product/additel-adt-681-digital-pressure-gauges/
December 30, 2020 @ 7:08 am
Hi – the update for variations is not out yet and not in the current sprint – you can see the Trello card for it here: https://trello.com/c/WXbeCZ21/88-scalability-pro-speed-boost-for-product-variations
Make sure you have an object cache (Redis is best, but Memcached is also good). It’s not a perfect solution but it does help a bit.
Aiming to have this fix implemented in February.
January 26, 2021 @ 12:44 pm
Hi – We have this exact issue on our dev site with a few products that have 500 plus variants. Any update on this would be great, but in the meantime would there be a benefit in installing Scalability Pro anyway re this issue? Thanks, this article was really helpful, not least because I know we are not alone!
March 29, 2021 @ 4:02 pm
I see your site is fast now, what solution did you find?
March 29, 2021 @ 4:00 pm
Hi Dave, I’ve been keeping an eye on this.. got an ETA?
March 29, 2021 @ 6:25 pm
No firm ETA yet, sorry. Schedule pretty much looks as follows: This week & next is bug & perf fixing + FWW testing. FWW should be released next week I think.
After FWW is released, I’ll have a sprint dedicated to Scalability Pro – there are quite a few potential things to include in that upgrade.
I haven’t prioritised this as high as maybe I should since there are workarounds that really help – reduce how many variations you have (e.g. using product options or product addons) and use a persistent object cache (e.g. redis or memcached). On top of that, recent versions of Woo have included ‘helper’ tables to improve performance, so I need to take time with a proper Scalability Pro sprint to examine how I can use those tables to further optimise, or whether I actually need to go the whole hog and create my own tables to improve performance.
June 16, 2022 @ 3:05 am
Has there been any further progress in Scalability Pro to speed up variations? Are there any new alternatives apart from the ones mentioned (Redis, Product Options etc)?
June 24, 2022 @ 9:17 am
Any further improvements require custom coding – but in most cases, you can make it work although sometimes you need to customise the code a little.
For example, there is some code in a lot of themes which alters the ‘sales flash’ to show a ‘percentage discount’ – this code got copied from the businessbloomer website where it had been submitted very badly coded. Basically, they looped through all child variations and got the prices from them. This involved loading each product variation. The code rewrite for this involves using a function available on the parent product to get child variation prices in an array. This is maintained at the parent level and is far faster, uses less RAM etc.
So – if you have a performance issue due to product variations, there’s a high chance that your codebase (some plugin feature or some theme feature) suffers from this bad coding pattern.
If you search for get_children() in your code base and then look at that code, if you see the code is performing a loop and then creating an object with each child variation then this code could probably be rewritten.
August 30, 2021 @ 5:46 am
please tell us more about woocommerce product rule and how to achieve that on our site
August 31, 2021 @ 10:25 am
If you have variations where a variation changes the price by a specific amount – e.g. medium + £10 or large + £20 then you can have them as rules rather than variations.
If you have varitions where the price doesn’t change and the images don’t change, you can have them as product addons. It’s a shame variations are the only default included in woo because people overuse them and end up with a slow site.
June 27, 2022 @ 3:26 pm
Hello Dave !
We lunched a Woocomerce website ; https://secondemain-brand.com/
As you can see on mobile version espacially the loading is really slow 🙁
I think it’s because variation product, each piece is unique so we had to install this plug in.
We have already used WP-Rocket but not to much effect. Do you thinkg buying scalability pro is a solution ?https://www.wpintense.com/product/scalability-pro/
June 28, 2022 @ 2:20 pm
The best things you can do right now is to try and reduce how many variations you have and use an object cache. The object cache helps with variations a lot, but if you have > 1000 variations then things will still be slow.
If a variation changes the price consistently and you do not have different images for that variation, then probably this could be a product option or product addon.
Scalability Pro does not help with product variations. It helps with many other areas, but not product variations.
November 8, 2022 @ 10:08 pm
An almighty quest for sure! I’m reading many comments mentioning “product options” but I feel I’m missing something obvious here. I can’t see any plugins or native features for adding product options. Perhaps it’s a reference to “Add-ons”?
If that’s the case, as far as I know, it’s not possible to manage stock levels of combinations with add-ons. Is that the case?
Thanks for the efforts!
November 10, 2022 @ 1:55 pm
Yes if you need stock options per combo then you need variations. If the options do not affect stock levels then you should options – add ons, options, called different things by different plugins and yes this functionality is unfortunately not native. Unfortunately you only get variations as native functionality for handling this stuff which leads to performance issues when people use them for things that would be better served by product options or product addons.
March 8, 2023 @ 4:15 pm
Apologies for the lag, thanks so much for your response! I need to be able to manage inventory for each variation, so will need to work out the best way of using variations.
I’ll try the redis plugin that has been mentioned many times, and I was also wondering if there would be a way to load the product page, THEN load the variations… however I can’t see how to do this. It seems when you click a link to the product page there is a lot of queries in the pre-load phase. I have a lot to learn!