[wp-trac] [WordPress Trac] #4665: Optimize get_posts query

[WordPress Trac]

#4665: Optimize get_posts query
--------------------------+-------------------------------------------------
 Reporter:  Otto42        |       Owner:  anonymous  
     Type:  enhancement   |      Status:  new        
 Priority:  normal        |   Milestone:  2.3 (trunk)
Component:  Optimization  |     Version:  2.2.1      
 Severity:  major         |    Keywords:             
--------------------------+-------------------------------------------------
 In post.php, in get_posts(), this is the main query built:

 {{{
         $query  = "SELECT DISTINCT * FROM $wpdb->posts ";
         $query .= empty( $category ) ? '' : ", $wpdb->term_relationships,
 $wpdb->term_taxonomy  ";
         $query .= empty( $meta_key ) ? '' : ", $wpdb->postmeta ";
         $query .= " WHERE 1=1 ";
         $query .= empty( $post_type ) ? '' : "AND post_type = '$post_type'
 ";
         $query .= empty( $post_status ) ? '' : "AND post_status =
 '$post_status' ";
         $query .= "$exclusions $inclusions " ;
         $query .= empty( $category ) ? '' : "AND ($wpdb->posts.ID =
 $wpdb->term_relationships.object_id AND
 $wpdb->term_relationships.term_taxonomy_id =
 $wpdb->term_taxonomy.term_taxonomy_id AND $wpdb->term_taxonomy.term_id = "
 . $category. ") ";
         $query .= empty( $post_parent ) ? '' : "AND
 $wpdb->posts.post_parent = '$post_parent' ";
         $query .= empty( $meta_key ) | empty($meta_value)  ? '' : " AND
 ($wpdb->posts.ID = $wpdb->postmeta.post_id AND $wpdb->postmeta.meta_key =
 '$meta_key' AND $wpdb->postmeta.meta_value = '$meta_value' )";
         $query .= " GROUP BY $wpdb->posts.ID ORDER BY " . $orderby . ' ' .
 $order;
         if ( 0 < $numberposts )
                 $query .= " LIMIT " . $offset . ',' . $numberposts;
 }}}

 The two things I'm curious about are the DISTINCT and the GROUP BY
 clauses. Neither one seems to be actually necessary.

 DISTINCT would eliminate duplicate rows. However, because it's selecting
 from $wpdb->posts (and not joining to itself), no duplicate rows are
 possible here.

 GROUP BY the posts->ID is useless, since the ID is the primary key, and
 unique.

 While I don't know if this has any real performance impact, it's certainly
 ripe for trimming and testing. I cut those out on my site a while back and
 have not noticed anything strange happening.

 Change the query to this:
 {{{
         $query  = "SELECT * FROM $wpdb->posts ";
 ... unchanged stuff ...
         $query .= " ORDER BY " . $orderby . ' ' . $order;
 ... unchanged stuff ...
 }}}

 Seems to work fine for me. And, in theory, should provide a speedup. The
 last thing we want is extra unnecessary load on the database.

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