CODEWITHSUNDEEP
mysqlwordpressoptimization
lorenzov
lorenzov

Reputation: 233

Query optimization

I have an installation of WP with more than 50k articles. sometimes the site slows down because of many locked queries. it seems that some code is launching insane queries like this one

SELECT SQL_CALC_FOUND_ROWS  wp_posts.* 
   FROM wp_posts  
   WHERE 1=1  
   AND wp_posts.ID NOT IN (43500, ... )) 
   AND wp_posts.ID NOT IN ( SELECT tr.object_id 
                                   FROM wp_term_relationships AS tr 
                                   INNER JOIN wp_term_taxonomy AS tt
                                   ON tr.term_taxonomy_id = tt.term_taxonomy_id WHERE tt.taxonomy = 'category' AND tt.term_id IN ('20207', '20788', '20789', '20790', '20791', '20792', '20793', '20794', '20795', '20796', '20797', '20798', '20799', '20800', '20801', '20802', '20803', '20804', '20805', '20806', '20807', '20808', '21012') ) 
                                  AND wp_posts.post_type = 'post' 
                                  AND (wp_posts.post_status = 'publish')  
   ORDER BY wp_posts.post_date DESC LIMIT 0, 5

the wp_posts ID NOT IN (...) list contains more than 5000 ids and this result in a very looooong query.

where does this query come from? how can I improve it?

Upvotes: 0

Views: 246

Answers (3)

lorenzov
lorenzov

Reputation: 233

I managed to optimize the query by writing my own sql code in the template.

Here is the code I use to select posts (excluding two categories). After the query I use the loop normally

<?php
$querystr = "SELECT DISTINCT wposts.* 
FROM $wpdb->posts wposts
    LEFT JOIN $wpdb->postmeta wpostmeta ON wposts.ID = wpostmeta.post_id 
    LEFT JOIN $wpdb->term_relationships ON (wposts.ID = $wpdb->term_relationships.object_id)
    LEFT JOIN $wpdb->term_taxonomy ON ($wpdb->term_relationships.term_taxonomy_id = $wpdb->term_taxonomy.term_taxonomy_id)
WHERE 
     $wpdb->term_taxonomy.taxonomy = 'category'
    AND NOT $wpdb->term_taxonomy.term_id IN(20207, 20788)
        AND post_status = 'publish'



ORDER BY post_date desc limit 15;

";
$pageposts = $wpdb->get_results($querystr, OBJECT);
  global $post; 
 foreach ($pageposts as $post): ?>
 <?php setup_postdata($post);

Upvotes: 0

Brimstedt
Brimstedt

Reputation: 3140

I do not know where the query comes from, but try a text search on your wp installation. Searching for SQL_CALC.. will probably do the trick.

IN and NOT IN are performance killers as in most query optimisers this is the same as a hug list of ORs.

Not sure what mysql supports, but NOT EXIST usually gives better performance. Left joining and WHERing on IS NOT NULL can sometimes do the trick too.

Upvotes: 1

Sudantha
Sudantha

Reputation: 16204

Creating indexes in foreign keys which in tables which are used in the JOIN will increse the performance

Upvotes: 1

Related Questions