MySQL: Should I keep a running total column in one table instead of summing a second table

MySQLoptimization

I have 3 tables named users, achievements, and userAchievements. The users table contains information like username, password, e-mail, etc… and the achievements table contains a list of possible achievements and how many points they are worth, and userAchievements lists all achievements completed by all users.

users                        userAchievements           achievements
--------------------------   ------------------------   ------------
username | email             username | achID           achID | points
johnb     john@aol.com       johnb      ach001          ach001  5
daver     dave@yahoo.com     johnb      ach002          ach002  10
miket     mike@hotmail.com   daver      ach001          ach003  15

In my app, once the user is logged in, some information about them from the users table is displayed along with their total achievement points. I'm doing this using two SELECT queries, one to pull information from users, the other to sum the points of all the achievements they have completed. In order to cut it down to one SELECT query, I'm thinking of adding an "achievementTotal" column to users which gets added to by the point value every time an entry in userAchievements is made.
Is it possible to get everything I need with just one SELECT query without doing that? Or is there an even better way to implement what I am trying to do?

Best Answer

SELECT username, email, achID, SUM(points)
FROM users                        
NATURAL JOIN userAchievements           
NATURAL JOIN achievements
WHERE username = 'johnb'
GROUP BY username, email, achID
WITH ROLLUP
HAVING GROUPING(username) + GROUPING(email) + GROUPING(achID) IN (0, 3)

fiddle

is there a way to get the output like this? 3 columns "username | email | SUM(points)" with 1 row of "johnb | john@aol.com | 15"

SELECT username, email, SUM(points)
FROM users                        
NATURAL JOIN userAchievements           
NATURAL JOIN achievements
WHERE username = 'johnb'
GROUP BY username, email;
Related Question