Single Column Counter VS Multi Column calculation
Posted on 2011-09-07
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"
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.