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
WHERE meta_key NOT BETWEEN '_'
HAVING meta_key NOT LIKE '\_%'
ORDER BY meta_key
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!