Getting Datafeedr working FAST with 50,000 products in Woocommerce or more

There are a lot of people out there who love Datafeedr and want to use it to make vast stores containing 100s of 1000s of products. Unfortunately, many of them are running on sub-optimal shared hosting or poor VPS servers running Apache.

I’ve gone into detail elsewhere about getting WordPress set up to run FAST on Digital Ocean, but getting your VPS environment configured well is only one half of the performance battle. Below I go into the other areas you need to optimise to ensure your site loads hyper-fast – maximum 1 second per page, for ANY page. Really, we’re aiming for 200ms load time.

I always recommend people install W3 Total Cache – it’s fantastic for caching entire pages and really giving a great boost to your site.  You can go farther and install Varnish on top of this. This article, however, is for those people with sites so large it’s really impossible to pre-cache everything. It’s incredibly important your site loads fast for GoogleBot if you want to rank well in SEO, and relying on a cache for all these pages isn’t going to work.

Find your slowest WooCommerce pages

Firstly, you need to analyse your performance load. If you’ve already got 50,000 products loaded into woocommerce, then this is a great start for us to check your load. You probably already know which pages are the slowest culprits on your site, but if you’ve followed my server set up guide then the likeliest pages to be slow are:

• Store category pages which contain > 1,000 products
• Search results page

Find your slowest page then use the following analysis techniques to figure out your bottlenecks so we can fix them.

Install the Query Monitor plugin to uncover your slow queries

Query Monitor – this will allow us to load the slowest page and view the SQL queries which are taking the longest. This will allow us to narrow down and fix the performance problems.

Once you’ve installed the Query Monitor, load the slowest page you’ve got and you’ll see something like the following image on your WP admin bar:

In this example, when I click on Slow Queries (27) I see a variety of slow queries but one REALLY BAD culprit:

On this site, the WooCommerce Price Filter Widget is being called multiple times for some reason and use 0.2 seconds on every load. Look at the length of that query (it scrolls downwards for about 10 pages!) and is an incredibly inefficient query.

I’m going to remove the plugin and rerun the page to  see the overall page speed.

If you scroll down further on the page, past all the queries, you’ll find a summary for each plugin. In this case, I’ve found a plugin called Ewww Image Optimizer which is using about 5 seconds to look up its tables. That’s terrible.

You’ll notice above there is also a CORE call which is running slowly – WP_Query->get_posts(). I am expecting this query to speed up significantly once I remove the other bad culprits which are hogging disk, memory and CPU.

The EWWW Image Optimizer is supposed to SPEED UP your site, so it’s pretty appalling that it’s actually slowing it down significantly here. If you’re looking for a good image optimizer which doesn’t suck so badly at performance, I recommend Imsanity instead. Anyway, I removed the EWWW Image Optimizer and hey presto – pages are loading sub-second:

I’m a little disappointed that WP_Query->get_posts is still performing so badly. 0.77 seconds is terrible. Consider that I’m running this on a Digital Ocean server with 2GB RAM and SSD disks and that the page is only displaying 40 products, it really should be returning in under 0.1 second MAXIMUM.

Squeezing even more performance from WooCommerce

I’m going to have to get a little technical on this to figure out why this performance is so bad.

I need to copy the query and load it into mysql to analyse the query. Firstly, I copy the query from the Query Monitor – I’ve got two examples:

Slow WooCommerce query when ordering by price

This query takes 0.77 seconds on the Digital Ocean server I’ve set up for this optimisation job.

SELECT SQL_CALC_FOUND_ROWS wp_posts.ID
FROM wp_posts
INNER JOIN wp_postmeta
ON (wp_posts.ID = wp_postmeta.post_id)
INNER JOIN wp_postmeta AS mt1
ON (wp_posts.ID = mt1.post_id)
WHERE 1=1
AND wp_posts.post_type = 'product'
AND (wp_posts.post_status = 'publish'
OR wp_posts.post_status = 'private')
AND (wp_postmeta.meta_key = '_price'
AND (mt1.meta_key = '_visibility'
AND CAST(mt1.meta_value AS CHAR) IN ('visible','catalog')) )
GROUP BY wp_posts.ID
ORDER BY wp_postmeta.meta_value+0 DESC
LIMIT 0, 40

Slow WooCommerce query when using default ordering

This query takes 0.27 seconds (still far too long):

SELECT SQL_CALC_FOUND_ROWS wp_posts.ID
FROM wp_posts
INNER JOIN wp_postmeta
ON (wp_posts.ID = wp_postmeta.post_id)
WHERE 1=1
AND wp_posts.post_type = 'product'
AND (wp_posts.post_status = 'publish'
OR wp_posts.post_status = 'private')
AND ( (wp_postmeta.meta_key = '_visibility'
AND CAST(wp_postmeta.meta_value AS CHAR) IN ('visible','catalog')) )
GROUP BY wp_posts.ID
ORDER BY wp_posts.menu_order ASC, wp_posts.post_title ASC
LIMIT 0, 40

Analyse your WooCommerce queries using MySQL Explain

Load up your SSH client (Putty) and connect to your server. If you have phpMyAdmin, you can use that instead if you wish. Open up a mysql session, change to your database and then enter the keyword EXPLAIN followed by your copied query:

show databases; # list your databases so you can see which one you need to connect to
use databasename; # switch to the relevant database
explain insertyoursqlqueryhere; # remember to add the semi-colon at the end

What I found was that there were a couple of indexes I could add to the wp_posts and wp_postmeta table which will speed up the query. Unfortunately, it doesn’t transform the query to blazingly fast but it does improve things by about 50% for this amount of products. Here are the two indexes I added:

alter table wp_posts add index woocommercespeed (post_type, post_status, id);
alter table wp_postmeta add index woocommercespeed1 (post_id, meta_key, meta_value);

These are covering indexes and in a better order than the default indexes already on those two tables.

Still, when I re-run my explain command I get the following output:

mysql> explain SELECT SQL_CALC_FOUND_ROWS wp_posts.ID FROM wp_posts INNER JOIN wp_postmeta ON (wp_posts.ID = wp_postmeta.post_id) INNER JOIN wp_postmeta AS mt1 ON (wp_posts.ID = mt1.post_id) WHERE 1=1 AND wp_posts.post_type = 'product' AND (wp_posts.post_status = 'publish' OR wp_posts.post_status = 'private') AND (wp_postmeta.meta_key = '_price' AND (mt1.meta_key = '_visibility' AND CAST(mt1.meta_value AS CHAR) IN ('visible','catalog')) ) GROUP BY wp_posts.ID ORDER BY wp_postmeta.meta_value DESC LIMIT 0, 40;
+----+-------------+-------------+-------+-------------------------------------------+-------------------+---------+-----------------------------+-------+-----------------------------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------------+-------+-------------------------------------------+-------------------+---------+-----------------------------+-------+-----------------------------------------------------------+
| 1 | SIMPLE | wp_posts | range | PRIMARY,type_status_date,woocommercespeed | type_status_date | 124 | NULL | 11160 | Using where; Using index; Using temporary; Using filesort |
| 1 | SIMPLE | mt1 | ref | post_id,meta_key,woocommercespeed1 | woocommercespeed1 | 776 | annabrody.wp_posts.ID,const | 1 | Using where |
| 1 | SIMPLE | wp_postmeta | ref | post_id,meta_key,woocommercespeed1 | woocommercespeed1 | 776 | annabrody.mt1.post_id,const | 1 | Using where |
+----+-------------+-------------+-------+-------------------------------------------+-------------------+---------+-----------------------------+-------+-----------------------------------------------------------+
3 rows in set (0.01 sec)

mysql>

The bad part of this query is the part where it loads 11160 rows from wp_posts. Unfortunately, because it’s sorting based on the meta_value column from wp_postmeta then as the query stands it needs to load all posts.

Instead of trying to rewrite the underlying query, I decided to backtrack a little. Showing *all* products and allowing users to sort *all* products by price is probably a little crazy. Instead, they’re going to want to sort their search results or a specific category by price, but not all products all mixed together.

So, if you’re currently showing all products on your /shop/ or /store/ page in WooCommerce, go back into your WooCommerce settings and switch to showing ‘subcategories’:

Now, this leaves the categories being displayed on the shop home page along with the product counts. The only thing left for my client to do is to create images for each of her product categories and she’ll be left with a blazingly super fast WooCommerce site.

Finally, set up W3 Total Cache and Cloudflare

I always recommend to install caching LAST. Caching can hide a lot of performance problems your site is having and these problems may be appearing only to the likes of Googlebot which typically will access pages that are not cached.

The simplest set up for W3 Total Cache is to install it and only enable page caching. I’ve found this runs incredibly well. You may optionally install Cloudflare too – they are offering a free CDN, so why wouldn’t you? Get your Cloudflare account set up, record your username and API key, go to W3 Total Cache Add Ons, install the free Cloudflare add on, then go to General Settings, scroll down to Cloudflare and configure your username and API key. Choose which options you want – I’ve found Cloudflare to be very resilient, so go for the maximum performanace on every option and your site will be flying.

Depending on how many products you have, you *may* wish to enable the W3 Total Cache pre-baking option but I wouldn’t really recommend it. If you have followed everything above, your site will be lightning fast, light on memory and capable of handling 1000s of simultaneous users at typically 200ms – 500ms speed.

If anyone needs any more specialized help with making WooCommerce and Datafeedr run super fast, leave comments below or get in touch.

• Author
• Recent Posts

Chat to me

Dave Hilditch

Founder at Affiliate Web Designers

Dave has been programming since 6 years old and has been developing WordPress plugins, themes and websites since 2010. In the past he built the browse view technology for Skyscanner and now he helps clients with interesting website challenges.

He is always on at least one of his computers when he's awake, so get in touch and he'll get right back to you.

Chat to me

Latest posts by Dave Hilditch (see all)

• My WordPress performance plugins and server stack have moved - July 31, 2016
• Price Comparison Pro 1.2 Released - July 5, 2016
• How to run backups on huge WordPress websites without your website being brought offline - February 4, 2016