Inefficient query generation in query_posts [With Suggested Patch]

[pedrop]

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 "categorynot_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) )";
                }

[ryan]

We support MySQL 4.0. I think subqueries were introduced in 4.1. We can't use subqueries until we bump the MySQL requirement.

[ryan]

Maybe we should consider adding wpdb::supports_subqueries() and conditionally use subqueries for categorynot_in if the mysql version supports it. If it is not supported, use the current query.

[ryan]

How's that patch look?

[ryan]

(In [8738]) Use a subquery in categorynot_in query if the DB version supports it. Props pedrop. fixes #7599

[ryan]

tag__not_in needs the same treatment.

[ryan]

Actually, it looks like this switched category__not_in from expecting term IDs to term taxonomy IDs. The subquery needs to change to use term IDs.

[ryan]

(In [9031]) tagnot_in and categorynot_in query fixes. see #7599