MySQL single table with hourly, daily, monthly values, or separate tables?

Question

When working with data values, should I create a single table storing the hourly values, and also the aggregated daily/monthly values, or should I create separate tables for these?

I'd imagine multiple tables would be the way to go, but I'm a complete amateur here. It sounds like something that would improve performance and possibly maintenance, but I'd also like to know if this even makes a difference. In the end, having 3-4 tables vs 1 could also cause some maintenance issues I would imagine.

So basically, a values_table containing:

id     value    datetime                 range
1      33       2022-05-13 11:00:00      hourly
2      54       2022-05-13 12:00:00      hourly
3      840      2022-05-13               daily
...

vs

hourly_values_table containing:

id     value    datetime
1      33       2022-05-13 11:00:00
2      54       2022-05-13 12:00:00
...

And a daily_values_table containing:

id     value    datetime
1      840      2022-05-13
...

What would be the proper way to handle this?

Answer 1

Your hourly data is a Data Warehouse 'Fact' table". It is, I assume, written 'continually' and never updated.

"Summary Table(s)" are useful for performance. Usually only 1 is needed. For example a "daily" table gives you about a 24x reduction. From that table you can fetch weekly, monthly, or any arbitrary date range reasonably efficiently. (I need more metrics and a better feel for what type of data you are storing to be surer of what I am saying.)

I discuss using MySQL for DW and Summary tables

Sure, purists debate the storing of "redundant" data. But when you get a billion rows, you really need summary tables to avoid performace bottlenecks.

As for how long to hold onto the data in the Fact table or the Summary table, I often suggest:

• Use Partitioning for speedy of purging old data (after, say, a month), thereby saving disk space;
• Keep the summary tables 'forever', since they are 'small'.