In a website’s PHP, I’ve got a query that takes about 6 seconds to run on a database.
(Query Part 1: General Info and Game fields)
PHP Code:
<!-- php buffer start -->
SELECT
`teams`.`t_code`,
CONCAT(`teams`.`home`, ' ', `teams`.`name`) AS `t_name`,
`confs`.`d_name` AS `d_name`,
`confs`.`conference` AS `conf`,
`confs`.`list_order` AS `div_order`,
--
// Previous Season's Performance
(SELECT
`past_seasons`.`div_rank`
FROM `past_seasons`
WHERE `past_seasons`.`t_code` = (CASE
WHEN (SELECT
COUNT(*)
FROM `past_seasons`
WHERE `past_seasons`.`t_code` = `teams`.`t_code`
AND `past_seasons`.`season` = CONCAT((SUBSTR(`game_seasons`.`season`,1,4) - 1), '-', SUBSTR(`game_seasons`.`season`,1,4))
) = 0 THEN `teams`.`was`
ELSE `teams`.`t_code`
END)
AND `past_seasons`.season = CONCAT((SUBSTR(`game_seasons`.`season`,1,4) - 1), '-', SUBSTR(`game_seasons`.`season`,1,4))
) AS `last_seas_div`,
(SELECT
`past_seasons`.`fba_rank`
FROM `past_seasons`
WHERE `past_seasons`.`t_code` = (CASE
WHEN (SELECT
COUNT(*)
FROM `past_seasons`
WHERE `past_seasons`.`t_code` = `teams`.`t_code`
AND `past_seasons`.season = CONCAT((SUBSTR(`game_seasons`.`season`,1,4) - 1), '-', SUBSTR(`game_seasons`.`season`,1,4))
) = 0 THEN `teams`.`was`
ELSE `teams`.`t_code`
END)
AND `past_seasons`.season = CONCAT((SUBSTR(`game_seasons`.`season`,1,4) - 1), '-', SUBSTR(`game_seasons`.`season`,1,4))
) AS `last_seas_oa`,
-- // // Overall Games
(SELECT
COUNT(*)
FROM `games`
WHERE `games`.`home_score` > `games`.`away_score`
AND `season` = `game_seasons`.`season`
AND `games`.`home` = `teams`.`t_code`
AND `games`.`home_score` IS NOT NULL
) + (SELECT
COUNT(*)
FROM `games`
WHERE `games`.`home_score` < `games`.`away_score`
AND `season` = `game_seasons`.`season`
AND `games`.`away` = `teams`.`t_code`
AND `games`.`home_score` IS NOT NULL
) AS `wins`,
(SELECT
COUNT(*)
FROM `games`
WHERE `games`.`home_score` < `games`.`away_score`
AND `season` = `game_seasons`.`season`
AND `games`.`home` = `teams`.`t_code`
AND `games`.`home_score` IS NOT NULL
) + (SELECT
COUNT(*)
FROM `games`
WHERE `games`.`home_score` > `games`.`away_score`
AND `season` = `game_seasons`.`season`
AND `games`.`away` = `teams`.`t_code`
AND `games`.`home_score` IS NOT NULL
) AS `losses`,
CONCAT(SUBSTR(((SELECT (`wins`/(`wins`+`losses`))) * 100),1, 4), '%') AS `rate`,
-- // Div Games
(SELECT
COUNT(*)
FROM `games`, `team_divs` AS `g_div`
WHERE `games`.`home_score` > `games`.`away_score`
AND `season` = `game_seasons`.`season`
AND `games`.`home` = `teams`.`t_code`
AND `games`.`home_score` IS NOT NULL
AND `games`.`away` = `g_div`.`t_code`
AND (`g_div`.`from` <= SUBSTR(`game_seasons`.`season`,1,4))
AND ((`g_div`.`to` >= SUBSTR(`game_seasons`.`season`,6)) OR (`g_div`.`to` IS NULL))
AND `divs`.`d_code` = `g_div`.`d_code`
) + (SELECT
COUNT(*)
FROM `games`, `team_divs` AS `g_div`
WHERE `games`.`home_score` < `games`.`away_score`
AND `season` = `game_seasons`.`season`
AND `games`.`away` = `teams`.`t_code`
AND `games`.`home_score` IS NOT NULL
AND `games`.`home` = `g_div`.`t_code`
AND (`g_div`.`from` <= SUBSTR(`game_seasons`.`season`,1,4))
AND ((`g_div`.`to` >= SUBSTR(`game_seasons`.`season`,6)) OR (`g_div`.`to` IS NULL))
AND `divs`.`d_code` = `g_div`.`d_code`
) AS `d_wins`,
(SELECT
COUNT(*)
FROM `games`, `team_divs` AS `g_div`
WHERE `games`.`home_score` < `games`.`away_score`
AND `season` = `game_seasons`.`season`
AND `games`.`home` = `teams`.`t_code`
AND `games`.`away` = `g_div`.`t_code`
AND `games`.`home_score` IS NOT NULL
AND (`g_div`.`from` <= SUBSTR(`game_seasons`.`season`,1,4))
AND ((`g_div`.`to` >= SUBSTR(`game_seasons`.`season`,6)) OR (`g_div`.`to` IS NULL))
AND `divs`.`d_code` = `g_div`.`d_code`
) + (SELECT
COUNT(*)
FROM `games`, `team_divs` AS `g_div`
WHERE `games`.`home_score` > `games`.`away_score`
AND `season` = `game_seasons`.`season`
AND `games`.`away` = `teams`.`t_code`
AND `games`.`home_score` IS NOT NULL
AND `games`.`home` = `g_div`.`t_code`
AND (`g_div`.`from` <= SUBSTR(`game_seasons`.`season`,1,4))
AND ((`g_div`.`to` >= SUBSTR(`game_seasons`.`season`,6)) OR (`g_div`.`to` IS NULL))
AND `divs`.`d_code` = `g_div`.`d_code`
) AS `d_losses`,
CONCAT(SUBSTR(((SELECT (`d_wins`/(`d_wins`+`d_losses`))) * 100),1, 4), '%') AS `d_rate`,
-- // Conf Games
(SELECT
COUNT(*)
FROM `games`, `team_divs` AS `g_div`, `divisions` AS `g_conf`
WHERE `games`.`home_score` > `games`.`away_score`
AND `season` = `game_seasons`.`season`
AND `games`.`home` = `teams`.`t_code`
AND `games`.`home_score` IS NOT NULL
AND `games`.`away` = `g_div`.`t_code`
AND (`g_div`.`from` <= SUBSTR(`game_seasons`.`season`,1,4))
AND ((`g_div`.`to` >= SUBSTR(`game_seasons`.`season`,6)) OR (`g_div`.`to` IS NULL))
AND `g_div`.`d_code` = `g_conf`.`d_code`
AND `confs`.`conference` = `g_conf`.`conference`
) + (SELECT
COUNT(*)
FROM `games`, `team_divs` AS `g_div`, `divisions` AS `g_conf`
WHERE `games`.`home_score` < `games`.`away_score`
AND `season` = `game_seasons`.`season`
AND `games`.`away` = `teams`.`t_code`
AND `games`.`home_score` IS NOT NULL
AND `games`.`home` = `g_div`.`t_code`
AND (`g_div`.`from` <= SUBSTR(`game_seasons`.`season`,1,4))
AND ((`g_div`.`to` >= SUBSTR(`game_seasons`.`season`,6)) OR (`g_div`.`to` IS NULL))
AND `g_div`.`d_code` = `g_conf`.`d_code`
AND `confs`.`conference` = `g_conf`.`conference`
) AS `c_wins`,
(SELECT
COUNT(*)
FROM `games`, `team_divs` AS `g_div`, `divisions` AS `g_conf`
WHERE `games`.`home_score` < `games`.`away_score`
AND `season` = `game_seasons`.`season`
AND `games`.`home` = `teams`.`t_code`
AND `games`.`home_score` IS NOT NULL
AND `games`.`away` = `g_div`.`t_code`
AND (`g_div`.`from` <= SUBSTR(`game_seasons`.`season`,1,4))
AND ((`g_div`.`to` >= SUBSTR(`game_seasons`.`season`,6)) OR (`g_div`.`to` IS NULL))
AND `g_div`.`<
No comments:
Post a Comment