Best practices storing, modifying and retrieving user reputation data

Suppose you have a MySQL table with 500,000 rows. Each row has a field with a numerical value in it that we'll call points. Every time the user does something on the site the points are updated in a positive or negative direction based on the activity. It's easy to put an index on the points field and retrieve a sorted list of users by points. However, what to do when we need to know where one random row falls in the list? If you were to write the code to pull up a user's profile and display their "rank" in relation to the other users, what system would you put in place to mak开发者_JAVA百科e that information available efficiently, ie without selecting the whole users table.

Thanks for the help.

Does just doing the direct approach (using the index you already have) work? I'm thinking of something like:

select count(*) from user_points where points > x

where x is the number of points for the user you are looking at.