I've WP installation with some 120K posts. One of the slow queries that frequently appear at @mysql-slow.log is below

 # Query_time: 1.744827  Lock_time: 0.000567 Rows_sent: 1  Rows_examined: 635732

 SELECT SQL_CALC_FOUND_ROWS  wp_posts.ID FROM wp_posts  LEFT JOIN wp_postmeta ON 
(wp_posts.ID = wp_postmeta.post_id AND wp_postmeta.meta_key = 'sort_order_' ) WHERE 1=1 
AND (wp_postmeta.post_id IS NULL) AND wp_posts.post_type IN ('post', 'photo', 'video')
AND ((wp_posts.post_status = 'publish')) GROUP BY wp_posts.ID ORDER BY wp_posts.post_date DESC LIMIT 0, 10;

And that is caused by WP_Query with meta_query arguments

$args = array(
            'post_type' => array('post','photo','video'),
            'post_status'=> 'publish',
            'posts_per_page' => 10,
            'meta_query'  => array(
            'relation' => 'AND',
            array( 'key' => 'sort_order_', 'compare' => 'NOT EXISTS')

Is there any way to speed up this query. May be like indexing. But how? Any help will be greatly appreciated . Thank you

  • That meta query is probably the culprit. What’s it for? Why are you checking that a key doesn’t exist? – Jacob Peattie Mar 31 '19 at 2:37
  • I've another query to fetch posts where the key does exist. Two quires work like, 'Post sorted' and 'Post to sort'. and this 'post to sort' fetch only posts which have no sort key. meaning the posts not already sorted. Interestingly key 'exist' query never logged in slow query log. – TELA Mar 31 '19 at 7:04
  • Is it possible to index that key ? Does it affect mysql performance in any other way? Or how to effectively index that key? Any help plz – TELA Mar 31 '19 at 7:06
  • Think about caching, like WP_Cache for an object cache on your server, maybe APC, Memcached? Also you can enhance this query, if it is possible to enhance the code to use the core transient cache; if you don't have the possibility of an object cache. – bueltge Mar 31 '19 at 12:39

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.