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?
2500%
Faster to search for an order
4900%
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 this installation started to suffer from a major Woocommerce slowdown when listing orders, searching orders and opening an order detail screen:
WordPress version: 4.4.2, Woocommerce version: 2.5.2, UTf-8, InnoDB tables
A – Use SqlMonitor to find the slow search
SELECT DISTINCT meta_key
FROM wp_postmeta
WHERE meta_key NOT BETWEEN '_'
AND '_z'
HAVING meta_key NOT LIKE '\_%'
ORDER BY meta_key
LIMIT 30
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.
SELECT MAX(LENGTH(meta_key)) FROM wp_postmeta;
which 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.
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.)
ALTER TABLE wp_postmeta MODIFY meta_key varchar(191);
Let’s check the query after adjusting the field size:
SIMPLE, wp_postmeta, range, meta_key, meta_key, 576, , 2, Using where; Using index for group-by
EXPLAIN SELECT DISTINCT meta_key FROM wp_postmeta WHERE meta_key NOT BETWEEN '_' AND '_z' HAVING meta_key NOT LIKE '\_%' ORDER BY meta_key LIMIT 30 1 row(s) returned 0.025 sec / 0.000034 sec
Result: 0.025 sec – Very Fast!
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[] = '_order_key';
$search_fields[] = '_billing_company';
$search_fields[] = '_billing_email';
// 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.
