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.4.6 look for search_products function

On line no. 1397 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.

Demo and Download 20 Best Free Premium Magazine WordPress Themes!

Channel

Channel Magazine WordPress Theme

Demo | Download Page


  • Aristo

    Aristo Magazine WordPress Theme

    Demo | Download Page


  • Falcon

    Falcon Magazine WordPress Theme

    Demo | Download Page


  • Scarlett

    SCARLETT Magazine WordPress Theme

    Demo | Download Page


  • The Web News

    The Web News Magazine WordPress Theme

    Demo | Download Page


  • Render Magazine

    Render Magazine WordPress Theme

    Demo | Download Page


  • MegaNews

    MegaNews Magazine WordPress Theme

    Demo | Download Page


  • Wise Business

    Wise Business Magazine WordPress Theme

    Demo | Download Page
    (Also include other free premium themes)


  • The Original Premium News

    The Original Premium News Magazine WordPress Theme

    Demo | Download Page
    (Other free themes from WooThemes here)


  • Wire News

    WireNews Magazine WordPress Theme

    Demo | Download Page


  • Blue News

    Blue News Magazine WordPress Theme

    Demo | Download Page


  • Masigasig Black

    Masigasig Black Magazine WordPress Theme

    Demo | Download Page
    (Also available in other colors)


  • ZinePress

    ZinePress Magazine WordPress Theme

    Demo | Download Page


  • Small Magazine

    Small Magazine Magazine WordPress Theme

    Demo | Download Page


  • WPBoxedTech

    WPBoxedTech Magazine WordPress Theme

    Demo | Download Page


  • Simplex

    Simplex Magazine WordPress Theme

    Demo | Download Page


  • NovaScotia

    NovaScotia Magazine WordPress Theme

    Demo | Download Page


  • StudioPress

    StudioPress Magazine WordPress Theme

    Demo | Download Page
    (Also available in other colors)


  • Arthemia Free

    Arthemia Free Magazine WordPress Theme

    Demo | Download Page


  • FREEmium

    FREEmium Magazine WordPress Theme

    Demo | Download Page


  • How to Import / Syndicate WordPress blog posts to Blogger.com (Blogspot.com)

    Blogger does not currently support importing a wordpress blog, but you can still do it easly:

    1. Login to your WordPress account and navigate to the Dashboard for the blog that you’d like to transfer to Blogger. Click on the Tools tab > Export link, then download the WordPress WXR export file by clicking on Download Export File and save this file to your local machine.

    2. Go to WordPress2Blogger, browse to that saved document and click Convert and save that file to your local machine. In this file will be the contents of your posts/comments from WordPress in a Blogger export file.

    3. Then from your dashboard: Settings > Other tab, Blog Tools and click “Import” to import the converted file.

    Weblog Tools Collection: WordPress 3.1 Released

    WordPress 3.1 has been released.
    This release features a lightning fast redesigned linking workflow which makes it easy to link to your existing posts and pages, an admin bar so you’re never more than a click away from your most-used dashboard pages,…

    WordPress 3.1 has been released.

    This release features a lightning fast redesigned linking workflow which makes it easy to link to your existing posts and pages, an admin bar so you’re never more than a click away from your most-used dashboard pages, a streamlined writing interface that hides many of the seldom-used panels by default to create a simpler and less intimidating writing experience for new bloggers (visit Screen Options in the top right to get old panels back), and a refreshed blue admin scheme available for selection under your personal options.

    All together, this release fixes more than 820 issues.

    Users can upgrade automatically via their Dashboard or use the manual instructions. As always, please contact the WordPress Support Forums if you run into any trouble.

    Now that WordPress 3.1 has been released, the core developers plan 3.2  to be a shorter development cycle,  ”and include some fun improvements around plugins and the speed of the admin.”

    Have you upgraded to WordPress 3.1 yet? What do you think of the new features?

    BuddyPress: BuddyPress 1.2.8

    Our next item up for bid is BuddyPress 1.2.8, which includes a few small bug fixes to the 1.2 branch of code and is a highly recommended update if you are updating to WordPress 3.1.
    This release backports a few important fixes from the currently in-dev…

    Our next item up for bid is BuddyPress 1.2.8, which includes a few small bug fixes to the 1.2 branch of code and is a highly recommended update if you are updating to WordPress 3.1.

    This release backports a few important fixes from the currently in-development 1.3 version of BuddyPress to play nicely with the new and very much improved WordPress network administration interface. We’ve also updated the bundled version of bbPress from 1.0.2 to 1.0.3, which addresses a few issues that do not effect BuddyPress directly.

    Every effort has been put into supporting both WordPress 3.0 and 3.1 with this release to make updating your sites as painless as possible, so this should be another worry-free update!