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.
- 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
November 21, 2014 @ 7:01 pm
Nice post, Dave! Very helpful. I will be pointing many folks to this article. If you write any more tips and tricks regarding optimizing WordPress and WooCommerce, let me know so I can send our members to those articles, too. 🙂
Eric
November 22, 2014 @ 6:58 pm
There is a related article for getting your VPS set up and configured with optimal performance using Nginx (rather than Apache which is total crap). I prefer Digital Ocean these days for VPS – they set up a new VPS in 55 seconds and use SSD etc. The client whose site I migrated and performance boosted for this article was getting page load speeds up over 60 seconds before I migrated her. After migration, before the above performance fixes, performance was far better – about 2 – 5 seconds per page – i.e. about 20 – 30 times faster than her previous host without any code changes.
You can find that other article here – http://www.affiliatewebdesigners.com/2014/11/20/setting-digital-ocean-wordpress/
There’s also a guide to setting up a real cron job to make product set imports faster and more reliable – http://www.affiliatewebdesigners.com/2014/11/22/set-real-cron-job-datafeedr-woocommerce-product-sets/
November 21, 2014 @ 7:08 pm
One last thing to add regarding W3 Total Cache, do not enable Object Cache. That bypasses the WordPress Transients API which the Datafeedr plugins rely on for storing data to reduce the number of API requests required to build and update your products.
November 22, 2014 @ 6:53 pm
Thanks for that Eric. I’ve found problems in the past when using W3 Total Cache object cache or database cache. My recommendation for anyone looking for maximum performance would be to use W3 Total Cache for *page caching* and install the Cloudflare add on for W3 Total Cache and use Cloudflare to handle JS/CSS minification, compression and browser caching settings.
May 13, 2015 @ 3:32 pm
when i run this SQL command.
alter table wp_postmeta add index woocommercespeed1 (post_id, meta_key, meta_value);
I get this error
Error
SQL query:
alter table qmdz_postmeta add index woocommercespeed1 (post_id, meta_key, meta_value)
MySQL said: Documentation
#1170 – BLOB/TEXT column ‘meta_value’ used in key specification without a key length
Can you help?
May 13, 2015 @ 3:48 pm
Hi – this can happen.
Change the command to:
alter table wp_postmeta add index woocommercespeed1 (post_id, meta_key, meta_value(20));
August 22, 2016 @ 12:49 am
Tried this, but then I get this error:
Error Code: 1071
Specified key was too long; max key length is 1000 bytes
I even tried setting it to (1) instead of (20) – still get the same error.
Any ideas how to fix that?
Thanks.
February 19, 2017 @ 6:08 pm
You are using mysql 5.5 – upgrade to at least 5.6 or better 5.7 or even better get MariaDB or Percona DB – I talk about this more over on http://www.wpintense.com
May 23, 2015 @ 11:04 pm
Hi Dave,
Do you still offer optimization services?
Thanks,
Dave
May 27, 2015 @ 3:06 pm
Yes we do – use the contact page or the request a quote page to get in touch for pricing. Please provide as much info as you can.
December 29, 2015 @ 1:50 pm
my site s hosted on vps server and wpallimport takes for ever to process 50 k records = 5k products . it takes about 20 hrs to process this little . thanks for your optimization service in advance. take care and waiting your reply
January 5, 2016 @ 3:47 pm
Hi – if you have 50k records, 5k products this presumably means an average of 10 variations per product?
Do you have a lot of metadata for the products too?
How frequently are you processing the 50k records? Are you on the mercury stack or plain old Apache?
November 28, 2016 @ 10:21 pm
Hi, I have a problem with woocommerce. I need about 3500-4000 product categories and admin products menu is very very slowly then. And I am testing now without plugin, themes or codes. And I am now about 2000 categories and again admin products menu slowly. And when I opened about 4000 categories I know admin products menu will be open very very slowly.
How can improve product categories queries ??? Really I need that very much ! I try to do a new multivendor website with WC vendors. But I can not still do it because of slow queries problem.
I hope you have an good idea !
Thank you so much!
February 19, 2017 @ 6:03 pm
Get my Scalability Pro plugin – with this many categories you will DEFINITELY need it – table scans are occuring and you can’t scale with table scans – Scalability Pro fixes this
March 1, 2017 @ 9:32 pm
Hi Dave,
Perfect article! Very nice read.
I have a problem with the price filter on my website and I am not sure if I need to scale up. I only have about 2000+ products in the shop. I will try using your analysis tools and see if this can help me. Thanks!
I have turned the price filter off now because using this filter will cause my website to go down.
When I look into PHPMyAdmin I can see the following notification which is holding up all other querys:
###START###
SELECT SQL_CALC_FOUND_ROWS wp_posts.ID FROM wp_posts LEFT JOIN wp_postmeta ON ( wp_posts.ID = wp_p
###END###
George
March 1, 2017 @ 11:22 pm
That’ll be doing a count of products – you should check out my Scalability Pro plugin – it has fast replacement woocommerce widgets that will drop in and inherit your existing styling. And they’re FAST!