MySQL and manipulating player ratings

Question

MySQL

Let's say there is a table with players, where each player has a "score" field.

And we want to create an overall rating of players sorted by this field.

The question is, is it possible to somehow get the position of a given player in this rating? Those. there is a player with score=12 and when sorted by this field, he will be in the list of all players in 50000th place.

Is it possible to somehow determine this very place with a slight movement of the hand?

Or is there only one option here - once every N time, pass through the sorted table and remember the ratings?

Data volumes are planned from 100 thousand users to a million. How are sorted ratings created with such large volumes?

Answer 1

Let there be a table table: id, PlayerName, score
there is data:
1 player1 12
2 player2 15
3 player3 14
4 player4 14
5 payer5 15
6 player6 12
SELECT COUNT(DISTINCT score) as position FROM table WHERE score >= (SELECT score FROM table WHERE PlayerName='player1' LIMIT 1)
The result will be:
SELECT COUNT(DISTINCT score) FROM table WHERE score >= 12
15
count = 14 = 3 = position
12

Answer 2

UPDATE table SET spore=spore+1/rand(1,10000000000000) takes
and silently adds a fluctuation after the decimal point.
If you have spore - defined up to 0.001 for example - define the fluctuation as 0.001\rand
Alternatively, fix this fluctuation to a specific user when creating this very user.
Well, those are the details.
The main thing is that you can always do
SELECT COUNT(*) FROM table WHERE spore<?
And get the exact value, even if we are looking for spore=12 and these 12 are several hundred thousand

Answer 3

Further than the fiftieth position, it is not so important - what exactly is the position of the gamer. Therefore, it makes sense to frequently update the “honestly” rating for the top50, where there is a cutting between the most ardent fans of the game for the first place.
further - the movement in the rating will not be sharp and will be a multiple of the time of one game (the interval for updating the rating of one player) and will not jump beyond the average number of points per session. Thus, the ratings can be updated "by eye" and beat them into groups.
That's if there are millions of players.
But there will be a maximum of hundreds online at the same time, so don't worry too much about optimizing this table.
And when thousands will play at the same time, you will refactor the code and databases, or maybe change the server.

Answer 4

Offtopic: if there are really so many users planned, then it is better to use division into divisions / leagues than to show a “clean” place. Few people are pleased to see that he is in 239676 place.

Answer 5

You can create a variable.

SELECT *, (@position:[email protected]+1) FROM `users`, (SELECT @position:=0) `a` WHERE 1

Answer 6

But it’s better “Or is there only one option here - once every N time, pass through the sorted table and remember the ratings?”