Will forcing `group by` to sort DESC speed up code up, or slow it down?

In MySQL group by does an implicit order by ASC.

This is great if you wanted to add an ORDER BY ASC because then the results are already ordered.开发者_StackOverflow社区

But if you want to ORDER BY .. DESC MySQL has to order the resultset exactly the other way round.

Will this trick speed up the select, slow it down, or do nothing at all

SELECT field1, field2 FROM atable 
GROUP BY -mydate   -- group by trick to force a `group by ... desc`
ORDER BY mydate DESC

I know I can just time the code, but I'm looking to gain some deeper insight into the issues at hand.

All the relevant indexes are in place naturally, because it would be silly to optimize without indexes.

From my tests, adding any sort a modifier to group by like - to change the sort order slows things down.

However you are allowed to specify:

SELECT id, name, sum(amount) FROM customers GROUP BY id DESC

And MySQL will happily order the results in DESC order without needing an extra order by clause. This will not incur the extra runtime that adding the - does.

I think you're mistaken: GROUP BY doesn't sort data. It's the default MySQL behaviour that does, as MySQL adds the same ORDER BY as the GROUP BY you've set, as you've mentioned in your first sentence.

So, if you disable the sort, by using ORDER BY NULL, there's no sorting at all. The GROUP BY will only group rows together, using indexes if possible. Hence the «trick» is wrong, as you'll remove the ability to use an index on mydate. GROUP BY performs great as long as the index is good for it.

So:

SELECT field1, field2 FROM atable 
GROUP BY mydate
ORDER BY NULL

should be really fast if you have an index on (mydate), and

SELECT field1, field2 FROM atable 
GROUP BY mydate
ORDER BY mydate DESC

should be as fast (depending on the table structure, MyISAM is a little bit slower in reverse order).

If you have a WHERE clause, check that you've added the columns in the index, for example:

SELECT field1, field2 FROM atable 
WHERE fied1 = 5
GROUP BY mydate
ORDER BY mydate DESC

will need an index on (field1,mydate).

Slow It Down

What's happening here is that you are asking MySQL to sort the records based on a (probably) non-indexed column mydate.

Any sort takes time, but sorts on indexed columns are blazing fast compared to non-indexed ones.

Here's some additional reading: http://www.mysqlperformanceblog.com/2006/09/01/order-by-limit-performance-optimization/