Recommendations for storing, modifying, and retrieving user reputation data

Question

Suppose you have a MySQL table with 500,000 rows. Each row has a field with a numerical value, which we will call points. Each time a user does something on the site, points are updated in a positive or negative direction based on activity. It’s easy to put an index in a points field and get a sorted list of users by points. However, what to do when we need to know where one random line is in the list? If you had to write code to pull out a user profile and display its “rank” in relation to other users, which system would you create to make this information available efficiently, that is, without selecting the entire user table.

Thanks for the help.

Answer 1

Does only a direct approach work (using the index that you already have)? I am thinking of something like:

select count(*) from user_points where points > x

where xis the number of points for the user you are looking for.