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

eran · #1 (**permalink**) 06-05-10, 11:59

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.

r937 · #2 (**permalink**) 06-05-10, 12:05

Quote:

Originally Posted by eran

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.

i agree, that wouldn't be the best way to do it

could you show me the query you're running for a specific user?

eran · #3 (**permalink**) 06-05-10, 21:49

Quote:

Originally Posted by r937

i agree, that wouldn't be the best way to do it

could you show me the query you're running for a specific user?

Sure,
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

r937 · #4 (**permalink**) 06-05-10, 22:21

that doesn't look like it returns results for a specific user

eran · #5 (**permalink**) 06-06-10, 05:14

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.

This is where i need advice.

r937 · #6 (**permalink**) 06-06-10, 07:01

since you don't have a query for a single user, i had to make it up

here ya go...

Code:

SELECT t.points
     , ( SELECT COUNT(*)+1
           FROM (
                SELECT field_user_uid
                     , SUM(field_comp_points_value) AS s
                  FROM content_type_results
                GROUP
                    BY field_user_uid
                ) AS m
          WHERE s > t.points
       ) AS rank
  FROM ( SELECT SUM(field_comp_points_value) AS points
           FROM content_type_results 
          WHERE field_user_uid = 937 ) AS t

eran · #7 (**permalink**) 06-07-10, 06:44

Thanks r937! i really appreciate your help.

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?