Jaromír Jágr vs. Columbus Blue Jackets

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

header_pvt_summary

WITH
  rspvt AS (
SELECT person_id, COUNT(*) as games, SUM(win=1) as wins, SUM(loss=1 AND (year_id<2000 OR type="post" OR overtimes=0)) as losses, SUM(tie=1) as ties, SUM(goals) AS goals, SUM(assists) AS assists, SUM(points) AS points
FROM sup_player_games as st
  JOIN (SELECT win, loss, tie, game_id, year_id, comp_id, phase_id, team_id, opp_team_id FROM sup_team_games) as tg USING (game_id, team_id)
  JOIN sup_games USING (game_id)
  JOIN sup_phase_seasons USING (comp_id, phase_id, year_id)
WHERE ((person_id=?))
  AND opp_team_id=?
  AND type="reg"  
  ),
  popvt AS (
SELECT person_id, COUNT(*) as games, SUM(win=1) as wins, SUM(loss=1 AND (year_id<2000 OR type="post" OR overtimes=0)) as losses, SUM(goals) AS goals, SUM(assists) AS assists, SUM(points) AS points
FROM sup_player_games as st
  JOIN (SELECT win, loss, tie, game_id, year_id, comp_id, phase_id, team_id, opp_team_id FROM sup_team_games) as tg USING (game_id, team_id)
  JOIN sup_games USING (game_id)
  JOIN sup_phase_seasons USING (comp_id, phase_id, year_id)
WHERE ((person_id=?))
  AND opp_team_id=?
  AND type="post"  
  )
SELECT rspvt.person_id, rspvt.games as rspvt_games, rspvt.wins as rspvt_wins, rspvt.losses as rspvt_losses, rspvt.ties as rspvt_ties, popvt.games as popvt_games, popvt.wins as popvt_wins, popvt.losses as popvt_losses, rspvt.goals AS rspvt_goals, popvt.goals AS popvt_goals, rspvt.assists AS rspvt_assists, popvt.assists AS popvt_assists, rspvt.points AS rspvt_points, popvt.points AS popvt_points
FROM rspvt
  JOIN popvt
WHERE 1  LIMIT 0, 200

SQL PARAMS -- $VAR1 = [
          'jagrja01',
          'CBJ',
          'jagrja01',
          'CBJ'
        ];

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

pvt_totals

Page Setup Time: 0.00074 seconds

SELECT year_id as year_id_csk, CONCAT(year_id-1,"-",SUBSTRING(year_id,3,2)) as year_id, GROUP_CONCAT(DISTINCT CONCAT('<a href="', 'DOMAIN', ts.link, '" target="_blank">', ts.name_abbr, '</a>')) as teams_played_for, COUNT(*) AS games, SUM(goals) AS goals, SUM(assists) AS assists, SUM(points) AS points, SUM(plus_minus) AS plus_minus, SUM(pen_min) AS pen_min, SUM(goals_pp) AS goals_pp, SUM(goals_sh) AS goals_sh, SUM(goals_gw) AS goals_gw, SUM(shots) AS shots, SUM(time_on_ice) / SUM(year_id>=1998) as time_on_ice_avg_csk, IF(
  MOD((SUM(time_on_ice) / COUNT(*)), 60) = 60,
  CONCAT_WS(':', FLOOR((SUM(time_on_ice) / SUM(year_id>=1998)) / 60) + 1, '00'),
  CONCAT_WS(':', FLOOR((SUM(time_on_ice) / SUM(year_id>=1998)) / 60), LPAD(CAST(MOD((SUM(time_on_ice) / SUM(year_id>=1998)), 60) AS SIGNED),2,'0'))
  ) AS time_on_ice_avg
FROM sup_player_games as st
  JOIN (SELECT game_id, year_id, comp_id, phase_id, team_id, opp_team_id, opp_comp_id, opp_phase_id, team_game_num_season, home_away_neutral FROM sup_team_games) as tg USING (game_id, team_id)
  JOIN sup_phase_seasons USING (comp_id, phase_id, year_id)
  JOIN (SELECT team_id, year_id, name_abbr, name, link, phase_id FROM sup_team_seasons) as ts USING (team_id, year_id, phase_id)
WHERE ((person_id=?))
  AND opp_team_id=?
  AND type="reg" 
GROUP BY year_id
ORDER BY year_id LIMIT 0, 200

SQL PARAMS -- $VAR1 = [
          'jagrja01',
          'CBJ'
        ];

Query Time: 0.04 seconds
SELECT year_id as year_id_csk, CONCAT(year_id-1,"-",SUBSTRING(year_id,3,2)) as year_id, GROUP_CONCAT(DISTINCT CONCAT('<a href="', 'DOMAIN', ts.link, '" target="_blank">', ts.name_abbr, '</a>')) as teams_played_for, COUNT(*) AS games, SUM(goals) AS goals, SUM(assists) AS assists, SUM(points) AS points, SUM(plus_minus) AS plus_minus, SUM(pen_min) AS pen_min, SUM(goals_pp) AS goals_pp, SUM(goals_sh) AS goals_sh, SUM(goals_gw) AS goals_gw, SUM(shots) AS shots, SUM(time_on_ice) / SUM(year_id>=1998) as time_on_ice_avg_csk, IF(
  MOD((SUM(time_on_ice) / COUNT(*)), 60) = 60,
  CONCAT_WS(':', FLOOR((SUM(time_on_ice) / SUM(year_id>=1998)) / 60) + 1, '00'),
  CONCAT_WS(':', FLOOR((SUM(time_on_ice) / SUM(year_id>=1998)) / 60), LPAD(CAST(MOD((SUM(time_on_ice) / SUM(year_id>=1998)), 60) AS SIGNED),2,'0'))
  ) AS time_on_ice_avg
FROM sup_player_games as st
  JOIN (SELECT game_id, year_id, comp_id, phase_id, team_id, opp_team_id, opp_comp_id, opp_phase_id, team_game_num_season, home_away_neutral FROM sup_team_games) as tg USING (game_id, team_id)
  JOIN sup_phase_seasons USING (comp_id, phase_id, year_id)
  JOIN (SELECT team_id, year_id, name_abbr, name, link, phase_id FROM sup_team_seasons) as ts USING (team_id, year_id, phase_id)
WHERE ((person_id=?))
  AND opp_team_id=?
  AND type="post" 
GROUP BY year_id
ORDER BY year_id LIMIT 0, 200

SQL PARAMS -- $VAR1 = [
          'jagrja01',
          'CBJ'
        ];

Query Time: 0.01 seconds
Table Build Time: 0.05 seconds


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

pvt_gamelogs

Page Setup Time: 0.00111 seconds

SELECT age_on_day, ts.name_abbr as team_name_abbr, ts.link as team_link, opp_team_link, date, game_id, sup_games.link as game_link, 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, os.name_abbr as opp_name_abbr, goals, assists, points, plus_minus, pen_min, goals_pp, goals_sh, shots, time_on_ice/60 as time_on_ice_csk, IF(st.time_on_ice,CONCAT_WS(':', FLOOR(st.time_on_ice / 60), LPAD(IF(ROUND(MOD(st.time_on_ice, 60)) >= 60, 0, ROUND(MOD(st.time_on_ice, 60))), 2, '0')),NULL) AS time_on_ice
FROM sup_player_games as st
  JOIN sup_games USING (game_id)
  JOIN sup_people USING (person_id)
  JOIN sup_players USING (person_id)
  JOIN (SELECT game_id, year_id, comp_id, phase_id, part_id, team_id, opp_team_id, opp_comp_id, opp_phase_id, team_game_num_season, home_away_neutral, win, loss, tie FROM sup_team_games) as tg USING (game_id, team_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)
  JOIN (SELECT team_id as opp_team_id, year_id, name_abbr, link as opp_team_link FROM sup_team_seasons GROUP BY team_id, year_id ) as os USING (opp_team_id, year_id)
WHERE ((person_id=?))
  AND opp_team_id=?
  AND type="reg" 
ORDER BY date DESC LIMIT 0, 20

SQL PARAMS -- $VAR1 = [
          'jagrja01',
          'CBJ'
        ];

Query Time: 0.06 seconds
SELECT age_on_day, ts.name_abbr as team_name_abbr, ts.link as team_link, opp_team_link, date, game_id, sup_games.link as game_link, 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, os.name_abbr as opp_name_abbr, goals, assists, points, plus_minus, pen_min, goals_pp, goals_sh, shots, time_on_ice/60 as time_on_ice_csk, IF(st.time_on_ice,CONCAT_WS(':', FLOOR(st.time_on_ice / 60), LPAD(IF(ROUND(MOD(st.time_on_ice, 60)) >= 60, 0, ROUND(MOD(st.time_on_ice, 60))), 2, '0')),NULL) AS time_on_ice
FROM sup_player_games as st
  JOIN sup_games USING (game_id)
  JOIN sup_people USING (person_id)
  JOIN sup_players USING (person_id)
  JOIN (SELECT game_id, year_id, comp_id, phase_id, part_id, team_id, opp_team_id, opp_comp_id, opp_phase_id, team_game_num_season, home_away_neutral, win, loss, tie FROM sup_team_games) as tg USING (game_id, team_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)
  JOIN (SELECT team_id as opp_team_id, year_id, name_abbr, link as opp_team_link FROM sup_team_seasons GROUP BY team_id, year_id ) as os USING (opp_team_id, year_id)
WHERE ((person_id=?))
  AND opp_team_id=?
  AND type="post" 
ORDER BY date DESC LIMIT 0, 20

SQL PARAMS -- $VAR1 = [
          'jagrja01',
          'CBJ'
        ];

Query Time: 0.01 seconds
Table Build Time: 0.05 seconds


--------------------------------------
SH->PARAM -- $VAR1 = {
          'match' => 'versus_playervteam',
          'player_id1' => 'jagrja01',
          't2yrto' => 2025,
          'team_id1' => 'CBJ',
          'comp_id' => 'NHL',
          't1yrto' => 2026
        };
SH->PARAM_NO_DEFAULT -- $VAR1 = {
          'player_id1' => 'jagrja01',
          'team_id1' => 'CBJ',
          'match' => 'versus_playervteam',
          'comp_id' => 'NHL'
        };