SQL queries: Sample optimization, how are statistics implemented?

Question

SQL

There is a user rating table: users_stat(id_user, total - number of rating points). The goal is to determine the place occupied by a certain user in the ranking.
Googling, I found the following solution:
I make two queries to the database:
First, I get the rating of the current user (user id - 4):
SELECT total FROM users_stat WHERE id_user = 4;[It turns out to be equal to, for example, 200 rating points and use it in the next request]
Second, I get this user's place in the rating ( user id - 4):

SET @x:=0;
SELECT num FROM (SELECT @x:[email protected]+1 num, id_user, total FROM users_stat WHERE total > 200-1 ORDER BY total DESC) num WHERE id_user = 4

As can be seen from the nested query, here all records are displayed and each number is assigned from the variable x, which seems to me to be an unoptimized and resource-consuming operation, for example, in the condition of a low rating of the current user, and therefore he is at the end of the list of the entire rating, consisting , for example, from 5000 users and more.
Is it possible to somehow optimize this operation, or is there a better way to implement ranking on the site?

Answer 1

It seems to me that even this code will run in O(n)

SELECT count(*) FROM users_stat as u, user_stat as v 
WHERE u.total > v.total AND
v.user_id == 777;