Support

Search results for ""

Sorry, no results found. Perhaps you would like to search the community forum?
All Topics
Daniel

Slow/complex queries (on sorting?)

Hey,

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.

regards,

3 years, 4 months ago
Daniel

ok i can now confirm that it’s related to the sorting functionality on an ACF field ‘is_blocked’ with type “yes/no”.
The Post Type that is sorted by this has around 800 entries. The CAC option to show all entries on sorting is enabled.

3 years, 4 months ago
Tobias

Hi Daniel,

Those LEFT JOINS are not from Admin Columns. Could you send us an email with the list of plugins you are using on that website?

3 years, 4 months ago
Daniel

ok, might need more debugging then, here’s the list:

http://jmp.sh/BS0OSTn

3 years, 4 months ago

You must be logged in to reply to this topic.