Make WordPress Core

Opened 12 years ago

Closed 12 years ago

#7599 closed defect (bug) (fixed)

Inefficient query generation in query_posts [With Suggested Patch]

Reported by: pedrop Owned by: ryan
Milestone: 2.7 Priority: normal
Severity: normal Version:
Component: General Keywords:
Focuses: Cc:



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

Attachments (1)

7599.diff (1.8 KB) - added by ryan 12 years ago.

Download all attachments as: .zip

Change History (10)

#1 @ryan
12 years ago

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

#2 @ryan
12 years ago

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.

12 years ago

#3 @ryan
12 years ago

How's that patch look?

#4 @ryan
12 years ago

  • Resolution set to fixed
  • Status changed from new to closed

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

#5 @ryan
12 years ago

  • Resolution fixed deleted
  • Status changed from closed to reopened

tag__not_in needs the same treatment.

#6 @ryan
12 years ago

  • Owner changed from pedrop to ryan
  • Status changed from reopened to new

#7 @ryan
12 years ago

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.

#8 @ryan
12 years ago

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

#9 @ryan
12 years ago

  • Resolution set to fixed
  • Status changed from new to closed
Note: See TracTickets for help on using tickets.