MySql speed of executing max(), min(), sum() on relatively large database

Question

I have a relatively large database (130.000+ rows) of weather data, which is accumulating very fast (every 5minutes a new row is added). Now on my website I publish min/max data for day, and for the entire existence of my weatherstation (which is around 1 year).

Now I would like to know, if I would benefit from creating additional tables, where these min/max data would be stored, rather than let the php do a mysql query searching for day min/max data and min/max data for the entire existence of my weather station. Would a query for max(), min() or sum() (need sum() to sum rain accumulation for months) take that much longer time then a simple query to a table, that already holds those min, max and sum values?

Answer 1

That depends on weather your columns are indexed or not. In case of MIN() and MAX() you can read in the MySQL manual the following:

MySQL uses indexes for these operations:

To find the MIN() or MAX() value for a specific indexed column key_col. This is optimized by a preprocessor that checks whether you are using WHERE key_part_N = constant on all key parts that occur before key_col in the index. In this case, MySQL does a single key lookup for each MIN() or MAX() expression and replaces it with a constant.

In other words in case that your columns are indexed you are unlikely to gain much performance benefits by denormalization. In case they are NOT you will definitely gain performance.

As for SUM() it is likely to be faster on an indexed column but I'm not really confident about the performance gains here.

Please note that you should not be tempted to index your columns after reading this post. If you put indices your update queries will slow down!

Cheerz!

Answer 2

Yes, denormalization should help performance a lot in this case.

There is nothing wrong with storing calculations for historical data that will not change in order to gain performance benefits.