WordPress.org

Forums

Inefficient query generation in query_posts (4 posts)

  1. pedrop
    Member
    Posted 6 years ago #

    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=8 or term_taxonomy_id=12)...

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

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

  2. pedrop
    Member
    Posted 6 years ago #

    An even easier query to read would be something like:

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

  3. pedrop
    Member
    Posted 6 years ago #

    Sorry for the repeated posts. Here is a proposed patch to be applied to the file "wp-includes/query.php" at line 1042. Entire if statement at that line 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) )";
                    }
  4. Scott H
    Member
    Posted 6 years ago #

    I'd suggest you make a ticket on Trac with a diff for this. Your account from here acts as your login and it is more likely that this change will be considered if it is there.

Topic Closed

This topic has been closed to new replies.

About this Topic