aristanoble
asked on
Single Column Counter VS Multi Column calculation
I have a table called users, prizes and point_transactions each with records into the millions.
In order to determine the number of available points per user, without using a view, should I:
A) Track a new column in the users table called 'available_points' and UPDATE the column by incrementing or decrementing this field when they add or spend points and derive the available points with the query "SELECT available_points FROM users WHERE user_id = $id"
or
B) Use the existing columns and derive the available points with the SELECT/calculation:
"SELECT (SELECT SUM(points_purchased) FROM point_transactions WHERE user_id = $user_id) - (SELECT SUM(prize_amunt) FROM prizes WHERE user_id = $user_id) AS points_available"?
Please explain why your choice would be more efficient or yield better performance over the alternative.
In order to determine the number of available points per user, without using a view, should I:
A) Track a new column in the users table called 'available_points' and UPDATE the column by incrementing or decrementing this field when they add or spend points and derive the available points with the query "SELECT available_points FROM users WHERE user_id = $id"
or
B) Use the existing columns and derive the available points with the SELECT/calculation:
"SELECT (SELECT SUM(points_purchased) FROM point_transactions WHERE user_id = $user_id) - (SELECT SUM(prize_amunt) FROM prizes WHERE user_id = $user_id) AS points_available"?
Please explain why your choice would be more efficient or yield better performance over the alternative.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
hmmm @nfaria so it would be like Credits, Debits and (Credits - Debits = ) Balance.
yes, you should do some testing to see how much time you gain in reading data and how much time you loose on writing.
But I think you would have a much faster reading for a tiny bit slower insert.
But I think you would have a much faster reading for a tiny bit slower insert.
This will work faster than a large SELECT SUM query because it will access fewer rows. It will be more technologically defensible because even a single query error could corrupt the available points values.
That's my $0.02. HTH, ~Ray