|
|
|
|
|
|
by bcoates
4722 days ago
|
|
|
If you just need simple subdivisions of time, like group by date(col)
or group by date(col), hour(col), floor(minute(col)/5)
an index on col is enough, or at least the last time I checked for my specific queries it was. If you need something like group by hour-of-day you'll need to create an indexed column of course. (this is with mariadb but I think basic mysql handles it too) |
|
|
Mysql will still use the column for a covering index and run the query in a single-pass, but it will still generate a temporary table for the results (and do a really slow sort unless you do 'order by null'). It doesn't look like mysql has any way of doing date grouping without temporaries even in the trivial cases. I guess eliminating the sort was enough for my queries.
Using date()-like functions, date_format(), and left() all have identical query plans and roughly comparable performance.