Unnecessary SQL causing slowdown for large sites

[Otto42]

In wp-includes/query.php, there is this code:

if ( !empty($q['category__in']) || !empty($q['category__not_in']) || !empty($q['category__and']) ) {
	$groupby = "{$wpdb->posts}.ID";
}

The category_in uses an inner join, and so it might be possible for it to cause duplicate rows that the group by eliminates. Maybe. I don't quite see it happening myself, but I can see that it is theoretically possible.

However, both category_not_in and category_and function by retrieving the post IDs from those specific categories and then creating a "posts.ID NOT IN" or "IN" bit in the where section that eliminates or adds those specific posts. In this situation, duplicates are not possible, since there is no join, and so the group by is wholly unnecessary.

Furthermore, on extremely large sets of posts (100k+), this extra group by can cause mysql to require a temporary table to hold the results. The performance drag of a "group by" in this case is just as bad, or worse, than using a "distinct".

Suggest altering this to remove the category_not_in and category_and's, as they don't require the groupby in the first place.

[Otto42]

Additional: support forum reference ​http://wordpress.org/support/topic/195967

[ryan]

Note that categorynot_in uses a subquery if the DB version supports it. See #7599.

[Otto42]

Replying to ryan:

Note that category_not_in uses a subquery if the DB version supports it. See #7599.

Yes, I noticed that recent change, but the problem still exists. Even with the subquery, the GROUP BY clause is unnecessary, as it's using "posts.ID NOT IN (subquery)", which is not substantially any different than "posts.ID NOT IN (list of post numbers)" for this purpose.

[ryan]

(In [8968]) Group by not needed for not_in and and taxonomy queries. Props Otto42. fixes #7761