SitePoint Sponsor

User Tag List

Results 1 to 3 of 3
  1. #1
    SitePoint Addict
    Join Date
    Jul 2008
    Posts
    220
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Which one better? INSERT + SELECT SUM() or SELECT + UPDATE

    say whenever my member plays a game and earn certain points.

    Case 1:--

    Code MySQL:
    INSERT INTO sample_table VALUES('$username','123');

    where 123 is the game points which can be of any value.

    Thus we will have many many many rows for this player's points.

    So, when retrieving the TOTAL points, I will use

    Code PHP:
    SELECT SUM(points) as total_points FROM sample_table WHERE player='$username';
     
    $display_total_points = $row['total_points'];

    Or

    Case 2:--

    We pre-create the table with default values of 0 for points field.

    When the player earns points, I retrieve the value FIRST:--

    Code PHP:
    $q = "SELECT points FROM sample_table WHERE player='$username'"
     
    list($player_points) = mysql_fetch_row($q);
     
    $player_points += $earned_points;
    Then update the points

    Code MySQL:
    UPDATE sample_table SET points='$player_points' WHERE WHERE player='$username'

    Hence in this case we always have just one row of data. Retrieving the points value is certainly much faster than Case 1 where we must SUM all values

    Get the points value into database is much faster and easier in Case 1, just always INSERT, that is all. But getting total points will require SUM all rows of data...

    In Case 2, whenever the player earns points, we must first retrieve original points value, then add up, then UPDATE. But Getting points is faster.

    Assuming frequency of getting new datas into database is the same as retrieving points value.

    So which should I choose??

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,251
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    keep the raw data

    if you just update the running total, you won't know how many games he's played, you won't be able to do stats per month, etc.

    note: for the update, you don't actually have to do a SELECT first...

    Code:
    UPDATE sample_table 
       SET points = points + 123
     WHERE player = '$username'
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Addict
    Join Date
    Jul 2008
    Posts
    220
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    keep the raw data

    if you just update the running total, you won't know how many games he's played, you won't be able to do stats per month, etc.

    note: for the update, you don't actually have to do a SELECT first...

    Code:
    UPDATE sample_table 
       SET points = points + 123
     WHERE player = '$username'
    it is all simplify version that help me just to understand, by ignoring all others things like how many game played, stats per month.....etc.

    Code:
    UPDATE sample_table 
       SET points = points + 123
     WHERE player = '$username'
    Thank you, one valuable lesson learned. I think I will choose Case 2 instead.


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
  •