Inefficient query generation in query_posts

  • pedrop

    (@pedrop)


    15 years, 8 months 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.

Viewing 3 replies - 1 through 3 (of 3 total)
  • Thread Starter pedrop

    (@pedrop)

    15 years, 8 months 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) )…

    Thread Starter pedrop

    (@pedrop)

    15 years, 8 months 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) )";
                }
    Scott H

    (@scohoust)

    15 years, 8 months 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.

Viewing 3 replies - 1 through 3 (of 3 total)
  • The topic ‘Inefficient query generation in query_posts’ is closed to new replies.