Results 1 to 8 of 8
  1. #1
    Join Date
    Aug 2004
    Posts
    6

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

    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. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by eran View Post
    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?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Aug 2004
    Posts
    6
    Quote Originally Posted by r937 View Post
    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

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    that doesn't look like it returns results for a specific user
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Aug 2004
    Posts
    6
    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.

  6. #6
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  7. #7
    Join Date
    Aug 2004
    Posts
    6
    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?

  8. #8
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by eran View Post
    will it still be less resource demanding then creating the table with indexes I suggested
    why don't you test both methods and let us know what you find...

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •