How to Optimize Rank Data in MySQL
Subject:   Rank
Date:   2007-05-22 22:22:02
From:   pilnic
A good article, well explained and useful technique that can also be applied for other cases as well.

But I am not sure if your definition of rank is the most common (I won't say correct as I suppose this is subjective), ie that rank only increments for unique scores.

I have seen scoring systems that will just assign a sequential rank for all rows with ties being resolved arbitrarily such as id or alphabetically (or with secondary metrics) and systems where the same rank is assigned for the same score but the next unique score will get the same rank as with a sequential rank, ie 1 2 2 4.

With your definition of rank if all the gamers above me had the same score and there were nine of them, am I second in the rankings? Then every time the score of any of those above me changed I would drop rank without anyone from below passing me? This is not intuitive and probably just complicates the SQL.

Also, to make the case more interesting you can consider optimazations when the games are multiplayer and two gamers get updated simultaneously (one wins and the other loses points). Or even four gamers per game in partnerships, eg Bridge.

1 to 1 of 1
  1. Baron Schwartz photo Rank
    2007-05-23 05:01:01  Baron Schwartz | O'Reilly Author [View]

1 to 1 of 1