需要帮助进行玩家评分计算的数据库/查询设计
我有一个游戏，每轮玩家都会获得奖励，并按总奖励计算评级。 我使用这样的模式：
1）表game_result（user_id，prize，game_date）用于单独存储游戏结果（我需要它进行统计）
2）表 rating（user_id，total_prize，total_games）用于存储评级数据并且total_prize、total_games值在每场游戏结束时更新
3）我需要快速找到用户的总奖金和评级位置（以在游戏期间显示）。现在我使用这种查询的一种
SET @rank=0;
SELECT @rank:=@rank+1 AS rank, user_id, total_prize, total_games FROM rating order by total_prize where user_id = ?
，但它为每个查询使用评级表，而且速度太慢。问题是，在每场比赛之后，单个玩家只更改一行（使用total_prize、total_games），但要找到他的新订单位置，我需要诉诸整个表，并且性能不可行。
是否可以重新组织表以提高此操作的性能？
谢谢！
I have a game, where for each round player gets a prize and rating by total prize is calculated.
I use such schema:
1) table game_result (user_id, prize, game_date) is used to store results of the games separately (I need it for my statistics)
2) table rating (user_id, total_prize, total_games) is used to store rating data and total_prize, total_games values are updated at the end of each game
3) I need to find user's total prize and position in rating fast (to display it during the game). Now I use a kind of this query
SET @rank=0;
SELECT @rank:=@rank+1 AS rank, user_id, total_prize, total_games FROM rating order by total_prize where user_id = ?
but it resorts rating table for each query and is too slow. The issue is that after each game only one row is changed (with total_prize, total_games) of a single player, but to find his new order place I need to resort the whole table, and performance is not feasible.
Is it possible to reorganize tables to improve performance of this operation?
Thanks!
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（1）
（或者进行单独的查询并获取用户的total_prize，加上您需要的其他列，然后对其进行计数。）
当然，请确保在total_prize上有一个索引。
（您可能需要 >= 来代替，具体取决于您所说的排名的含义。）
(Or do a separate query and get the total_prize for the user, plus the other columns you need, then count it.)
Make sure to have an index on total_prize of course.
(You may need >= instead, depending on what you mean by rank.)