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
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 ▶