CODEWITHSUNDEEP
phpmysqlperformance
JZZ
JZZ

Reputation: 1

massive DB storing common calculated values vs/ calculating it on the fly

i am building a PHP+MySQL for my employer to see the statistics per sales-guy over the past months and years. the idea is to show the sales revenue per employee. there is a lot of employee (current and past employee). the statistics table i am thinking of is like this:

table_sales_revenue
id
id_sales_guy
Year
year_nbr
jan_nbr
feb_nbr
mar_nbr
etc..
dec_nbr

so for Sales Guy John

id=1
id_sales_guy=1231852
year=2011
year_nbr=12k
avg_day=32.8  (that's year_nbr/365, bcse they work 7/7)
jan_nbr=1k
feb_nbr=1k
etc..
dec_nbr=1k

I am storing the year nbr value which is the sum of the month_nbr, because the user of this app, will compare a lot based on the year_nbr, and then for more details he will go thru the month details. so i am storing it to avoid calculating it every time. is that good?

is it useful to add the first 'id' field(auto-increment, PK) to boost perf? thanks

Upvotes: 0

Views: 119

Answers (1)

Dutchie432
Dutchie432

Reputation: 29170

This question really is subjective and depends on the actual quantities of calculations being done. Generally speaking,if you are going to be doing large amounts of calculations, and your database truly is massive then yes - it is good to store some values. You'll be happier with the return time of your queries. Otherwise, performance gains and losses can be minimal.

Upvotes: 1

Related Questions