Efficient Score in InnoDB

[Thomas Rice]

Hi,

I have an InnoDB table "players" with a field for "type" (the type of player) and a field for "score", and the table has over 100,000 entries.

I have an index on (type, score).

There are two thinks I'd like to do -- work out the ranking of a player of a particular type, along with the player that ranks above them and below them.

I've been trying to accomplish these two with these queries:

-----
(to get the ranking of a player with a score of 39848)
SELECT COUNT(*) FROM players WHERE type=1 AND score >= 39848 LIMIT 0, 1;

(to get the person above him)
SELECT * FROM players WHERE type=1 AND score > 39848 ORDER BY score ASC LIMIT 0, 1;

(to get the person below him)
SELECT * FROM players WHERE type=1 AND score < 39848 ORDER BY score DESC LIMIT 0, 1;
-----

These queries work, but they seem quite slow (and EXPLAIN is telling me they examine many thousands of rows each time). I would have thought that the above would only need to examine 2-3 rows given the index?

So, I have a few questions:
- Is examining thousands of rows each time bad (these queries are run very very often)?
- If so, is there a way to structure the query so it only examines a few rows (by adding other indexes or changing the SQL)
- Or if not, is the way to do this to restructure the tables completely, perhaps with a separate "rankings" table that's updated whenever "score" is?

Thanks,

Thomas.