[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  |
-------------------------------------+-----------------------------
Changes (by mwidmann):

 * cc: martin.widmann@… (added)


Comment:

 @scribu: I wanted to give some feedback on the patch you submitted. I
 tried it out as we are currently facing some very strange issues related
 to the {{{SQL_CALC_FOUND_ROWS}}} which caused high load on the servers.

 The problem with the patch is that {{{count(*)}}} and {{{group by}}} when
 used together can create more than one rows of result, making the returned
 number of rows incorrect. In that case you'd have to count the rows
 returned.

 Because there's no easy way for me to fix this in core I created a couple
 of filters to add to a mu-plugin:
 {{{
 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 ) {
         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' );
                 foreach ( $pieces as $piece )
                         $$piece = isset( $last_post_clauses[ $piece ] ) ?
 $last_post_clauses[ $piece ] : '';

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

                 if ( !empty($groupby) ) {
                         $counts = $wpdb->get_results( $query, ARRAY_N );
                         $rows = sizeof( $counts );
                         $query = "SELECT $rows cnt";
                 }
         }
         return $query;
 }
 }}}

 It seems a little bit hack-ish to me though.

 Explanation what it does:
  1. I store the last post_clauses to the {{{$last_post_clauses}}} global
 in order to have access to this data in the count statement
  1. I look at each {{{query}}} if it contains the
 {{{SQL_CALC_FOUND_ROWS}}} command and remove it from there after
 registering a filter for {{{found_posts_query}}}
  1. in the {{{found_posts_query}}} filter I check the clauses and create
 the count statement. If there's a groupby I execute the query there and
 return a helper sql statement that just returns the number of items
 instead of counting. This way the normal behavior is tricked and the stuff
 works.

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