[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:  2.9                     
Component:  Performance  |     Version:  2.8.4                   
 Severity:  normal       |    Keywords:  query_posts, performance
-------------------------+--------------------------------------------------
 We have a blog with over 80k posts and 750k records in post meta table.
 Noticed several inefficient queries that slowed down the home page of a
 theme with several widgets using the query_posts function.

 Two examples:
 SELECT SQL_CALC_FOUND_ROWS wp_1_posts.* FROM wp_1_posts WHERE 1=1 AND
 wp_1_posts.post_type = 'post' AND (wp_1_posts.post_status = 'publish')
 ORDER BY wp_1_posts.post_date DESC LIMIT 0, 10;

 and

 SELECT SQL_CALC_FOUND_ROWS wp_1_posts.* FROM wp_1_posts INNER JOIN
 wp_1_term_relationships ON (wp_1_posts.ID =
 wp_1_term_relationships.object_id) INNER JOIN wp_1_term_taxonomy ON
 (wp_1_term_relationships.term_taxonomy_id =
 wp_1_term_taxonomy.term_taxonomy_id) WHERE 1=1 AND
 wp_1_term_taxonomy.taxonomy = 'category' AND wp_1_term_taxonomy.term_id IN
 ('21', '49', '53', '488', '91', '107', '111', '136', '202', '209', '490')
 AND wp_1_posts.post_type = 'post' AND (wp_1_posts.post_status = 'publish')
 GROUP BY wp_1_posts.ID ORDER BY wp_1_posts.post_date DESC LIMIT 0, 4

 Changed wp-includes>query.php get_posts function.

 Starting at line 2251...

 //////// BELOW CODE MODIFIED BY DAVE BUCHANAN, SPLIT INTO TWO QUERIES
 /////////////////////
                 if ( !empty($limits) )
                         $found_rows = 'SQL_CALC_FOUND_ROWS';
                 /// FIRST SELECT JUST IDs
                 $tmp = " SELECT $distinct $wpdb->posts.id FROM
 $wpdb->posts $join WHERE 1=1 $where $groupby $orderby $limits";
                 $tmp_q = $wpdb->get_col($tmp);
                 $tmp_ids = implode($tmp_q,',');
                 /// NOW NORMAL SELECT WHERE ID IS IN FIRST QUERY LIST
                 $this->request = " SELECT $found_rows $distinct $fields
 FROM $wpdb->posts $join WHERE $wpdb->posts.id IN ($tmp_ids) $groupby
 $orderby ";
                 if ( !$q['suppress_filters'] )
                         $this->request = apply_filters('posts_request',
 $this->request);

                 $this->posts = $wpdb->get_results($this->request);

 Let me know if you require further information or anything.

 Thanks,
 Dave Buchanan
 Dolan Media Company

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