Query a ranking number extremely fast in MySQL?

Question

Database has the following columns: id, uuid, trophies

In order to get the ranking number of a player based on trophies, I currently use this query:

 SELECT rank FROM (SELECT (@num := @num +1) as rank, uuid FROM User, 
 (SELECT @num := 0) x ORDER BY trophies DESC) as TMP WHERE 
 uuid=UNHEX('7c6d37759dd749349f2c32303a367226');

However, the query takes around 1.3 seconds due to the size of the database (300,000 players).

Is there a more efficient method on getting the rank number in MySQL?

Here's the SHOW CREATE TABLE statement as requested:

CREATE TABLE `User` (`id` int(11) NOT NULL AUTO_INCREMENT, 
`uuid` binary(16) DEFAULT NULL, `trophies` int(11) DEFAULT NULL, 
PRIMARY KEY (`id`), UNIQUE KEY `uuid` (`uuid`)
) ENGINE=InnoDB AUTO_INCREMENT=57917982 DEFAULT CHARSET=latin1

EXPLAIN statement:

Please don't use Old comma based Implicit joins and use Modern [Explicit `Join` based syntax](https://stackoverflow.com/q/5654278/2469308) — Madhur Bhaiya, Nov 10 '18 at 04:55
Please share the indexing details on your database. Do you have an integer Primary key ? — Madhur Bhaiya, Nov 10 '18 at 04:55
@MadhurBhaiya Yes, I have a primary key. id INT NOT NULL AUTO_INCREMENT PRIMARY KEY — Bob, Nov 10 '18 at 05:50
@Bob do you have any indexes defined ? Please edit the question and add `SHOW CREATE TABLE` statement result. Also, `EXPLAIN` statement results can be helpful as well. — Madhur Bhaiya, Nov 10 '18 at 05:56
@Bob please add indexing to `trophies` column and check again. — Madhur Bhaiya, Nov 10 '18 at 06:15
@MadhurBhaiya added. same thing, takes 1.3 seconds (KEY `trophies` (`trophies`)) — Bob, Nov 10 '18 at 16:30

score 1 · Accepted Answer · answered Nov 10 '18 at 18:52

1

Found the solution:

This query is at least 10,000x faster.

SELECT 1 + (SELECT COUNT(*) FROM User a WHERE a.trophies > b.trophies) AS rank 
FROM User b WHERE uuid = UNHEX('7c6d37759dd749349f2c32303a367226') ORDER BY 
rank LIMIT 1;

answered Nov 10 '18 at 18:52

Bob

1,201
1
9
22

Query a ranking number extremely fast in MySQL?

1 Answers1