[wp-trac] [WordPress Trac] #10964: Improving query_posts performance

[WordPress Trac]

#10964: Improving query_posts performance
-------------------------------------+-----------------------------
 Reporter:  buch0090                 |       Owner:
     Type:  enhancement              |      Status:  new
 Priority:  normal                   |   Milestone:  Future Release
Component:  Performance              |     Version:  2.8.4
 Severity:  normal                   |  Resolution:
 Keywords:  has-patch needs-testing  |
-------------------------------------+-----------------------------

Comment (by mwidmann):

 Acutally it doesn't help once a GROUP BY is involved.  With no GROUP BY
 it's about 4-5 times less time consuming on the db, though. Therefore it
 still would be a good thing to do. You'd need to check for $groupby being
 st though.

 As the speed increase for unfiltered queries (e.g. the loop on the
 frontpage or the edit posts page in the admin) is substantial, I'd
 consider applying a modified version nevertheless.

 My mu-plugin now looks like this:

 {{{
 add_filter( 'posts_clauses', 'dh_store_last_post_clauses', 10, 2 );
 add_filter( 'query', 'dh_patch_10964', 1 );
 function dh_store_last_post_clauses( $clauses, $wp_query ) {
         global $last_post_clauses;
         $last_post_clauses = $clauses;
         return $clauses;
 }

 function dh_patch_10964( $query ) {
         global $last_post_clauses;

         if ( is_array( $last_post_clauses) && empty(
 $last_post_clauses['groupby'] ) ) {
                 if ( strpos( $query, 'SQL_CALC_FOUND_ROWS' ) ) {
                         add_filter( 'found_posts_query',
 'dh_patch_10964_phase2', 10, 2 );
                         $query = str_replace( 'SQL_CALC_FOUND_ROWS', '',
 $query );
                 }
         }
         return $query;
 }

 function dh_patch_10964_phase2( $query, $wp_query ) {
         global $wpdb, $last_post_clauses;
         remove_filter( 'found_posts_query', __FUNCTION__ );

         if ( is_array( $last_post_clauses ) ) {
                 $where = '';
                 $groupby = '';
                 $orderby = '';
                 $join = '';

                 $pieces = array( 'where', 'groupby', 'join', 'orderby',
 'distinct', 'fields', 'limits' );

                 if ( empty($groupby) ) {

                         foreach ( $pieces as $piece )
                                 $$piece = isset( $last_post_clauses[
 $piece ] ) ? $last_post_clauses[ $piece ] : '';

                         if ( !empty( $orderby ) )
                                 $orderby = 'ORDER BY ' . $orderby;
                         $query = "SELECT count(ID) FROM $wpdb->posts $join
 WHERE 1=1 $where $orderby";
                 }
         }
         return $query;
 }
 }}}

 I took the freedom to create a patch with the changes.

-- 
Ticket URL: <http://core.trac.wordpress.org/ticket/10964#comment:88>
WordPress Trac <http://core.trac.wordpress.org/>
WordPress blogging software