[wp-trac] [WordPress Trac] #7761: Unnecessary SQL causing slowdown for large sites

[WordPress Trac]

#7761: Unnecessary SQL causing slowdown for large sites
--------------------------+-------------------------------------------------
 Reporter:  Otto42        |       Owner:  anonymous
     Type:  defect        |      Status:  new      
 Priority:  normal        |   Milestone:  2.7      
Component:  Optimization  |     Version:  2.6.1    
 Severity:  normal        |    Keywords:           
--------------------------+-------------------------------------------------
 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.

-- 
Ticket URL: <http://trac.wordpress.org/ticket/7761>
WordPress Trac <http://trac.wordpress.org/>
WordPress blogging software