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

Databases
1

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

Case 1:–

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

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:–

$q = "SELECT points FROM sample_table WHERE player='$username'"

list($player_points) = mysql_fetch_row($q);

$player_points += $earned_points;

Then update the points

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

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…

UPDATE sample_table
   SET points = points + 123
 WHERE player = '$username'
3

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.

UPDATE sample_table 
   SET points = points + 123
 WHERE player = '$username'

Thank you, one valuable lesson learned. I think I will choose Case 2 instead.