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

I have a huge problem with the loading time for my query. In this case I need values from the column hg_ft_won (table: values) for the home_team_id and away_team_id (table: matches).

It does work as it should. It only takes really long to load. Does anyone have ideas how to improve that by having an alternative query?

$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'");

values table

values table

matches table

matches table

results from webpagetest.org performance test

Never use commas in the FROM clause. Always use proper, explicit JOIN syntax. This is essentially your query:

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'");

For this query, you want indexes on:

  • matches(starting_date, season_id)
  • seasons(id) (probably already there)
  • competition(id) (probably already there)
  • values(team_id, season_id, hg_ft_won)

I don't know where competition_id is. It should be added at the last key to whichever table it belongs to.

Have a try on this query. Create the required index wherever possible.

    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, 
        v1.hg_ft_won  AS hg_ft_won1, 
        v2.hg_ft_won  AS hg_ft_won2, 
    FROM matches m 
        INNER JOIN seasons s ON m.season_id = s.id
        LEFT JOIN `values` v1 ON m.home_team_id = v1.team_id  AND m.season_id = v1.season_id
        LEFT JOIN `values` v2 ON m.home_team_id = v2.team_id  AND m.season_id = v2.season_id
    AND m.starting_date = '2017-02-11'