Optimizing WordPress and WooCommerce for thousands of products

Even though WooCommerce is immensely popular it struggles when you add thousands of products. Here is how we optimize for our customers.

We recently had a customer who plonked 85,000 products into their database (thanks woo commerce import pro !).

Our first observation, the pages were slow, starting at about 1.4 seconds per page. Then Googlebot came past, and things got slower. And as the days went on the pages got slower and slower to eventually nearly 2 seconds. At this stage, the site was eating up all the server CPU in PHP and MySQL.

After optimization, this is a graph of the optimized WordPress hosting server, showing an average of 0.4 seconds per page:

First investigations showed 500k entries in wp_options. This cleared about 380K , leaving 120K.

wp-cli transient delete-expired

If you don’t have access to wp-cli, there is a plugin that will clear those transients. This recovered the speed to the original 1.4 seconds.

Moving the site to PHP-fpm 7.0 dropped the performance to a better 0.8 seconds. Whilst this was reasonable, and PHP CPU usage dropped significantly , MySQL was still being hammered.

Adding this fix, dropped the MySQL execution time dramatically, and the MySQL CPU usage.


CREATE INDEX autoload ON wp_options(autoload, option_name);


what this does is puts an index on the options table. Generally, the options table is mostly loaded into memory at the start of each page. But with WooCommerce scribbling in the options table, there’s still over 100k entries in there, only 267 are ‘autoload’ options. So it’s like looking for a needle in the haystack – but an index on the table fixes that. This dropped the performance of the page down to around 0.45 seconds – but MySQL was still a problem – it was still being hammered.

By adding ‘redis object cache’ WordPress plugin, we dropped the MySQL level down again. And the page load time is down to 0.35 seconds.

The rest of the time seems to be absorbed in the WordPress taxonomy. There are a few hundred thousand entries in there as well – but that seems more trouble to optimize.

By Scott Farrell on August 29th, 2016 , Follow @scott_WordPress Tweet to @scott_WordPress