MySql : Get Ranks Heavy Query Optimization - help needed

Question

我的数据库中只有下表:

分数表

Field...............|........Type.........|..Null..|..Key..|...........Default.................|..Extra
...............................................................................................................................................
id....................|...........int(20)...|..NO...|..PRI..|..NULL.............................|..auto_increment
scr..................|...........int(20)...|..NO...|..........|.....0.................................|..........................
player_name..|..varchar(150)..|..NO...|..........|..NULL............................|.........................
location...........|.varchar(5).......|..NO...|..........|..NULL............................|.........................
DateUpdated..|..timestamp......|..NO...|..........|CURRENT_TIMESTAMP|..on-update

目前我在 id 字段上有一个索引。

在我的案例中，我正在处理 100 万条记录。每个玩家在数据库中只会有一条记录。

我想检索名为 john、id 为 682 的特定玩家的全局排名，如下例所示:

Rank..........Id.......SCR......DateUpdated
-------------------------------------------------------------
15257...53264.......62........2013-3-10 16:45:37
15258...3533.........62........2013-3-10 16:45:37
15259...7283.........62........2013-3-13 16:45:37
15260...386...........61........2013-3-09 18:55:25
15261...78252.......61........2013-3-10 13:33:21
15262...682...........61........2013-3-10 16:45:37 <== this is our player
15263...9263.........61........2013-3-10 16:45:37
15264...7263.........61........2013-3-10 16:56:25
15265...7826.........60........2013-3-10 12:26:37
15266...9276.........60........2013-3-10 15:22:37
15267...932872.....60........2013-3-13 11:45:37

玩家出现在中间，上面有 5 个玩家，下面有 5 个玩家请注意，排名按 scr 排序，然后按 DateUpdated 排序

这是我的查询，它带来了这些结果:

 SELECT id,
  scr,
  player_name,
  location,
  dateupdated,
  rank
FROM
  (SELECT id,
    scr,
    player_name,
    location,
    dateupdated
  FROM scores
  WHERE id IN
    (SELECT id
    FROM
      (SELECT id
      FROM scores
      WHERE id IN
        (SELECT id
        FROM scores
        WHERE scr >=
          (SELECT scr FROM scores WHERE id = 1140188
          )
        AND id     != 1140188
        AND id NOT IN
          (SELECT id
          FROM scores
          WHERE scr IN
            (SELECT scr FROM scores WHERE id = 1140188
            )
          AND dateupdated >=
            (SELECT dateupdated FROM scores WHERE id = 1140188
            )
          )
        ORDER BY scr ASC,
          dateupdated ASC
        )
      ORDER BY scr,
        dateupdated ASC limit 0,
        5
      ) AS t
    UNION ALL
    SELECT id FROM
      (SELECT id FROM scores WHERE id = 1140188
      ) AS g
    UNION ALL
    SELECT id
    FROM
      (SELECT id
      FROM scores
      WHERE id IN
        (SELECT id
        FROM scores
        WHERE scr <=
          (SELECT scr FROM scores WHERE id = 1140188
          )
        AND id     != 1140188
        AND id NOT IN
          (SELECT id
          FROM scores
          WHERE scr IN
            (SELECT scr FROM scores WHERE id = 1140188
            )
          AND dateupdated <
            (SELECT dateupdated FROM scores WHERE id = 1140188
            )
          )
        ORDER BY scr ASC,
          dateupdated ASC
        )
      ORDER BY scr DESC,
        dateupdated ASC limit 0,
        5
      ) AS s
    )
  ORDER BY scr DESC,
    dateupdated ASC
  ) AS A
LEFT JOIN
  (SELECT l.id             AS id2,
    @curRow := @curRow + 1 AS Rank
  FROM scores l
  JOIN
    (SELECT @curRow := 0
    ) r
  ORDER BY scr DESC,
    dateupdated ASC
  ) AS B ON A.id = B.id2;

但这个查询在我的本地计算机上大约需要 8 秒，并且消耗大量资源，在 Web 服务上实现此查询最终会导致灾难。

任何人都可以在这里提供任何提示，甚至欢迎全新的查询..

请帮忙!!!!

Answer 1

SELECT
all_ranked.*
FROM (select rank 
      from (SELECT l.id             AS id2,
            @curRow := @curRow + 1 AS Rank
            FROM scores l
            JOIN
                (SELECT @curRow := 0) r
                ORDER BY scr DESC, dateupdated ASC
            ) AS B) 
      where B.id=1234567) as rank_record, <--- just one record - value of rank
      (SELECT l.id             AS id2,
            @curRow := @curRow + 1 AS Rank
            FROM scores l
            JOIN
                (SELECT @curRow := 0) r
                ORDER BY scr DESC, dateupdated ASC
            ) AS all_ranked <--- all ranked users
where all_ranked.rank>=rank_record.rank-5 and all_ranked.rank>=rank_record.rank+5;