Sum values of multiple rows mysql vs php performance

Hello all,

If I have a table like this

[table=“width: 500”]
[tr]
[td]id (int)[/td]
[td]user (varchar)[/td]
[td]amount (double)[/td]
[/tr]
[tr]
[td]1[/td]
[td]1[/td]
[td]1.2[/td]
[/tr]
[tr]
[td]2[/td]
[td]1[/td]
[td]0.3[/td]
[/tr]
[tr]
[td]3[/td]
[td]1[/td]
[td]2.6[/td]
[/tr]
[tr]
[td]4[/td]
[td]1[/td]
[td]1[/td]
[/tr]
[tr]
[td]5[/td]
[td]2[/td]
[td]1[/td]
[/tr]
[tr]
[td]6[/td]
[td]2[/td]
[td][/td]
[/tr]
[tr]
[td]7[/td]
[td]2[/td]
[td]5.9[/td]
[/tr]
[tr]
[td]8[/td]
[td]1[/td]
[td]6[/td]
[/tr]
[tr]
[td]9[/td]
[td]2[/td]
[td]1[/td]
[/tr]
[tr]
[td]10[/td]
[td]2[/td]
[td]0.7[/td]
[/tr]
[/table]

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

  1. 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` )
  2. 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

If you need the single row data as well, I think your solution is just fine.
But, you can always do a test. Fill your table with a years worth of test data, and try the two solutions :slight_smile:

yes I guess that is what I should do, also what I was thinking is that in any case a years data is only 365 rows so is still not that much I guess

I believe SUM in MySQL will always be faster than grabbing all rows and summing in php. For larger datasets it will much faster.

But if the OP needs to get the entire record set and loop through it anyway, I’m sure doing the summing in PHP will be faster than doing an extra MySQL call ?

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.

not if they’re FLOATs