Re: How to somehow use index for group by expression

[Rick James]

select  description as name,
        sum(`count`),
        max(date(transdate)),
        ceil((UNIX_TIMESTAMP(`timestamp`) - 1306382221) / (86400 * 100)) as grouper
    from  reporting
    where  UNIX_TIMESTAMP(`timestamp`) between 1323595130 and 9999999999
      and  transtype in (0 , 1, 2, 10, 11, 12, 13)
    group by  name , grouper
    order by  name , `timestamp`

The optimal index is the "compound" INDEX(transtype, `timestamp`)
You will still have "using temp, filesort", but it will not be as bad.
You don't need indexes with expressions (in this case).

Make the ORDER BY the same as the GROUP BY.

What's wrong with your "grouper"? Maybe 1306382221 isn't quite the right value? If not, then use UNIX_TIMESTAMP('2012-01-01'), or whatever.

> where UNIX_TIMESTAMP(`timestamp`) between 1323595130 and 9999999999
Why not:
where UNIX_TIMESTAMP(`timestamp`) >= 1323595130

Instead of
max(date(transdate)),
You could calculate the end (or start) of the "grouper" range:
ceil((UNIX_TIMESTAMP(`timestamp`) - 1306382221) / (86400 * 100)) * (86400 * 100)
At which point, you don't need both the max and the grouper; keep the grouper.

Or, are you looking for the 'last' timestamp in each group?