Fixing Slow Woocommerce
Really Slow Woocommerce?
Suffering from having thousands of Woocommerce orders with millions of meta records? Read this article on how a crawling slow Woccommerce installation was tuned to be very fast:
Order search is now a magnitude faster: from previously 80 secs to less than 1 sec., listing 20-40 orders was taking 20+ seconds, opening an order detail screen up to 80 seconds!
After these adjustments Woocommerce order search and opening an order detail screen are now a magnitude faster.
How Much Faster Is It Now?
Faster to search for an order
Faster to open an order detail screen
| Use Case | Before | After | % Faster |
|---|---|---|---|
| Order List Screen | 24+ secs | less than 1 second | 2300% |
| Order Detail | 32+ secs | 0.64 seconds | 4900% |
| Search by email | 80+ secs | less than 1 second | 7900% |
| Search by name | 29+ secs | less than 1 second | 2800% |
| Search by order# | 26+ secs | less than 1 second | 2500% |
Follow These 3 Steps to Fix a Slow Woocommerce Installation:
1. Fix mySQL database
Somewhere along the WordPress upgrade process from 3.x to 4.x to 5.x this installation started to suffer from a major WooCommerce slowdown when listing orders, searching orders and opening an order detail screen:
Updated June 2021: WordPress version: 5.7.2, WooCommerce version: 5.4.1, UTf-8, InnoDB tables
A – Use SqlMonitor to find the slow search
B – Run EXPLAIN on the slow query in mySql WorkBench
We discovered that no index was used, even though the meta_key field has an index assigned.
Take advantage of fast indexing and not slow sequential search:
The culprit was found in the wp_postmeta table and is discussed in more detail at WordPress Core
The meta_key field was a VARCHAR(255) and being too long in size it did exceed it’s maximum index length of 767 bytes and therefore did not use the index. Let’s check how much space we are actually utilizing from this VARCHAR(255) field.
The result returns ’49’. So the longest value of all the ‘7,014,698’ meta records we currently use is only 49 chars, not even close to the 255 chars the field was assigned to.
Get this fixed for me ▶
This installation had at the time 62K Woocommerce orders with ‘7,014,698’ related wp_postmeta records. Let’s bring the field size down to a VARCHAR(191) to stay within the index limit: (It is smart to backup your table or db first.)
Let’s check the query after adjusting the field size:
Result: 0.025 sec – Very Fast!
No Time? Too busy? Ask a specialist for help ▶
2 – Limit Fields to Search in
Here is one more improvement to speed up Woocommerce without needing full root access to your server. Limiting the quantity of fields to search for to the most relevant ones:
/**
* Limit woocommerce order search fields.
*/
function custom_woocommerce_shop_order_search_fields( $search_fields ) {
// error_log( 'currently searching in these order fields: ' . print_r($search_fields, true) );
unset( $search_fields );
$search_fields[] = '_meta_key_one';
$search_fields[] = '_meta_key_two';
$search_fields[] = '_meta_key_three';
// error_log( 'now only searching in these order fields: ' . print_r($search_fields, true) );
return $search_fields;
}
add_filter( 'woocommerce_shop_order_search_fields', 'custom_woocommerce_shop_order_search_fields' );
3 – Implement Elastic Search
Here are some extra improvements to speed up Woocommerce. If you have root access to your web-hosting account download and install Elastic Search Server, then the Elastic Press Wordpress plugin and WC-CLI
Once the Elastic Search Server has been installed we can either utilize the “ElasticPress WooCommerce” plugin to hand over all critical WooCommerce sql queries to Elasticsearch instead of MySQL or write our own.
4 – General WooCommerce Speed Improvements
PHP, WooCommerce core and plugin performance tracing is an essential method to find and establish causality in bottlenecks. We have achieved dramatic speed improvements when adding Application Performance Management tools to reveal application performance issues. We can pinpoint and fix errors in Database, Dispatcher, PHP, View & External Code areas.
5 – Scaling WooCommerce
A slow WooCommerce installation will exhibit scaling issues and system crashes under traffic spikes and incremental load increases. Read how to scale WooCommerce when traffic reaches the breaking points of your e-commerce system.
6 – How does Specto Design fix WooCommerce Speed Issues?
Specto Design’s WooCommerce team uses a set of custom built tools to accurately analyze and detect problems and errors in the PHP layer, the plugins installed and analyze slow SQL queries by any of the active components. Over the last 10 years Specto Design has analyzed the majority of their clients mission critical WooCommerce product websites and are now able to pinpoint the most common areas where WooCommerce slow-downs occur. This experience and the use of custom analysis tools lets Specto Design quickly and reliably profile problem zones and fix speed issues.
Easy Fix: Find out about the WooCommerce Admin Order Search Plugin7 – Do you Provide a Service to fix WooCommerce Speed Issues?
Specto Design’s provides a customized, individual service to improve WooCommerce search and scaling issues for your installation. We have helped many very recognizable names in online retail to dramatically improve their situations.
