0

When I search on my wordpress (woocommerce) it takes 50 seconds to load. According to Query Monitor the problem is this query.

SELECT wp_posts.ID
FROM wp_posts
LEFT JOIN wp_postmeta ON (wp_posts.ID = wp_postmeta.post_id)
LEFT JOIN wp_postmeta AS mt1 ON (wp_posts.ID = mt1.post_id AND mt1.meta_key = '_stock_status')
LEFT JOIN wp_postmeta AS mt2 ON (wp_posts.ID = mt2.post_id)
LEFT JOIN wp_postmeta AS mt3 ON (wp_posts.ID = mt3.post_id AND mt3.meta_key = '_lottery_closed')
LEFT JOIN wp_postmeta AS mt4 ON (wp_posts.ID = mt4.post_id AND mt4.meta_key = '_auction_closed')
WHERE 1=1 
AND wp_posts.ID IN (3391,3392,42395,42396,42399,{LITERALLY 8000+ ids},42400,42402,42403,42406,42407,0) 
AND wp_posts.post_parent = 0 
AND (wp_posts.ID NOT IN (
        SELECT object_id
        FROM wp_term_relationships
        WHERE term_taxonomy_id IN (6)) AND 
    wp_posts.ID NOT IN (
        SELECT object_id
        FROM wp_term_relationships
        WHERE term_taxonomy_id IN (637)) AND 
    wp_posts.ID NOT IN (
        SELECT object_id
        FROM wp_term_relationships
        WHERE term_taxonomy_id IN (436))
    ) 
AND (wp_postmeta.meta_key = '_price' AND 
        (
            mt1.post_id IS NULL OR 
            (mt2.meta_key = '_stock_status' AND mt2.meta_value != 'outofstock') OR 
            mt3.post_id IS NULL OR 
            mt4.post_id IS NULL
        )
    ) 
AND wp_posts.post_type = 'product' 
AND ((wp_posts.post_status = 'publish'))
GROUP BY wp_posts.ID
ORDER BY wp_postmeta.meta_value+0 DESC

Is it because of all the JOINs from other plugins?

Or because the 8K+ post ids?

Should I build my own search query?

Here's a EXPLAIN:

"1" "PRIMARY"   "wp_posts" NULL "range" "PRIMARY,post_name,type_status_date,post_parent,post_author"    "PRIMARY"   "8" NULL    "8479"  "5.00"  "Using where"
"1" "PRIMARY"   "wp_postmeta"   NULL    "ref"   "post_id,meta_key"  "post_id"   "8" "wordpress.wp_posts.ID" "20"    "2.64"  "Using where"
"1" "PRIMARY"   "mt1" NULL  "ref"   "post_id,meta_key"  "post_id"   "8" "wordpress.wp_posts.ID" "20"    "100.00"    "Using where"
"1" "PRIMARY"   "mt2" NULL  "ref"   "post_id"   "post_id"   "8" "wordpress.wp_posts.ID" "20"    "100.00"    NULL
"1" "PRIMARY"   "mt3" NULL  "ref"   "post_id,meta_key"  "meta_key"  "767"   "const" "1" "100.00"    "Using where"
"1" "PRIMARY"   "mt4" NULL  "ref"   "post_id,meta_key"  "post_id"   "8" "wordpress.wp_posts.ID" "20"    "100.00"    "Using where"
"4" "SUBQUERY"  "wp_term_relationships" NULL    "ref"   "PRIMARY,term_taxonomy_id"  "term_taxonomy_id"  "8" "const" "100"   "100.00"    "Using index"
"3" "SUBQUERY"  "wp_term_relationships" NULL    "ref"   "PRIMARY,term_taxonomy_id"  "term_taxonomy_id"  "8" "const" "1" "100.00"    "Using index"
"2" "SUBQUERY"  "wp_term_relationships" NULL    "ref"   "PRIMARY,term_taxonomy_id"  "term_taxonomy_id"  "8" "const" "8" "100.00"    "Using index"

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.