php - 从另一个表中选择值 - 太慢

Question

我的查询加载时间有很大问题。在这种情况下，我需要 hg_ft_won 列(表:值)中的值，用于 home_team_id 和 away_team_id(表:匹配)。

它确实可以正常工作。加载只需要很长时间。有没有人知道如何通过替代查询来改进它？

$sql = $conn->query("SELECT m.home_team_name, 
       m.away_team_name, 
       m.home_team_id, 
       m.away_team_id, 
       m.starting_time, 
       m.starting_date, 
       m.match_id, 
       m.season_id, 
       m.competition_id, 
       s.season_name, 
       s.country_name, 
       s.competition_name, 

  (SELECT hg_ft_won 
   FROM `values` v 
   WHERE m.home_team_id = v.team_id 
     AND m.season_id = v.season_id ) AS hg_ft_won1, 

  (SELECT hg_ft_won 
   FROM `values` v 
   WHERE m.away_team_id = v.team_id 
     AND m.season_id = v.season_id ) AS hg_ft_won2, 

FROM matches m, 
     seasons s
WHERE m.season_id = s.id 
AND m.starting_date = '2017-02-11'");

值表

匹配表

来自 webpagetest.org 的结果

Answer 1

永远不要在FROM 子句中使用逗号。 始终使用正确、明确的JOIN 语法。这基本上是您的查询:

SELECT . . .
       (SELECT hg_ft_won 
        FROM `values` v 
        WHERE m.home_team_id = v.team_id AND m.season_id = v.season_id
      ) AS hg_ft_won1, 
      (SELECT hg_ft_won 
       FROM `values` v 
       WHERE m.away_team_id = v.team_id AND
             m.season_id = v.season_id
      ) AS hg_ft_won2,
FROM matches m JOIN 
     seasons s
     ON m.season_id = s.id LEFT JOIN
     competition
     ON competition.id = ?.competition_id 
WHERE m.starting_date = '2017-02-11'");

对于此查询，您需要索引:

• 比赛(开始日期，season_id)
• seasons(id)(可能已经存在)
• competition(id)(可能已经存在)
• values(team_id, season_id, hg_ft_won)

我不知道 competition_id 在哪里。它应该添加到它所属的任何表的最后一个键。