id	summary	reporter	owner	description	type	status	priority	milestone	component	version	severity	resolution	keywords	cc
10964	Improving query_posts performance	buch0090		"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"	enhancement	closed	normal		Performance	2.8.4	normal	duplicate	has-patch needs-testing	willmot joehoyle m@… popokolok rowan@… shockdiode mikeschinkel@… Klark0 mpretty@… seanosh pkirk TonyTheGreat martin.widmann@… milez graffics MZAWeb sheatsb asannad sbressler@…