Question 291558 in Wordpress Question posted in
The official Wordpress documentation can be found here.

WordPress – Slow custom order search SQL query in WooCommerce

On a website with roughly 10k orders, our order search takes on average 20 seconds to complete while searching for order number. This is the sql-query that is slow

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

Is there any change I could make to this query to speed it up?

I tried cleaning up the database with a number of plugins, turning off most plugins, but query monitor leads me to this specific query every time as the culprit

Tags:

2

Answers


  1. 0

    Your SQL query is very heavy as it searches for an order number using "LIKE" and "%ordernumber%" on 5 different meta fields without restricting the search to "shop_order" post_type and to some specific(s) post_status. So the query is extended to all posts, pages and other custom post types as product, product_variations, shop_coupon, shop_orders and so on…

    If you don’t use any custom code or any plugin that handle a sequential order number, in that case the Order ID is the "ID", so you should search for the ID for shop_order post type using the following lightweight query:

    SELECT p.ID FROM nb_posts p
WHERE p.post_type = 'shop_order' AND p.ID = '%s'

    where '%s' is the placeholder for the order number to search.

    If you don’t use some custom code or some plugin that handle a sequential order number (different from the order ID), in that case there is a specific custom field that handles this sequential order number. You need to first find the related meta_key in nb_postmeta database table.

    There are all this order number related threads on stack overflow that sometimes uses a custom field, like in this one where the meta_key used is _order_number.
    Then your lightweight SQL query will be:

    SELECT pm.post_id FROM nb_postmeta pm
LEFT JOIN nb_posts p ON pm.post_id = p.ID
WHERE p.post_type = 'shop_order' 
AND pm.meta_key = '_order_number' AND pm.meta_value = '%s'

    where '%s' is the placeholder for the order number to search.

    Login or Signup to reply.
  2. 0

    Make sure meta_key and meta_value columns are indexed. If you don’t already have a clustered index, then adding a clustered index on meta_key might help. Although, a clustered index could slow down other queries.

    @LoicTheAztec is correct that the LIKE is probably slowing things down a lot. It would help if you could get rid of the first % – LIKE ‘ordernumber%’ – especially if you add an index to meta_value. Make sure the query isn’t being run every time someone types a letter in the order number.

    The in() is probably slowing things down. If the list of items in the in() are fixed, it would be faster to create a table named something like nb_postmeta_search_meta_keys that has a single row for each value (‘_billing_address_index’,’_shipping_address_index’,’_billing_last_name’,’_billing_email’,’_billing_phone’). Add a unique, clustered index to that field and join nb_postmeta to the new table.

    The DISTINCT might be slowing things down. You could try switching to a GROUP BY:

    SELECT p1.post_id
    FROM nb_postmeta p1
    WHERE p1.meta_key IN (‘_billing_address_index’,’_shipping_address_index’,’_billing_last_name’,’_billing_email’,’_billing_phone’)
    GROUP BY p1.post_id, p1.meta_value
    HAVING p1.meta_value LIKE ‘%ordernumber%’

    Moving the LIKE to HAVING should speed things up since the WHERE will be applied first. I don’t know the data so I’m not sure if it will give you the results you need. (I don’t know anything about WooCommerce). You might not have to do all of these things. Do the easiest things first and see how much it helps. A reasonably powered db server should be able to return results on 10k records very quickly if the SQL is optimized.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search