Udi Idan Udi Idan - 1 month ago 19
SQL Question

Optimizing rank() query

I'm using the following query, within a stored procedure, to get user's ranking.

While trying to optimize the database, I’m trying to determine if this query can be optimize as it is the most used query in my database.

id column is indexed.

SELECT
@p_rank = all_time_rank
FROM
(SELECT
user_rankings.*,
RANK() OVER (ORDER BY score DESC) AS all_time_rank
FROM
user_rankings) t
WHERE
id = @p_id;


Is there any way to get this query to perform better?

Answer

You can phrase this as:

SELECT @p_rank = COUNT(*) + 1
FROM user_rankings ur
WHERE ur.score > (SELECT ur2.score FROM user_rankings ur2 WHERE ur2.id = @pid);

You would want an index on user_rankings(id, score) and user_rankings(score).

I think this will have somewhat better performance.