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 make that information available efficiently, ie without selecting the whole users table.
假设有一个包含50万行的MySQL表。每一行都有一个具有数值的字段，我们称之为点。每次用户在站点上做一些事情时，基于该活动，这些点会以正方向或负方向更新。在points字段上放置索引并按点检索用户的排序列表很容易。然而，当我们需要知道一个随机行落在列表中的位置时，该怎么办呢?如果你编写代码来提取用户的个人资料并显示他们与其他用户的“等级”，你会使用什么系统来有效地获取信息，即不选择整个用户表。
Thanks for the help.
谢谢你的帮助。
1 个解决方案
#1
4
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.
其中x是你要查看的用户的点数。
#1
4
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.
其中x是你要查看的用户的点数。