mysql - 提高 SQL 查询性能

Question

我有三个表，用于存储实际人员数据 (person)、团队数据 (team) 和条目数据 (athlete)。三个表的架构为:

每个团队中可能有两名或更多运动员。

我正在尝试创建一个查询来生成最常见的配对，即以两人一组进行游戏的人。我提出了以下查询:

SELECT p1.surname, p1.name, p2.surname, p2.name, COUNT(*) AS freq
FROM person p1, athlete a1, person p2, athlete a2
WHERE
    p1.id = a1.person_id AND
    p2.id = a2.person_id AND
    a1.team_id = a2.team_id AND
    a1.team_id IN
          ( SELECT team.id
            FROM team, athlete
            WHERE team.id = athlete.team_id
            GROUP BY team.id
            HAVING COUNT(*) = 2 )
GROUP BY p1.id
ORDER BY freq DESC

显然这是一个消耗资源的查询。有什么办法可以改善吗？

Answer 1

SELECT id
FROM team, athlete
WHERE team.id = athlete.team_id
GROUP BY team.id
HAVING COUNT(*) = 2

性能提示 1:此处仅需要 athlete 表。