id,summary,reporter,owner,description,type,status,priority,milestone,component,version,severity,resolution,keywords,cc
7599,Inefficient query generation in query_posts [With Suggested Patch],pedrop,ryan,"

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) )"";
                }
}}}
",defect (bug),closed,normal,2.7,General,,normal,fixed,,