Why the WooCommerce Products On Sale shortcode sucks (including a free plugin to fix it)

• Author
• Recent Posts

Talk to me

Dave Hilditch

Founder at WP Intense

Author of the WPI performance guides and creator and coder of the plugins sold by WP Intense.

Talk to me

Latest posts by Dave Hilditch (see all)

• Running huge imports with WP All Import reliably (yes, even if you use Cloudflare!) - September 19, 2023
• New SQL enhancement for Scalability Pro to fix WooCommerce long-running query in the product-hero block - August 31, 2023
• Create a static favicon.ico to avoid surplus PHP requests for rush traffic - July 25, 2023

You can chat to all our authors on our Discord

UPDATE: It’s been a long time since I wrote this Products on Sale shortcode plugin – since then I’ve integrated way better functionality into my Faster Woo Widgets plugin so you can have /sale/dog-toys/black/ as your permalinks and have your products-on-sale filterable, paginated and optionally indexable by google. If you get Faster Woo Widgets, please disable the plugin linked in this article since there may be some conflicts.

There are some common performance problems people come to me with. Probably the most annoying of all of these for my customers is telling them not to use the WooCommerce ‘on sale’ shortcode. The code inside is really awful for performance, hurting the trifecta of RAM, disk and CPU on your poor wee server. What did your server do to deserve this? What did you do to deserve this?

The answer is nothing – it’s normal to want to show products on sale. Read on to find out why you shouldn’t use the default WooCommerce way of doing that, and what you can do to show products on sale to your users.

A bit of background – the birth of a monster

WooCommerce started from a company who were creating shop themes for a plugin called Jigoshop. That plugin still exists. WooCommerce (WooThemes back then) got so big that they made an offer to buy JigoShop. The offer was to get hold of the staff too so they could develop the plugin in the direction they wanted. Jigoshop refused, so WooThemes took the Jigoshop code and forked it, renaming it WooCommerce. This is all legit and above board – pretty much all WordPress plugins are coded under the GPL license to encourage developers to ‘reuse’ code from other developers. That’s not to say it didn’t create a stir at the time. They snatched a couple of the key developers from JigoShop, forked the code, renamed it to WooCommerce and hey presto – they now had their own eCommerce plugin.

If that sounds harsh on Jigoshop, it probably is. But then Jigoshop didn’t give their talent enough of a good reason to stay. Anyway, along with all the good stuff from Jigoshop came all the bad stuff too.

If you take a look at the Jigoshop shortcodes, they’ll probably look familiar to WooCommerce users.

You can see these shortcodes for yourself here – https://www.jigoshop.com/documentation/our-codes/

In WooCommerce 3.1, the on_sale shortcode was removed and the functionality was moved to the ‘products’ shortcode. Unfortunately, thinking performance might have been improved was expecting a bit much – I hear they might be doing that in the upcoming database redesign that’s been promised for at least a year.

That brings us to now – users have growing stores, and those stores are grinding to a halt.

Why is the WooCommerce sale shortcode so slow?

I have to do a bit of guessing here for the background, but I have a lot of experience at guessing, so here goes.

Firstly, WooCommerce/Jigoshop implemented multiple types of products for various scenarios. These include simple products, variable products, external/affiliate products and subscription products. There are others – you can extend the product types – but this is enough to be getting on with.

Simple products and external products are quite simple – there’s one product, one option, one price. This price gets stored in multiple fields, but that’s ok – there’s _sale_price, _regular_price and then there’s just _price. _price is effectively storing the display price, so if there is a sale on, _price will match _sale_price, otherwise it will match _regular_price.

Variable products, and the way WooCommerce/Jigoshop implements them, are partly where the problems arise. This is actually true for other areas of WooCommerce too, like the shop archive, or their awful price filter but let’s stay focused.

In the image below you’ll see some custom metadata from one of my own variable products. This is from the ‘parent’ product. In this example, there are 3 product variations.

The above query is against the ‘parent’ product. WooCommerce stores products in wp_posts. I personally don’t think there’s anything wrong with this at all, although this is what has been identified as the performance bottleneck by many experts. I’ll show you further on why they’re talking crap and how WooCommerce could get great performance with the same exact table structure that already exists. Anyway, 3 product variations results in 4 entries in wp_posts – 1 entry for the parent, which is what you’re editing when you click ‘edit product’ and then 3 entries for each variation which is what you’re editing when you click the variations tab on the edit-product page.

Highlighted in yellow above are the product IDs for the variations. It’s important to remember that product variations are not listed in shop archives, nor would they be listed in the ‘products on sale’ shortcode. It’s the parent product that gets listed.

Now here comes the issue that WooCommerce solves in a truly awful way. How do you decide if a product is on sale?

Don’t worry – you don’t need a degree in Computer Science to decipher this stuff above. I’m going to explain it to you in English.

A product is considered to be on sale if it, or one of its variations, has a sale price matching its current price.

Remember earlier I told you there were 3 prices? _regular_price is like the RRP – it’s the one that gets scored out and replaced with the sale price. Then _sale_price is the sale price and _price is the current price.

So, their logic is that if the sale price equals the current price then the product is on sale.

Ok – so why so slow? There are a few reasons, some of which require a database expert to explain – luckily I am a database expert – but the first issue is how they try to solve performance problems.

What have WooCommerce done to improve performance of sale products?

The answer is really nothing. They will tell you caching. Fuck caching. Caching is not a solution for performance problems. Caching is a solution for traffic spikes. If it takes over a minute to populate the cache, your user is gone, your server got nuked, other innocent users trying to use your website probably left too as your poor wee server ground to a halt trying to populate the products_onsale cache. If you have a busy site with low stock levels, this caching is next to useless anyway.

Take a look at the row count returned by the query above – it’s the number in the 2nd last column. This is on my million product store. 367840 products, or their variants, are on sale. Then notice number 10 in column 2, this little function: get_on_sale_products()

What WooCommerce are doing is this horrible little design pattern they’ve used:

1. Fetch all of the product IDs for products that are on sale
2. Store them all in a variant (a variant is just a temporary storage space, by default it’ll be in your wp_options table)
3. Retrieve all Product IDs from variant
4. Pass all the product IDs to the WP_Query function and then sort and limit the results according to what was originally asked for.

So, even if your shortcode is only requesting 4 products on sale (the shortcode doesn’t even do pagination anyway!), it’ll still be grabbing every product on sale from your database and pass those IDs to the WordPress WP_Query function.

If you have some database skills of your own, you may be puking in your bucket at about this point. That’s the feeling I get every time I see crap like this:

The blue box highlight is showing you that this page used 300MB of RAM to generate the page. For comparison, normal pages on this site use 10MB and the /shop/ page uses 13MB.

If 300MB RAM is consumed generating a page, that will seriously limit how many pages can be generated simultaneously.

They implemented this pattern (order of events to solve this problem) to try and solve performance, so how about if we refresh the page with the products on sale?

That’s 2.6 seconds spent in MySQL, 5.2 seconds total, so another 2.6 seconds spent in PHP. This page is not running the full query, it’s retrieving the 300,000+ entries from the wp_options table and passing them to WP_Query. You can see RAM usage is lower too, so page load is definitely faster after the first page load but no one can argue this is a good result or a good solution. If your database is using 2.6s that means your CPU and disk are maxed out for the duration. You want your database time to be down below 0.3 seconds at most.

How to fix this awful WooCommerce performance

To fix this awful performance, we need to change the design pattern. We can’t go passing through every product ID to WP_Query and expect good performance. So, we’ll need to fix the design pattern and do the sorting and limiting EARLIER and actually use the database for WHAT IT’S GOOD AT!

Here’s the query from earlier to grab all the products on sale. There are 2 tables involved in the query – wp_posts and wp_postmeta. wp_postmeta is aliased twice, once as meta, then the 2nd time as meta2. The first alias is to get the _sale_price value while meta2 has the _price (current price).

I’m going to use this to create a new wp_postmeta key called ‘onsale’. This new key will have a value of 1 when a product is on sale.

INSERT INTO `wp_postmeta` (post_id, meta_key, meta_value)
SELECT post.ID as id, post.post_parent as parent_id
FROM `wp_posts` AS post 
LEFT JOIN `wp_postmeta` AS meta
ON post.ID = meta.post_id 
LEFT JOIN `wp_postmeta` AS meta2
ON post.ID = meta2.post_id 
WHERE post.post_type IN ( 'product', 'product_variation' ) 
AND post.post_status = 'publish' 
AND meta.meta_key = '_sale_price' 
AND meta2.meta_key = '_price' 
AND CAST( meta.meta_value AS DECIMAL ) >= 0 
AND CAST( meta.meta_value AS CHAR ) != '' 
AND CAST( meta.meta_value AS DECIMAL( 10, 2 ) ) = CAST( meta2.meta_value AS DECIMAL( 10, 2 ) ) 
GROUP BY post.ID;

By running the above command we have a new key/value pair in wp_postmeta which is going to make finding products on sale lightning fast. It will allow us to keep those 300,000+ product IDs in the database, not return them to PHP, not consume tons of RAM etc etc. It will be good. In fact, it’s such a good solution that it even means you can query posts using WP_Query which is fast by the way, unless abused like WooCommerce are doing here. With WP_Query, you can order products on sale by ‘best selling’ or ‘popular’ something which is not possible with the WooCommerce sale shortcode.

Before I do the WP_Query thing, here’s an example of the speed we’ll be seeing, directly from the database:

Ultra-fast products-on-sale

Here are the results – I have this working on my foundthru site and here is the Query Monitor analysis of the main get_posts query:

And here is the Query Monitor top bar:

Summary

It is possible to solve WooCommerce performance problems without creating new tables. It’s possible to retrieve products on sale far more quickly and far more efficiently than is currently being done.

I’ve bundled all of the above code into a free plugin for you to use. To do this more quickly, I forked this plugin by wp genie and added all the optimisations from above to make your sales pages run really fast.

Download the Super Speedy Sales Page for WooCommerce plugin for free.