On 10/5/2010 5:07 AM, Tompkins Neil wrote:
> I have a number of tables of which I use to compute totals. For example I
> table : players_master
> I can easily compute totals for the field rating. However, at the end of a
> set period within my application, the values in the rating field are
> changed. As a result my computed totals would then be incorrect.
> Is the best way to overcome this problem to either compute the total and
> store as a total value (which wouldn't change in the future), or to store
> the rating values in a different table altogether and compute when required.
> If you need table information please let me know and I can send this.
Many databases designed for rapid, time-based reporting do exactly as
you propose: build a table just to hold the aggregate of a time-interval
Here's a rough example.
Let's say that you run a web site and you want to track your traffic
levels. Every second you may have thousands of hits, every hour
hundreds of thousands of hits, and by the end of the week you may have
hundreds of millions of individual data points to report on. To compute
monthly stats, you are looking at a huge volume (billions) of data
points unless you start aggregating.
Lets say you build tables like: stats_hour, stats_day, stats_week, and
Every hour, you would take the last hour's worth of traffic and condense
those values into the stats_hour table. At the end of the day, you take
the previous 24 entries from stats_hour and compute a stats_day entry.
Each level up aggregates the data from the level below.
Does that give you an idea about how other people may have solved a
MySQL Principal Technical Support Engineer
Oracle USA, Inc.
Office: Blountville, TN