How to do quick grouping by day and hour in mysql?

Question

MySQL

Here is the standard path.
accross.su/blog/view/131
And it would work if there were not a million records.
How to do quick temporary groupings in mysql with a large number of records?

Answer 1

Add columns with calculated values ​​(can be triggers when inserting) into int and indexes on them.

Answer 2

indices?

Answer 3

Might be worth looking into partitions? dev.mysql.com/doc/refman/5.5/en/partitioning-pruning.html

Answer 4

quickly possible only if you make storage separate
date | count
and increment with a common insert. (update order_counter set count=count+1 where date=date_format(date, 'and what type of grouping do you need'), you need to check what replace or insert will do with on duplicate key for the very first insert, ideally instead of update replace then record you won't need to check what is there,
or describe the task in more detail, if there is still some kind of filter in the selection,
then the solution with a separate counter will not help.

Answer 5

Try the following option - 1. This is for grouping by day
SELECT ...
FROM ...
GROUP BY UNIX_TIMESTAMP ( field with date and time ) DIV 24 * 3600
2. This is for grouping by hour
SELECT ...
FROM ...
GROUP BY UNIX_TIMESTAMP ( field with date and time ) DIV 3600
If it still slows down (most likely it will), then these two fields need to be added to the table and an index built on them