[wp-trac] [WordPress Trac] #7599: Inefficient query generation in query_posts [With Suggested Patch]

[WordPress Trac]

#7599: Inefficient query generation in query_posts [With Suggested Patch]
---------------------+------------------------------------------------------
 Reporter:  pedrop   |       Owner:  pedrop
     Type:  defect   |      Status:  new   
 Priority:  normal   |   Milestone:  2.7   
Component:  General  |     Version:        
 Severity:  normal   |    Keywords:        
---------------------+------------------------------------------------------
 Hi,

 It appears that the wp_query->get_posts function (also called by
 query_posts and used to generate the main loop) isn't building a very
 efficient query when the "category__not_in" parameter is used. What the
 current code does (see below) is first query for a list of posts that have
 that category and then include an array with the id's of the posts into
 the actual query:

 {{{

 if ( !empty($q['category__not_in']) ) {
                         $ids = get_objects_in_term($q['category__not_in'],
 'category');
                         if ( is_wp_error( $ids ) )
                                 return $ids;
                         if ( is_array($ids) && count($ids > 0) ) {
                                 $out_posts = "'" . implode("', '", $ids) .
 "'";
                                 $whichcat .= " AND $wpdb->posts.ID NOT IN
 ($out_posts)";
                         }
                 }
 }}}


 This may work ok for a small amount of posts, but when the database has
 10,000+ posts and most of them aren't in that category it is extremely
 slow.

 An improved version could either use a left join or create a sub query
 such as:

 ...where id not in (select object_id from wp_term_relationships where
 term_taxonomy_id in (8,12) )...

 Where the 8 and the 12 are categories that should be excluded.

 This has proven to be dramatically faster on a large database.

 Here is a proposed patch to be applied to the file "wp-includes/query.php"
 at line 1042 of wordpress 2.6.1. Entire if statement at that line (which
 is the one pasted above) should be replaced with the if statement below:


 {{{
 if ( !empty($q['category__not_in']) ) {
                         $cat_string= "'" . implode("', '",
 $q['category__not_in']) . "'";
                         $whichcat .= " AND $wpdb->posts.ID NOT IN (select
 $wpdb->term_relationships.object_id from $wpdb->term_relationships where
 $wpdb->term_relationships.term_taxonomy_id in ($cat_string) )";
                 }
 }}}

-- 
Ticket URL: <http://trac.wordpress.org/ticket/7599>
WordPress Trac <http://trac.wordpress.org/>
WordPress blogging software