Mysql query statement index tuning
===

I'm working on how to implement a global leaderboard for a Facebook racing game my company has released.  What I'd like to do is be able to store the player's userID and their time for a race.  I've got a table like the one below:

    +--------+-----------------------+------+-----+---------+-------+
    | Field  | Type                  | Null | Key | Default | Extra |
    +--------+-----------------------+------+-----+---------+-------+
    | userID | mediumint(8) unsigned | NO   | PRI | 0       |       |
    | time   | time                  | YES  | MUL | NULL    |       |
    +--------+-----------------------+------+-----+---------+-------+

And a sample set of data like so:

    +--------+----------+
    | userID | time     |
    +--------+----------+
    | 505610 | 10:10:10 |
    | 544222 | 10:10:10 |
    | 547278 | 10:10:10 |
    | 659241 | 10:10:10 |
    | 681087 | 10:10:10 |
    +--------+----------+

My queries will be coming from PHP.  Now if I assume that I've got unlimited resources, what I could do is this:

    $q1 = "Set @rank := 0";
    $q2 = "select @rank:=@rank+1 as rank,userID,time from highscore order by time asc where userID=$someUserID";
    $q3 = "Set @rank := 0";
    $q4 = "select @rank:=@rank+1 as rank,userID,time from highscore order by time asc where rank > $rankFromSecondQuery - 10 and rank < $rankFromSecondQuery + 10";

But I don't have unlimited resources and I have to be able to scale this to support millions of players since it's going into a social game on Facebook.  So after spending a few days crawling all over Google, I've been able to get my queries down to this:

    $q1 = "select rank,userID,time from (select @rank:=0) r, (select @rank:=@rank+1 as rank,userID,time from highscore order by time asc) as myMine where userID=$someUserID"
    $q2 = "select rank,userID,time from (select @rank:=0) r, (select @rank:=@rank+1 as rank,userID,time from highscore order by time asc) as myMine where rank > $rankFromFirstQuery - 10 and rank < $rankFromSecondQuery + 10";

This works, but it isn't very pretty with an average runtime per query of approx 2.3 seconds.  Here's the explain extended block:

    mysql> explain select rank,userID,time from (select @rank:=0) r, (select @rank:=@rank+1 as rank,userID,time from highscore order by time asc) as myMine where userID=11345;
    +----+-------------+------------+--------+---------------+----------+---------+------+---------+----------------+
    | id | select_type | table      | type   | possible_keys | key      | key_len | ref  | rows    | Extra          |
    +----+-------------+------------+--------+---------------+----------+---------+------+---------+----------------+
    |  1 | PRIMARY     | <derived2> | system | NULL          | NULL     | NULL    | NULL |       1 |                |
    |  1 | PRIMARY     | <derived3> | ALL    | NULL          | NULL     | NULL    | NULL | 2500000 | Using where    |
    |  3 | DERIVED     | highscore  | index  | NULL          | idx_time | 4       | NULL | 2500842 | Using index    |
    |  2 | DERIVED     | NULL       | NULL   | NULL          | NULL     | NULL    | NULL |    NULL | No tables used |
    +----+-------------+------------+--------+---------------+----------+---------+------+---------+----------------+

So ultimately, what I'd really like to be able to do is get this down into just one query such that I can temper the execution time with a high CPU server or two.  Either that or I'd like to figure out a way to just hit an index on the part of the query that's associated with the derive3 line in the explain block that's hitting on all the rows in the table.  Anyone have any ideas how to either get my two queries listed above to hit on an index so the execution time decreases or how to combine the two queries into one so I only have to suffer with a painful execution time once? I'd also be open to tuning/optimizations like tweaking MySQL config settings and/or using something like Percona if anyone has experience using something like that and would like to share their experiences.
