[wp-hackers] group by in SQL queries

[Johannes Ruthenberg]

Hi everyone!

Just wanted to share something strange that caused some confusion among 
German WordPress users lately. I don't know if this is already known and 
if anything needs to be done about it (I usually don't read SQL specific 
mails on this list too carefully). Couldn't find something in Trac, but 
I thought a lot of you would know if this needs to be fixed.

For all German speaking people, here is a detailed report on this:
http://schnurpsel.de/wordpress-und-die-suboptimale-mysql-optimierung-5051-74/

For everyone else I'll try to summarize it:

In WP 2.0.x queries for posts contain the following:
   GROUP BY wp_posts.ID
   ORDER BY post_date DESC

The "group by" has no real effect here and has been removed in WP 2.1. 
Now lately a lot of providers updated their MySQL to 5.0.51 where this 
group by doesn't get optimized out of the query anymore. So the effect 
was that suddenly the postings in the blog were not descending by date 
anymore but ascending by ID. So the oldest entry was at the top. A very 
strange effect, and it took some time for a clever user to find the reason.

So the first question is does this need to be fixed in the WP 2.0.x 
releases? I seem to remember that this branch still gets bugfix releases 
every now and then?!

And then for the current WordPress version: The user who reported this 
says it also effects the current versions of WP if you use categories or 
tags in the query, like this:
query_posts( $query_string.'&cat=-8' );

So there is the possibility that something needs to be changed in the 
current version, too. Sorry if I made any mistake in summarizing this. I 
haven't looked into it too closely. Just thought I should mention it 
here. :-)

Greetings,
Johannes