Slow/complex queries (on sorting?)
we noticed that there are quite slow queries now and then (the server admin told us about it) and after looking at those kind of queries that take while to finish (1-2 minutes) or even fail and stay in an endless loop so that he had to kill the processes manually (!), we think this is related to sorting (and maybe filtering as well) by admin columns functionality:
we have quite a lot of (ACF) postmeta data that we use to add columns with CAC.
please see this query:
SELECT SQL_CALC_FOUND_ROWS 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 = 'is_blocked' ) 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 = 'is_blocked' ) LEFT JOIN wp_postmeta AS mt4 ON ( wp_posts.ID = mt4.post_id ) LEFT JOIN wp_postmeta AS mt5 ON (wp_posts.ID = mt5.post_id AND mt5.meta_key = 'is_blocked' ) LEFT JOIN wp_postmeta AS mt6 ON ( wp_posts.ID = mt6.post_id ) WHERE 1=1 AND ( ( ( wp_postmeta.meta_key = 'is_blocked' AND CAST(wp_postmeta.meta_value AS CHAR) = '0' ) OR mt1.post_id IS NULL ) OR ( ( mt2.meta_key = 'is_blocked' AND CAST(mt2.meta_value AS CHAR) = '0' ) OR mt3.post_id IS NULL ) OR ( ( mt4.meta_key = 'is_blocked' AND CAST(mt4.meta_value AS CHAR) = '0' ) OR mt5.post_id IS NULL ) OR ( mt6.meta_key = 'related_country' AND CAST(mt6.meta_value AS SIGNED) = '98' ) ) AND wp_posts.post_type = 'mep' AND (wp_posts.post_status = 'publish' OR wp_posts.post_status = 'acf-disabled' OR wp_posts.post_status = 'future' OR wp_posts.post_status = 'draft' OR wp_posts.post_status = 'pending' OR wp_posts.post_author = 32 AND wp_posts.post_status = 'private') AND wp_posts.id NOT IN (SELECT post_id FROM wp_wpfront_ure_post_type_permissions WHERE role IN ('country_manager') AND enable_permissions = 1 AND has_read = 0 GROUP BY post_id HAVING COUNT(*) = 1) GROUP BY wp_posts.ID ORDER BY wp_posts.menu_order ASC LIMIT 0, 20;
This seems like a rather expensive complex query that can potentially be optimized.
Maybe there’s something to be optimized here? The JOINS that are used to query the postmeta ‘is_blocked’ seem redundant to me.
You must be logged in to reply to this topic.