Unanswered: Need advise on how to structure a table for a certain task
I'm working on a site where users get scores for certain things. I can easily run a query that gives me all the user's scores. The output looks like so:
User name | Score
User A 25
User B 23
User C 15
and so on...
I want to be able to give each user their Ranking within this table. I added this:
SELECT (@a:=@a+1) as Rank
and now i also get the rank of the user
User name | Score | Rank
User A 25 1
User B 23 2
User C 15 3
But, I want to be able to show the rank of each user on their page, so it makes no sense running this query and looping through the results until I find the row for that specific user.
What I thought about was creating a table to hold the results of the query that produces the illustrated results. If i have an index on the user name i will be able to easily pull just one record. In order to stay current this table will be deleted and then populated by a cron every minute. The problem with this approach is that if someone looks at user pages while the table is still half empty and being filled is that they won't get any results.
I'd love to get some views and ideas on how it is best to approach this issue
could you show me the query you're running for a specific user?
one table has games in it (content_type_game), another has results(content_type_results). The query joins the two tables and groups by user ID while summing the scores this user got for each result entry:
SET @a=0; SELECT SUM(r.field_comp_points_value) as SUM, (@a:=@a+1) as Rank, r.field_user_uid FROM content_type_game as g LEFT JOIN content_type_results as r on g.nid=r.field_game_nid GROUP by r.field_user_uid ORDER BY sum DESC
Oh no it doesn't, it only returns all the users scores and their rank. It's good enough for a table that shows the ranking of all users and their scores. The advice that I need is how to get the info for a specific user.
the first solution that comes to mind is running this query and looping through it until it finds the specific user. but if for example there will be 50,000 user records and given that this is needed every time a user profile is rendered it could kill the site resource wise.
So what i was thinking was doing is storing the results of that query in a table that has an index for the user id and then pulling the records of a single user shouldn't be much of a problem.
Problem would be refreshing the content of the table every minute. That would require emptying the table and refilling it, but during this procedure if a user profile is rendered it won't get the right info as the table is not complete.
since you don't have a query for a single user, i had to make it up
here ya go...
, ( SELECT COUNT(*)+1
, SUM(field_comp_points_value) AS s
) AS m
WHERE s > t.points
) AS rank
FROM ( SELECT SUM(field_comp_points_value) AS points
WHERE field_user_uid = 937 ) AS t
your query is just what I was looking for. I didn't even know it was possible to do such Mysql queries, so I also learned a few things
btw, let's say this query is run many many times (suppose there many simultaneous profile pages views) will it still be less resource demanding then creating the table with indexes I suggested and pulling one record out of it for each view?