Skip to content

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:

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:

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')


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.

Did this article answer your question?

Leave a Reply

Your email address will not be published.

Come chat to me on our Discord server to ask questions - Dave Hilditch, WP Intense

We use cookies

We use cookies for various things on our site, including our on-site chat bubble (if you use it), our comment forms (if you use them) and for session handling (if you log in).

Other than that, we use cookies to identify where traffic came from to help us understand which traffic turns into sales, we use a cookie for Google Analytics traffic analysis and we use a cookie to customise adverts for our own products we think you'll be interested in.

You can read more detail in our privacy policy page. Please click 'Accept' or 'Decline' to continue.