SitePoint Sponsor

User Tag List

Results 1 to 7 of 7
  1. #1
    SitePoint Addict tlacaelelrl's Avatar
    Join Date
    Apr 2011
    Location
    Mexico city, Mexico
    Posts
    353
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Question sum values of multiple rows mysql vs php performance

    Hello all,

    If I have a table like this

    id (int) user (varchar) amount (double)
    1 1 1.2
    2 1 0.3
    3 1 2.6
    4 1 1
    5 2 1
    6 2
    7 2 5.9
    8 1 6
    9 2 1
    10 2 0.7

    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?
      Code 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
    Do you get bothered because I do the same thing every day?
    Do you question why I do it?
    Then find something that you actually like doing!!!

    Stop thinking on what I do.

  2. #2
    From Italy with love silver trophybronze trophy
    guido2004's Avatar
    Join Date
    Sep 2004
    Posts
    9,509
    Mentioned
    163 Post(s)
    Tagged
    4 Thread(s)
    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

  3. #3
    SitePoint Addict tlacaelelrl's Avatar
    Join Date
    Apr 2011
    Location
    Mexico city, Mexico
    Posts
    353
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by guido2004 View Post
    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
    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
    Do you get bothered because I do the same thing every day?
    Do you question why I do it?
    Then find something that you actually like doing!!!

    Stop thinking on what I do.

  4. #4
    SitePoint Guru bronze trophy
    Join Date
    Dec 2003
    Location
    Poland
    Posts
    930
    Mentioned
    7 Post(s)
    Tagged
    0 Thread(s)
    I believe SUM in MySQL will always be faster than grabbing all rows and summing in php. For larger datasets it will much faster.

  5. #5
    From Italy with love silver trophybronze trophy
    guido2004's Avatar
    Join Date
    Sep 2004
    Posts
    9,509
    Mentioned
    163 Post(s)
    Tagged
    4 Thread(s)
    Quote Originally Posted by Lemon Juice View Post
    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 ?

  6. #6
    SitePoint Guru bronze trophy
    Join Date
    Dec 2003
    Location
    Poland
    Posts
    930
    Mentioned
    7 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by guido2004 View Post
    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.

  7. #7
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,341
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by Lemon Juice View Post
    MySQL's SUM will give the exact number right away.
    not if they're FLOATs
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"


Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •