我的数据库中只有以下表:
成绩表
字段 ...............|........ 类型 .........|.. 空. . | . .关键 ..|........... 默认的 .................|.. 额外的
...............................................................................................................................................
id ....................|........... int(20) |…. .不…| . . PRI . . | . . NULL .............................|.. auto_increment
可控硅 ..................|........... int(20) |…不 ...|..........|..... 0 .................................|..........................
player_name . . | . . varchar(150) . . | . .没有 ...|..........|.. 零 ............................|.........................
位置 ...........|. varchar (5 ).......|.. 没有 ...|..........|.. 零 ............................|.........................
DateUpdated . . | . .时间戳……| . .没有 ...|..........| CURRENT_TIMESTAMP | . .在更新
目前我有一个索引id字段。
在我的案例中,我正在处理100万条记录。每个球员在数据库中只有一条记录。
我想检索一个名为:John, id: 682的特定球员的全球排名,按照下面的例子:
排名 .......... Id……可控硅……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 <==这是我们的播放器
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服务上实现它将以灾难告终。
任何人都可以在这里提供任何提示,甚至一个全新的查询是受欢迎的。请帮助!!!!!
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;