优化慢排序查询
我需要为一个永远耗费时间的排名优化一个查询(查询本身是有效的，但我知道它很糟糕，我刚刚用大量的记录尝试过它，它给出了一个超时)。
我将简要解释该模型。我有3个表:球员，团队和player_team。我有球员，他们可以属于一支球队。听起来很明显，球员存储在球员表中，团队存储在团队中。在我的应用程序中，每个玩家都可以在任何时候切换球队，并且必须管理日志。然而，一名球员在给定的时间被认为只属于一支球队。球员的当前团队是他加入的最后一个团队。
我认为，球员和团队的结构是不相关的。我每个都有一个id列PK。在player_team中，我有：
id (PK)
player_id (FK -> player.id)
team_id (FK -> team.id)现在，每支球队都为每个加入的球员分配了一分。所以，现在，我想要得到前N个球员数量最多的球队的排名。
我的第一个想法是首先从player_team获取当前球员(这是每个球员的一个记录top；该记录必须是该球员的当前球队)。我找不到一种简单的方法来做到这一点(尝试了player_team.player_id的GROUP = player_team.id = MAX(player_team.id)，但这并不能解决问题。
我尝试了一些查询，但都不起作用，但还是成功了。
SELECT
COUNT(*) AS total,
pt.team_id,
p.facebook_uid AS owner_uid,
t.color
FROM
player_team pt
JOIN player p ON (p.id = pt.player_id)
JOIN team t ON (t.id = pt.team_id)
WHERE
pt.id IN (
SELECT max(J.id)
FROM player_team J
GROUP BY J.player_id
)
GROUP BY
pt.team_id
ORDER BY
total DESC
LIMIT 50 正如我所说的，它很有效，但看起来很糟糕，性能也更差，所以我相信肯定有更好的方法。有没有人对此有任何优化的想法？
顺便说一下，我用的是mysql。
提前感谢
添加解释。(抱歉，不确定如何正确格式化)
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY t ALL PRIMARY NULL NULL NULL 5000 Using temporary; Using filesort
1 PRIMARY pt ref FKplayer_pt77082,FKplayer_pt265938,new_index FKplayer_pt77082 4 t.id 30 Using where
1 PRIMARY p eq_ref PRIMARY PRIMARY 4 pt.player_id 1
2 DEPENDENT SUBQUERY J index NULL new_index 8 NULL 150000 Using index回答 5
Stack Overflow用户
发布于 2010-05-07 21:44:48
试试这个：
SELECT t.*, cnt
FROM (
SELECT team_id, COUNT(*) AS cnt
FROM (
SELECT player_id, MAX(id) AS mid
FROM player_team
GROUP BY
player_id
) q
JOIN player_team pt
ON pt.id = q.mid
GROUP BY
team_id
) q2
JOIN team t
ON t.id = q2.team_id
ORDER BY
cnt DESC
LIMIT 50在player_team (player_id, id)上(按此顺序)创建索引，以便快速执行此操作。
Stack Overflow用户
发布于 2010-05-07 21:33:31
如果你在player_team表上添加一个值字段，如果你给它赋值=1(如果它是当前的)，如果它是旧的，你可以通过这样做来简化这个过程：
SELECT
COUNT(*) AS total,
pt.team_id,
p.facebook_uid AS owner_uid,
t.color
FROM
player_team pt
JOIN player p ON (p.id = pt.player_id)
JOIN team t ON (t.id = pt.team_id)
WHERE
player_team.current = 1
GROUP BY
pt.team_id
ORDER BY
total DESC
LIMIT 50 对于相同的关系，在player_team表中有多个条目，区分哪个是“当前”记录的唯一方法是比较两行(或更多行)，我认为这是不好的做法。我以前遇到过这种情况，你必须做的变通工作真的会降低性能。通过执行简单的查找(在本例中是where current=1) -或者通过将历史数据移动到完全不同的表中(取决于您的情况，这可能有些夸张)，能够查看哪一行是当前行要好得多。
Stack Overflow用户
发布于 2010-05-07 21:31:55
我有时发现MySQL中更复杂的查询需要分成两部分。
第一部分将把所需的数据拉入一个临时表中，第二部分将是试图操作所创建的数据集的查询。这样做肯定会带来显著的性能提升。
https://stackoverflow.com/questions/2788806
复制相似问题
腾讯云开发者
