Optimize get_posts query

[Otto42]

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.

+1

This looks like a good idea.

Referring to #2604 and [3678] the DISTINCT and GROUP BY went from query.php a while ago giving some performance improvement.

Patch to eliminate distinct and group by from get_posts()

+1. Patch looks good.

I think we have to retain group by for queries involving categories.

I don't follow you there, ryan. The posts->ID is unique, why would grouping by it help any?

Because of the joins, try one of the queries without it. You'll get "dupe" posts.

matt: No, still not seeing it. You're joining to terms and to postmeta. Unless you have duplicated terms or postmeta info, you're not going to get the same post twice in that query.

get_posts() now uses WP_Query.