How to find out the user's place depending on the points scored?

Question

MySQL

Hello!

Imagine a table with the following structure and one million rows.

CREATE TABLE `table` (
  `user_id` int(11) NOT NULL DEFAULT 0, 
  `points` int(11) NOT NULL DEFAULT 0,
  PRIMARY KEY (`user_id`)  
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4

Values ​​in points are constantly changing, they can increase, they can decrease.
Whoever has the highest points value - has the first place.
Whoever has the smallest points value is in last place.

1. Is it possible to find out the current location of any user with one request?
2. Is it possible to solve this problem without a full table scan (some kind of magical virtual field)?

Thanks for answers!

Answer 1

SELECT COUNT(*) + 1
  FROM `table`
  WHERE `points` > (
    SELECT `points`
      FROM `table`
      WHERE `user_id` = :userId
      LIMIT 1
  )

To speed up it is worth creating an index on the points field.
Only there is a question, if there are two players with the same number of points, then what are their places?