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:
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`.`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_losses`,
CONCAT(SUBSTR(((SELECT (`c_wins`/(`c_wins`+`c_losses`))) * 100),1, 4), '%') AS `c_rate`,
-- // Overall POINTS
-- -- // OA Points Earned
CASE WHEN (SELECT
COUNT(*)
FROM `games`
WHERE `season` = `game_seasons`.`season`
AND `games`.`home` = `teams`.`t_code`
AND `games`.`home_score` IS NOT NULL
) > 0 THEN (SELECT
SUM(home_score)
FROM `games`
WHERE `season` = `game_seasons`.`season`
AND `games`.`home` = `teams`.`t_code`
AND `games`.`home_score` IS NOT NULL
) ELSE 0 END + CASE WHEN (SELECT
COUNT(*)
FROM `games`
WHERE `season` = `game_seasons`.`season`
AND `games`.`away` = `teams`.`t_code`
AND `games`.`home_score` IS NOT NULL
) > 0 THEN (SELECT
SUM(away_score)
FROM `games`
WHERE `season` = `game_seasons`.`season`
AND `games`.`away` = `teams`.`t_code`
AND `games`.`home_score` IS NOT NULL
) ELSE 0 END AS `points_e`,
-- -- // OA Points Against
CASE WHEN (SELECT
COUNT(*)
FROM `games`
WHERE `season` = `game_seasons`.`season`
AND `games`.`home` = `teams`.`t_code`
AND `games`.`home_score` IS NOT NULL
) > 0 THEN (SELECT
SUM(away_score)
FROM `games`
WHERE `season` = `game_seasons`.`season`
AND `games`.`home` = `teams`.`t_code`
AND `games`.`home_score` IS NOT NULL
) ELSE 0 END + CASE WHEN (SELECT
COUNT(*)
FROM `games`
WHERE `season` = `game_seasons`.`season`
AND `games`.`away` = `teams`.`t_code`
AND `games`.`home_score` IS NOT NULL
) > 0 THEN (SELECT
SUM(home_score)
FROM `games`
WHERE `season` = `game_seasons`.`season`
AND `games`.`away` = `teams`.`t_code`
AND `games`.`home_score` IS NOT NULL
) ELSE 0 END AS `points_a`,
-- // Division POINTS
-- -- // Div Points Earned
CASE WHEN (SELECT
COUNT(*)
FROM `games`, `team_divs` AS `g_div`
WHERE `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`
) > 0 THEN (SELECT
SUM(home_score)
FROM `games`, `team_divs` AS `g_div`
WHERE `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`
) ELSE 0 END + CASE WHEN (SELECT
COUNT(*)
FROM `games`, `team_divs` AS `g_div`
WHERE `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`
) > 0 THEN (SELECT
SUM(away_score)
FROM `games`, `team_divs` AS `g_div`
WHERE `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`
) ELSE 0 END AS `d_points_e`,
-- -- // Div Points Against
CASE WHEN (SELECT
COUNT(*)
FROM `games`, `team_divs` AS `g_div`
WHERE `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`
) > 0 THEN (SELECT
SUM(away_score)
FROM `games`, `team_divs` AS `g_div`
WHERE `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`
) ELSE 0 END + CASE WHEN (SELECT
COUNT(*)
FROM `games`, `team_divs` AS `g_div`
WHERE `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`
) > 0 THEN (SELECT
SUM(home_score)
FROM `games`, `team_divs` AS `g_div`
WHERE `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`
) ELSE 0 END AS `d_points_a`,
-- // CONFERENCE POINTS
-- -- // Conf Points Earned
CASE WHEN (SELECT
COUNT(*)
FROM `games`, `team_divs` AS `g_div`, `divisions` AS `g_conf`
WHERE `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`
) > 0 THEN (SELECT
SUM(home_score)
FROM `games`, `team_divs` AS `g_div`, `divisions` AS `g_conf`
WHERE `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`
) ELSE 0 END + CASE WHEN (SELECT
COUNT(*)
FROM `games`, `team_divs` AS `g_div`, `divisions` AS `g_conf`
WHERE `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`
) > 0 THEN (SELECT
SUM(away_score)
FROM `games`, `team_divs` AS `g_div`, `divisions` AS `g_conf`
WHERE `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`
) ELSE 0 END AS `c_points_e`,
-- -- // Conf Points Against
CASE WHEN (SELECT
COUNT(*)
FROM `games`, `team_divs` AS `g_div`, `divisions` AS `g_conf`
WHERE `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`
) > 0 THEN (SELECT
SUM(away_score)
FROM `games`, `team_divs` AS `g_div`, `divisions` AS `g_conf`
WHERE `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`
) ELSE 0 END + CASE WHEN (SELECT
COUNT(*)
FROM `games`, `team_divs` AS `g_div`, `divisions` AS `g_conf`
WHERE `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`
) > 0 THEN (SELECT
SUM(home_score)
FROM `games`, `team_divs` AS `g_div`, `divisions` AS `g_conf`
WHERE `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`
) ELSE 0 END AS `c_points_a`
FROM
`teams`,
`team_divs` AS `divs`,
`divisions` AS `confs`,
(SELECT DISTINCT `games`.`season` AS `season` FROM `games`) AS `game_seasons`
WHERE `divs`.`from` <= (SELECT SUBSTR(`game_seasons`.`season`,1,4))
AND ((`divs`.`to` > (SELECT SUBSTR(`game_seasons`.`season`,1,4))) OR (`divs`.`to` IS NULL))
AND (`teams`.`t_code` = `divs`.`t_code`)
AND (`confs`.`d_code` = `divs`.`d_code`)
AND (`teams`.`began` <= SUBSTR(`game_seasons`.`season`,1,4))
AND ((`teams`.`ended` >= SUBSTR(`game_seasons`.`season`,6)) OR (`teams`.`ended` IS NULL))
AND `game_seasons`.`season` = '$season'
-- // "$season" is a PHP variable
ORDER BY `wins` DESC, `losses` ASC, `d_wins` DESC, `d_losses` ASC, `c_wins` DESC, `c_losses` ASC,
`last_seas_div` ASC, `last_seas_oa` ASC,
`points_e` DESC, `points_a` ASC, `d_points_e` DESC, `d_points_a` ASC, `c_points_e` DESC, `c_points_a` ASC
;
Considering the size of the query (I included the full one in a text file), would breaking it up work better?
Reply With Quote
Bookmarks