Re: consolidating temporal data

[Peter Brawley]

MySQL Views do not optimise well. You're adding something like half a million data rows a day, so yes, a regularly updated summary table will speed up report queries considerably.

Whether it's made with sprocs, functions or straightforward scriots doesn't matter much in terms of outcome. An sql script is probably easiest to develop, debug and maintain.

What you do need to get right, though, are the exact data needed in the table and the queries that will have to run against it, so I suggest the first thing is to write a first model of the summary table, populate it with realistic data, then write, test, optimise & debug the queries that will need to be run against it. In the course of doing that, you'll find yourself fine-tuning the table, and when you're done, you'll already have the required table structure and much of the script logic needed to update it--you'll just need to add date-specific scoping to the queries that update the summary table.