I am implementing some statistics with the data in a new PHP application so the database is not large at the moment so I just query for the user, add up the values and get the average and draw a graph, right now everything is done in PHP and it is fast however when the data increases (1 row per user per day) so a year from now maybe the user will like to see the average for the year.
So my question is, should I
perform the calculation in mysql and run two queries 1 for the sum and one for the data to draw the graph?
mysql SELECT `user` , SUM( `amount` ) FROM `records` GROUP BY ( `user` )
Get the rows and while running the foreach to draw the graph sum the values
What do you think?
by the way I have to do the calculation for 5 different columns
Yes, if he needs to loop over the data rows anyway then I think doing it in PHP would be better - but I’d expect the difference to be negligible.
However, there’s another reason to do the summing in MySQL - but it may not apply to the OP’s scenario (it doesn’t now with his current data types). The reason is precision. I don’t know what kind of data the the ‘amount’ column is supposed to hold but if he means monetary data then a DECIMAL type would be preferable because it’s precise. Then if he needs precise sum of all values then (potentially) he might come across precision problems when adding floats in php unless he resorts to tricks like using bc_add(). MySQL’s SUM will give the exact number right away.