How best to organize the miscalculation of the user's place in the rating table?

Question

MongoDB

Hello, it is necessary to form a top of the best 100 users, while each user will display his place in the top. It is clear that it is better to cache the rating, so as not to calculate everything all over again with each new request.

The question is different - how best to assign each user a number of his seat? Just go through the cycle and assign each one its place? It is necessary that all users be shown their place in the top, or at least an approximate one. How to do this most efficiently if there are thousands of users in the database? Thanks in advance for your advice!

Answer 1

You can do without seat numbers.
Enter the score attribute and enter the calculated real value of the rating there for some operations. For example, when writing a post. When listing, sort in descending order by score.

Answer 2

I don't see any problems. The best "score" by some value - what you write into it is up to you. For example, the number of comments per day, week or month. Depends on your ranking.
1 - Select 100 records from the database, sort by score in ascending order and update date in descending order
2 - Create a one-to-one RATING table with any fields
3 - Add the selected records in the resulting
ID order to the RATING AUTO_INCREMENT table - it will contain SEAT NUMBERS from 1 to 100 automatically. When joining tables, the user row RATING_ID will already have a rating number. And the rest have NULL
* When updating this table, clear it with a reset of AUTO_INCREMENT
---
Option #2
After receiving 100 records - Just loop around and do an UPDATE on the received IDs in the users table in its separate field NUMBER IN RATING - put the value of the cycle counter there

for ($i = 1; $i <= 100; $i++) {
    "UPDATE `users` SET rating_position = ".$i." WHERE ...
}

# или foreach по выборке $i = 1; снаружи и $i++ внутри цикла

---

it, if in basis of thousand users?

Yes, no matter how many, you will choose only 100 by the "score" value, which you yourself will calculate in advance
---
# Fallback
All users will have a RATING from 0 to 5 - but floating. For example, from 0 to 5000000 and for display, divide by 1000000 - then 4832156 will become 4.832156 can be shown rounded as 4.8 or 5
* But sorting the top by the value of the field in the database 4832156 and the update date
* Then you can choose any number - top 3 (5, 10, 100, 1000...) at any time
** Storing a fraction in a database like 4.8321563465464531354655.... is not recommended, but you can :)