   A better way for player's numerical ranking?
  Currently, we are doing the players' numerical ranking by checking the player's rank when he logs in (When the player log, we check his points then do Count(Select * where points > player points) and then add 1).We realize now that this was ... not wise. It didn't show the effect when the number of players was small. But now that we have 500K+ player, it is increasing the DB load from 0 to 300 something.The problem seems to be in the fact that the table holds all the players points, and the points gets updated with every round (every 4 players play in a room, card game). So when a player logs in, it locks the table to do the Count(), and at the same time, other threads are trying to update the players scores. With a few players, this is not an issue. But when there is 3000+ player online, its increases the load.The first idea is to use a 2nd table that holds the players ranks and update them once a day during low traffic hours.The 2nd idea, which is the one I want to verify, is to create some sort of a script that acts as an iterator. It will follow an in-place sorting algorithm. We add an indexed rank column and sort the ranks when the site is down. Then run the site with the new script. The script will access the table and check the first and 2nd rank position, compare the points, and switch rank if needed, then move on (following the sorting algorithm chosen. It should be always an almost-sorted list, since the script will start from the top again once it reach the last position. If there are no changes in points, the script does nothing.The idea here is that only a few rows will be locked at any time (using innodb table), and the rest of the table wont be affected. Plus this way insure that we can update the rank to say, 10 min intervals? instead of once a day.The question is: Any major flow that cant be solved in this 2nd idea?   
  The only difference between your first and second ideas is that the first idea doesn't specify the sorting algorithm, whereas the second idea specifies bubble sort. Bubble sort does approach O(n) complexity in the case where the array is pre-sorted, so it would be asymptotically faster than the O(n log n) performance of, say, qsort on blocks and n-way merge on the results. However, the additional calls to and from the database layer might quickly eat up that difference. I wouldn't bother with it until confirming that the first method was too slow. Quote:The idea here is that only a few rows will be locked at any time (using innodb table), and the rest of the table wont be affected. Plus this way insure that we can update the rank to say, 10 min intervals? instead of once a day.In either case you wouldn't be sorting in-place, so the locking wouldn't really matter.   