id,summary,reporter,owner,description,type,status,priority,milestone,component,version,severity,resolution,keywords,cc
7761,Unnecessary SQL causing slowdown for large sites,Otto42,anonymous,"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.
",defect (bug),closed,normal,2.7,Optimization,2.6.1,normal,fixed,,