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

Improve SQL Query WordPress when search product by SKU or name

I’m experiencing significant performance issues when searching for products by name or SKU in the WordPress WooCommerce admin. With around 40k products, the search takes between 16 to 50 seconds, which is quite slow. Furthermore, sometimes this delay results in a 504 error.

I’ve looked for solutions online, browsed forums, and even tried with chatbots like ChatGPT, but haven’t found a definitive solution.

Using the Query Monitor plugin, I identified a particular query that seems to be the main culprit:

SELECT DISTINCT posts.ID as product_id, posts.post_parent as parent_id
FROM wpprefix_posts posts
LEFT JOIN wpprefix_wc_product_meta_lookup wc_product_meta_lookup
ON posts.ID = wc_product_meta_lookup.product_id
LEFT JOIN wpprefix_wc_product_meta_lookup parent_wc_product_meta_lookup
ON posts.post_type = 'product_variation'
AND parent_wc_product_meta_lookup.product_id = posts.post_parent
WHERE posts.post_type IN ('product','product_variation')
AND ( ( ( posts.post_title LIKE '%DELETEME%')
OR ( posts.post_excerpt LIKE '%DELETEME%')
OR ( posts.post_content LIKE '%DELETEME%' )
OR ( wc_product_meta_lookup.sku LIKE '%DELETEME%' )
OR ( wc_product_meta_lookup.sku = ''
AND parent_wc_product_meta_lookup.sku LIKE '%DELETEME%' ) ))
ORDER BY posts.post_parent ASC, posts.post_title ASC

For our use-case, we only need to search products based on their name (title) and SKU. I’m looking to optimize this search, particularly within the WooCommerce admin, given the large number of products we handle.

Additionally, I’ve observed errors in my server logs such as:

upstream timed out unknown error while reading response header from upstream "/wp-admin/edit.php?s=DELETEME..."

I’ve also noticed a few other "unknown error while reading response header from upstream" for other endpoints like GET /wp-json/.

Furthermore, when users try searching for products on the frontend, it relies on admin-ajax.php, but this too isn’t functioning as expected.

We have large description in each product it is html code meybe that causes slow search?

Despite increasing resources on my VPS setup, these issues persist. Could someone guide me on how to write or adjust code for my theme’s functions.php to enhance this search query’s performance and tackle these errors? Any help or direction would be greatly appreciated!

Tags:

2

Answers


  1. 0

    The multiple LIKEs with OR operators are likely to slow down the performance of your query, especially if the columns have long text content.

    AND 
( 
(posts.post_title LIKE '%DELETEME%')
OR (posts.post_excerpt LIKE '%DELETEME%')
OR (posts.post_content LIKE '%DELETEME%')
OR (wc_product_meta_lookup.sku LIKE '%DELETEME%')
OR (wc_product_meta_lookup.sku = ''
AND parent_wc_product_meta_lookup.sku LIKE '%DELETEME%') 
)

    Assuming that your database is MySQL, you could use a FULLTEXT index like this on the wpprefix_posts table:

    FULLTEXT (post_title, post_excerpt, post_content)

    and then use the function

    MATCH (post_title, post_excerpt, post_content)

    for searching in place of %LIKEs and ORs.

    (see Natural Language Full-Text Searches)

    And same for the table wpprefix_wc_product_meta_lookup.

    Let us know if this helped!

    Login or Signup to reply.
  2. 0

    SELECT DISTINCT p.ID as product_id, p.post_parent as parent_id
    FROM wpprefix_posts p
    LEFT JOIN wpprefix_wc_product_meta_lookup sku_lookup ON p.ID = sku_lookup.product_id
    WHERE p.post_type IN (‘product’, ‘product_variation’)
    AND (
    p.post_title LIKE ‘%DELETEME%’
    OR sku_lookup.sku LIKE ‘%DELETEME%’
    )
    ORDER BY p.post_parent ASC, p.post_title ASC;

    can you try that i have combined the conditions for searching product names (post_title) and SKUs (sku) using the OR operator,
    i have also maintained the DISTINCT keyword to ensure that only unique product IDs are returned.
    and kept the ORDER BY clause for ordering the results

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

Back To Top
Search