Re: Averaging data on the fly vs Second Table

[Rick James]

Any program doing anything with 30 million things will take time. It's not so much a question of MySQL, as it is of CPU speed and disk speed. I strongly recommend rolling up the data into summary tables. Based on what you say, "quarter-hourly" would be the only table you need. I would have the time (rounded down the the quarter-hour) and the average for that time period (perhaps as a float). You might keep max and min in that table, too.

Running queries against the summary table will necessarily run 900 (# seconds in a quarter-hour) times as fast. Probably you would not need any other summary tables.

You could store the 30M rows in a db table, but plan on doing the real work with the summary table(s).

I suspect you will often find no temperature change over a 15-minute period. It might make more sense to collect the data for a minute (or 15), then store the average (etc) in a table. Or do you really have use for the second-by-second details?