2

I've got a table in MySQL lets just say for example its got two fields Username, GameName and Score. I want to calculate the rank of a user for an indivudal game name so I could do the query

SELECT * FROM scores WHERE `GameName` = 'Snake' ORDER BY `Score` DESC

to get a list of all users in order of highest to lowest and assign a number to each user.

But is there an easier way to get the rank for an indivdual user rather than selecting the entire table as that doesn't seem too efficient.

Thanks

Jared Farrish
  • 48,585
  • 17
  • 95
  • 104
Jack
  • 3,769
  • 6
  • 24
  • 32
  • My thought is that if you could do this in MySQL, it would be by using a temporary table, subselect, and/or calculating row number with a Rank alias. The trick would be getting MySQL to populate a temporary table/subquery and then allow you to select only one result (the individual player's highest rank). That would be what I'm not sure how to do, without trial and error and testing. See: http://jimlife.wordpress.com/2008/09/09/displaying-row-number-rownum-in-mysql/ – Jared Farrish Jan 30 '11 at 20:46
  • I did understand you right that you're looking for a user's overall highest score rank within all players, and not that player's highest score, right? – Jared Farrish Jan 30 '11 at 20:57

8 Answers8

4

If you want overall rankings, you unfortunately have to sort the whole table. Simply put, you cannot know someone's rank in the table without knowing the other ranks in the table.

That said, if you are worried about performance, there's a fairly easily solution here - cache the result of your ranking query (maybe into another a MySQL table!), and query that for all your reads. When someone posts a new score, recalculate your temporary table. You can periodically flush all records under a certain rank (say, anyone ranking under 100 gets removed from the scores table) to keep recomputations fast, since nobody would ever climb in rank after being knocked down by a higher score.

# Create your overall leaderboards once
create table leaderboards (rank integer primary key, score_id integer, game varchar(65), user_id integer, index game_user_id_idx (game, user_id))


# To refresh your leaderboard, we'll query the ranks for the game into a temporary table, flush old records from scores, then copy
# the new ranked table into your leaderboards table.
# We'll use MySQL's CREATE TABLE...SELECT syntax to select our resultset into it directly upon creation.
create temporary table tmp_leaderboard (rank integer primary key auto_increment, score_id integer, game varchar(65), user_id integer)
  select ID, GameName, UserID, from scores where GameName = '$game' order by score desc;

# Remove old rankings from the overall leaderboards, then copy the results of the temp table into it.
delete from leaderboards where game = '$game';
insert into leaderboards (rank, score_id, game, user_id)
  select rank, score_id, game, user_id from tmp_leaderboard;

# And then clean up the lower scores from the Scores table
delete from scores join tmp_leaderboard on scores.id = tmp_leaderboard.score_id, scores.GameName = tmp_leaderboard.game where tmp_leaderboard.rank < 100;

# And we're done with our temp table
drop table tmp_leaderboard;

Then, whenever you want to read a rank for a game:

select rank from leaderboards where game = '$game' and user_id = '$user_id';
Chris Heald
  • 61,439
  • 10
  • 123
  • 137
  • I knew it was possible, I just didn't know how exactly. Would this really be faster than just limiting the query to what was needed (top 100) and letting PHP iterate? I've never needed temp tables, so I'm just not sure. Also, this could be a stored procedure, right? – Jared Farrish Jan 30 '11 at 21:26
  • It would likely be extremely fast if you keep the part that flushes old scores from the scores table. A PHP solution will probably be acceptably fast as long as you have indexes on `game` and `score`, but it will very certainly be slower, since you'll be recomputing rank and reading 100 rows out of that table for every read, rather than 1 row out of 1 table for every read. This could easily be a stored procedure, and you could rewrite it to skip the select into temp table step if you wanted to go that route. – Chris Heald Jan 30 '11 at 21:28
  • Also, you've got leaderboars instead of leaderboards in the last query. – Jared Farrish Jan 30 '11 at 21:30
2

You can't get away from reading a lot of the data in the table - but you don't need to haul it all the way back to your processing script:

SELECT COUNT(*)
FROM scores 
WHERE `GameName` = 'Snake'
AND user=$some_user;

(since you probably want the first person to have a rank of '1' rather than '0', increment the result).

However if you need to run the query often, it's worth maintaining a materialized view of the sorted results.

symcbean
  • 47,736
  • 6
  • 59
  • 94
1

My one query solution:

select @rank:=@rank+1 AS Rank,L1.* from 
    (select @rank:=0) as L2,
    (select i.*,count(*) as sum 
        FROM 
        transactions t
        LEFT JOIN companies c on (c.id = t.company_id)  
        LEFT JOIN company_industries ci on (c.id = ci.company_id)  
        LEFT JOIN industries i on (ci.industry_id = i.id)
        GROUP by i.name 
        ORDER BY sum desc ) as L1;
c9s
  • 1,888
  • 19
  • 15
1

get the user id from your users table and use it in your query


SELECT * FROM scores WHERE `GameName` = 'Snake' 
and `youruseridfield` = '$useridvalue'
ORDER BY `Score` DESC
Muhamad Bhaa Asfour
  • 1,075
  • 3
  • 22
  • 39
1

SELECT * FROM scores WHERE 'GameName' = 'Snake' && userID = '$userID' ORDER BY 'Score' DESC

Bryan
  • 347
  • 1
  • 10
  • 1
    This won't get you a ranking, because it will only order the results after they've been pared down by the `where` clause. He wants overall rankings - a leaderboard. – Chris Heald Jan 30 '11 at 21:02
1

Be interesting to see if there were a way to get the rank in MySQL, but here is how you could do it in PHP:

function getRank($user, $game, $limit=50) {
    $sql = "
SELECT @rank:=@rank+1 AS Rank, User, GameName
FROM scores, (SELECT @rank:=1) AS i
WHERE `GameName` = '$game' 
ORDER BY `Score` DESC
LIMIT 0, $limit
";

    $result =  mysql_query($sql);

    while ($row = mysql_fetch_assoc($result)) {
        if ($row['User'] == $user) {
            return $row['Rank'];
        }
    }

    return -1;
}

Note, I put the limit in there because otherwise you will not get but 30 results back. And it returns -1 if the player is unranked.

Jared Farrish
  • 48,585
  • 17
  • 95
  • 104
0

You should add indexes to GameName and Score. (And don't forget to escape the GameName before inserting in the query => mysql_real_escape_string). @Bryan: shouldn't it be "AND" than "&&" ?

ipsum
  • 1,042
  • 7
  • 17
  • I don't see a problem with &&. I use it in my php. http://stackoverflow.com/questions/2803321/and-vs-as-operator – Bryan Jan 30 '11 at 20:46
  • There isn't any problem with && in MySQL, they're both valid and act as aliases for each other. Many people by convention use AND in MySQL, but that's not written in stone. http://dev.mysql.com/doc/refman/5.0/en/logical-operators.html#operator_and – Jared Farrish Jan 30 '11 at 20:55
  • Also, this doesn't answer the question, it's just meant to speed the lookup itself. It belongs more as a comment to the original question. – Jared Farrish Jan 30 '11 at 20:56
0

Following query is using rank function, this can be materialised as well:

WITH game_rank AS(
    SELECT 
        user_id, 
        gameName, 
        RANK() OVER (
            PARTITION BY gameName
            ORDER BY score DESC
        ) order_value_rank
    FROM
        scores
)
SELECT 
    * 
FROM 
    game_rank
WHERE 
    gameName = "Snake";
bindasarpan
  • 3
  • 1
  • 5