0
SELECT SQL_CALC_FOUND_ROWS pn_posts.id 
FROM   pn_posts 
       LEFT JOIN pn_term_relationships 
              ON ( pn_posts.id = pn_term_relationships.object_id ) 
       INNER JOIN pn_postmeta 
               ON ( pn_posts.id = pn_postmeta.post_id ) 
       INNER JOIN pn_postmeta AS mt1 
               ON ( pn_posts.id = mt1.post_id ) 
       LEFT JOIN pn_wc_product_meta_lookup wc_product_meta_lookup 
              ON pn_posts.id = wc_product_meta_lookup.product_id 
WHERE  1 = 1 
       AND ( pn_posts.id NOT IN (SELECT object_id 
                                 FROM   pn_term_relationships 
                                 WHERE  term_taxonomy_id IN ( 8 )) 
             AND pn_term_relationships.term_taxonomy_id IN ( 21 ) ) 
       AND ( pn_postmeta.meta_key = '_manage_stock' 
             AND mt1.meta_key = '_stock_status' ) 
       AND pn_posts.post_type = 'product' 
       AND ( pn_posts.post_status = 'publish' ) 
GROUP  BY pn_posts.id 
ORDER  BY pn_postmeta.meta_value ASC, 
          Field (mt1.meta_value, 'instock', '3days', 'outofstock'), 
          wc_product_meta_lookup.min_price ASC 
LIMIT  48, 24; 

enter image description here

2
  • A non-ERM table structure? Is it particularly slow? Can it be cached? aka - how often does the return value change / do inaccuracies matter?
    – danblack
    Mar 15 '20 at 23:58
  • Condition other than IS NULL by right table in WHERE - and LEFT JOIN?
    – Akina
    Mar 16 '20 at 4:37
1

A first step in improving the performance of WordPress is to change the "meta" schema as discussed here:

http://mysql.rjweb.org/doc.php/index_cookbook_mysql#speeding_up_wp_postmeta

If that does not help enough, I will take a deeper look at your query.

Your Answer

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

Not the answer you're looking for? Browse other questions tagged or ask your own question.