[wp-hackers] group by in SQL queries

[Otto]

I don't get it. If the order is explicitly defined as ORDER BY
post_date DESC, why would they come back in a different order? How
they are grouped shouldn't make any difference.

If this behavior is actually occurring due to this, then it seems that
it should be reported as a bug in MySQL.


On 1/14/08, Johannes Ruthenberg <johannes at bolarus.de> wrote:
> 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
> _______________________________________________
> wp-hackers mailing list
> wp-hackers at lists.automattic.com
> http://lists.automattic.com/mailman/listinfo/wp-hackers
>