Team Game Stats Finder - Hockey

--------------------------------------

main

Page Setup Time: 0.00094 seconds

SELECT ts.name_abbr as team_name_abbr, ts.link as team_link, os.name_abbr as opp_name_abbr, opp_team_link, date, game_id, year_id as year_id_csk, CONCAT(year_id-1,"-",SUBSTRING(year_id,3,2)) as year_id, IF(home_away_neutral="H","",IF(home_away_neutral="A","@","N")) as game_location, (SELECT goals-goals_against+IFNULL(goals,0)/100 FROM sup_team_games tg2 WHERE tg2.game_id=st.game_id AND tg2.team_id=st.team_id) as game_result_csk, (SELECT CONCAT(IF(win=1,'W',IF(loss=1,'L','T')),' ',goals,'-',goals_against, IF(overtimes>0, IF(is_shootout,' (SO)',' (OT)'),'')) FROM sup_team_games tg2 WHERE tg2.game_id=st.game_id AND tg2.team_id=st.team_id) as game_result, sup_games.link as game_link, comp_id, goals, goals_pp, goals_sh, shots, pen_min, goals_against, goals_against_pp, goals_against_sh, shots_against, opp_pen_min, chances_pp, opp_chances_pp, goals - goals_against AS score_differential
FROM sup_team_games as st
  JOIN sup_games USING (game_id)
  JOIN sup_phase_seasons USING (comp_id, phase_id, year_id)
  JOIN sup_comp_seasons USING (comp_id, year_id)
  JOIN (SELECT team_id, year_id, name_abbr, name, link FROM sup_team_seasons GROUP BY team_id, year_id ) as ts USING (team_id, year_id)
  LEFT JOIN (SELECT team_id as opp_team_id, link as opp_team_link, year_id, name_abbr FROM sup_team_seasons GROUP BY team_id, year_id ) as os USING (opp_team_id, year_id)
WHERE comp_id=?
  AND year_id>=?
  AND type=?
  AND year_id<=?
  AND date < current_date() 
ORDER BY goals DESC, date DESC, team_id LIMIT 0, 20

SQL PARAMS -- $VAR1 = [
          'NHL',
          1918,
          'reg',
          2025
        ];

Query Time: 2.08 seconds
Table Build Time: 0.05 seconds


--------------------------------------
SH->PARAM -- $VAR1 = {
          'comp_id' => 'NHL',
          'year_min' => 1918,
          'order_by' => 'goals',
          'team_game_min' => 1,
          'match' => 'team_game',
          'comp_type' => 'reg',
          'year_max' => 2025,
          'team_game_max' => 84,
          'timeframe' => 'seasons'
        };
SH->PARAM_NO_DEFAULT -- $VAR1 = {
          'comp_id' => 'NHL',
          'year_min' => 1918,
          'year_max' => 2025,
          'order_by' => 'goals'
        };