Need advise on how to structure a table for a certain task

Databases
1

Hi,
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:
SET @a=0;
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

Thanks.

2

ScallioXTX:

I don’t have a user score column… I have many scores that I sum together the get the total score.

Here is the query i use:

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

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.

Thanks for pointing out the issue of multiple users with the same score. I’m aware of it and at the moment it’s not really a problem.

3

That sounds like a heavy job for cron!
You could also use this


SELECT
  COUNT(*) + 1 AS Rank
FROM
  users
WHERE
  score > $userscore

Where $userscore is the score of the user you want to show the rank for.
Note that your query does not take into account that multiple users may have the same score. If bob has 20 points and alice has 20 points, are they both first? If one of them first and the other second? Why?