Faster wp-admin meta search

There is a flaw in the default search functionality in wp-admin that could be argued is a feature.

When searches happen against wp_postmeta, such as when using the search field in WooCommerce->Orders, the LIKE operator is used, like this:

SELECT DISTINCT p1.post_id
FROM _tkpostmeta p1
WHERE p1.meta_value LIKE '%0752167787%'
AND p1.meta_key IN ('_billing_address_index','_shipping_address_index','_billing_last_name','_billing_email')

Notice how the LIKE ‘%…%’ has a leading % sign.

This prevents the use of any index we have on meta_value. To understand this, imagine we have 10,000 people in an index, ordered by their last name.

If we were to search against this index for something like ‘Hild%’ then the database can go straight to the pages where the keys start with HILD and then scan the few remaining rows. That’s very fast.

On the other hand, if we ask the question: LIKE ‘%hild%’ then the database HAS to check every single row in the database since we do not know any of the starting characters. That causes a full table or index scan and results in a lot of disk, RAM and CPU usages, as well as some of the caches being flushed by all this data.

The reason this flaw could be considered a feature is that the LIKE operator works well at finding text matches inside other text, or partial matches. So – if you search for only part of the phone number, with the LIKE operator, those rows will be found whereas with the MATCH operator they will not be found.

New optimisation added to Super Speedy Search

Super Speedy Search has mostly been focused on improving user search speed – that’s free text search from your front-end pages. It does this using a fulltext index and by altering LIKE statements to their faster MATCH statement equivalents.

In this case, we can repeat that approach – the new version includes creating an index on wp_postmeta against the meta_value and then alters these types of queries to use the match operator.

So, those queries against your 1000s of orders will now behave like this:

SELECT DISTINCT p1.post_id
FROM _tkpostmeta p1
WHERE match(p1.meta_value) against('searchstring')
AND p1.meta_key IN ('_billing_address_index','_shipping_address_index','_billing_last_name','_billing_email')

Summary

If you have 1000s of orders, and if you regularly search against these orders, you may have started noticing your searches getting slower and slower. This will only get worse over time, as your orders grow in number, and the LIKE operator gets slower and slower as it has to check every row every time.

Using Super Speedy Search will add the fulltext index to postmeta and alter any LIKE operators against wp_postmeta, changing them to the faster MATCH operator.