Hi Shawn
Thanks for your response. In your experience do you think I should
still retain the data used to generate the computed totals ? Or just
compute the totals and disregard the data used ?
Regards
Neil
On 8 Oct 2010, at 19:46, "Shawn Green (MySQL)"
<shawn.l.gr...@oracle.com> wrote:
Hi Neil,
On 10/5/2010 5:07 AM, Tompkins Neil wrote:
Hi
I have a number of tables of which I use to compute totals. For
example I
have
table : players_master
rec_id
players_name
teams_id
rating
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 of values.
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 stats_month.
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
similar problem?
--
Shawn Green
MySQL Principal Technical Support Engineer
Oracle USA, Inc.
Office: Blountville, TN
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=arch...@jab.org
