Slow query while searching a product woocommerce

I was struggling with this issue since long with my wordpress with wooocommerce website. Whenever i was doing a search in admin dashboard for wooommerce products it was taking long time and end up with no results.

SELECT DISTINCT posts.ID as product_id, posts.post_parent as parent_id
    FROM wp_posts posts 
         LEFT JOIN wp_postmeta postmeta ON posts.ID = postmeta.post_id 
    WHERE ( posts.post_title LIKE '%Samsung%' 
            OR posts.post_excerpt LIKE '%Samsung%' 
            OR posts.post_content LIKE '%Samsung%' 
            OR ( postmeta.meta_key = '_sku'
                 AND postmeta.meta_value LIKE '%Samsung%' ) ) 
          AND posts.post_type IN ('product','product_variation') 
    ORDER BY posts.post_parent ASC, posts.post_title ASC

After months of troubleshooting i am still not able to fully resolve the issue, however with this workaround i am finally somewhat relived.

To resolve this issue…

Edit this file – public_html/wp-content/plugins/woocommerce/includes/data-stores/class-wc-product-data-store-cpt.php

In Woocommerce version 3.5.4 look for search_products function

On line no. 1432 look for

$term_group_query .= $wpdb->prepare( ” {$searchand} ( ( posts.post_title LIKE %s) OR ( posts.post_excerpt LIKE %s) OR ( posts.post_content LIKE %s ) OR ( postmeta.meta_key = ‘_sku’ AND postmeta.meta_value LIKE %s ) )”, $like, $like, $like, $like ); // @codingStandardsIgnoreLine.

Remove following code

OR ( postmeta.meta_key = ‘_sku’ AND postmeta.meta_value LIKE %s )

It should then look like this…

$term_group_query .= $wpdb->prepare( ” {$searchand} ( ( posts.post_title LIKE %s) OR ( posts.post_excerpt LIKE %s) OR ( posts.post_content LIKE %s ) )”, $like, $like, $like, $like ); // @codingStandardsIgnoreLine.

Save the file and you should be able to search for products now. The downside with this is you’ll not be able to search SKU’s. But i was ok with this as dont have SKU’s added to my products.

I hope this will help you.