How best to recalculate group by values

I have a table that stores the summed values of a large table. I'm not calculating them on the 开发者_Python百科fly as I need them frequently.

What is the best way to update these values?

1. I could delete the relevant rows from the table, do a full group by sum on all the relevant lines and then insert the new data.

2. Or I could index a timestamp column on the main table, and then only sum the latest values and add them to the existing data. This is complicated because some sums won't exist so both an insert and an update query would need to run.

I realize that the answer depends on the particulars of the data, but what I want to know is if it is ever worth doing the second method; if there are millions of rows being summed in the first example and only tens in the second, would the second be significantly faster to execute?

You can try with triggers on update/delete. Then you check inserted or deleted value and according to it modify the sum in second table.

http://dev.mysql.com/doc/refman/5.0/en/triggers.html

For me there is several ways :

• Make a view which should be up-to-date (i don't know if you can do concrete views in mysql)
• Make a table which will be up-to-date using a trigger (on update/delete/insert as example) or using a batch during (night, so data will be 1 day old)
• Make a stored procedure which will be retrieving and computing only the data needed.

I would do something like this (INSERT UPDATE):

mysql_query("
INSERT INTO sum_table (col1, col2)
SELECT id, SUM(value)
FROM table
GROUP BY id
ON DUPLICATE KEY UPDATE col2 = VALUES(col2)
");

Please let me know if you need more examples.