What is a good design for Ranking System

database, mysql, ranking

Solution

SET @rownum := 0;

SELECT rank, userId, point 
FROM (
       SELECT @rownum := @rownum + 1 AS rank, userId, point
       FROM user ORDER BY point DESC
     ) 
as result WHERE userId = xxxxxxxx

Problem

I have a `USER` table, that has `userId` and `point` fields. At runtime, I want to know what is a ranking of a particular user base on their point. What is the best way to accomplish this: 1: Query all users into a list. Sort the list base on point and do a binary search to find the ranking of that user. Sound like a bad idea here. 2: Is it possible to accomplish these tasks by creating database queries? I expect 2000-5000 users.

Original source