Open vorpal-buildbot opened 1 year ago
Exceeded slow_query limit (1114.8 > 60.0) in mysql: ```
CREATE TABLE IF NOT EXISTS _new_card_archetype_stats (
name VARCHAR(190) NOT NULL,
season_id INT NOT NULL,
archetype_id INT NOT NULL,
wins INT NOT NULL,
losses INT NOT NULL,
draws INT NOT NULL,
perfect_runs INT NOT NULL,
tournament_wins INT NOT NULL,
tournament_top8s INT NOT NULL,
deck_type ENUM('league', 'tournament', 'other') NOT NULL,
PRIMARY KEY (season_id, archetype_id, name, deck_type),
FOREIGN KEY (season_id) REFERENCES season (id) ON UPDATE CASCADE ON DELETE CASCADE,
FOREIGN KEY (archetype_id) REFERENCES archetype (id) ON UPDATE CASCADE ON DELETE CASCADE,
INDEX idx_archetype_id_name (archetype_id, name)
) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci AS
SELECT
card AS name,
season.season_id,
d.archetype_id,
SUM(CASE WHEN d.id IS NOT NULL THEN 1 ELSE 0 END) AS num_decks,
IFNULL(SUM(dsum.wins), 0) AS wins,
IFNULL(SUM(dsum.losses), 0) AS losses,
IFNULL(SUM(dsum.draws), 0) AS draws,
SUM(CASE WHEN dsum.wins >= 5 AND dsum.losses = 0 AND d.source_id IN (SELECT id FROM source WHERE name = 'League') THEN 1 ELSE 0 END) AS perfect_runs,
SUM(CASE WHEN dsum.finish = 1 THEN 1 ELSE 0 END) AS tournament_wins,
SUM(CASE WHEN dsum.finish <= 8 THEN 1 ELSE 0 END) AS tournament_top8s,
(CASE WHEN ct.name = 'League' THEN 'league' WHEN ct.name = 'Gatherling' THEN 'tournament' ELSE 'other' END) AS deck_type
FROM
deck AS d
INNER JOIN
-- Eliminate maindeck/sideboard double-counting with DISTINCT. See #5493.
(SELECT DISTINCT card, deck_id FROM deck_card) AS dc ON d.id = dc.deck_id
LEFT JOIN
competition AS c ON d.competition_id = c.id
LEFT JOIN
competition_series AS cs ON cs.id = c.competition_series_id
LEFT JOIN
competition_type AS ct ON ct.id = cs.competition_type_id
LEFT JOIN deck_cache AS season ON d.id = season.deck_id
LEFT JOIN
(
SELECT
d.id,
d.created_date,
d.finish,
SUM(CASE WHEN dm.games > IFNULL(odm.games, 0) THEN 1 ELSE 0 END) AS wins, -- IFNULL so we still count byes as wins.
SUM(CASE WHEN dm.games < odm.games THEN 1 ELSE 0 END) AS losses,
SUM(CASE WHEN dm.games = odm.games THEN 1 ELSE 0 END) AS draws
FROM
deck_match AS dm
INNER JOIN
deck_match AS odm ON dm.match_id = odm.match_id AND dm.deck_id <> odm.deck_id
INNER JOIN
deck AS d ON d.id = dm.deck_id
GROUP BY
d.id
) AS dsum ON d.id = dsum.id
WHERE
d.archetype_id IS NOT NULL
GROUP BY
card,
d.archetype_id,
season.season_id,
ct.name
```
[]
(slow_query, 1114.8, mysql)
Reported on decksite by mysql-perf
Location Hash: f2991d02069e5a1fa5707770fd42095b662f4c3c
Labels: decksite
Exceeded slow_query limit (840.4 > 60.0) in mysql: ```
CREATE TABLE IF NOT EXISTS _new_card_archetype_stats (
name VARCHAR(190) NOT NULL,
season_id INT NOT NULL,
archetype_id INT NOT NULL,
wins INT NOT NULL,
losses INT NOT NULL,
draws INT NOT NULL,
perfect_runs INT NOT NULL,
tournament_wins INT NOT NULL,
tournament_top8s INT NOT NULL,
deck_type ENUM('league', 'tournament', 'other') NOT NULL,
PRIMARY KEY (season_id, archetype_id, name, deck_type),
FOREIGN KEY (season_id) REFERENCES season (id) ON UPDATE CASCADE ON DELETE CASCADE,
FOREIGN KEY (archetype_id) REFERENCES archetype (id) ON UPDATE CASCADE ON DELETE CASCADE,
INDEX idx_archetype_id_name (archetype_id, name)
) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci AS
SELECT
card AS name,
season.season_id,
d.archetype_id,
SUM(CASE WHEN d.id IS NOT NULL THEN 1 ELSE 0 END) AS num_decks,
IFNULL(SUM(dsum.wins), 0) AS wins,
IFNULL(SUM(dsum.losses), 0) AS losses,
IFNULL(SUM(dsum.draws), 0) AS draws,
SUM(CASE WHEN dsum.wins >= 5 AND dsum.losses = 0 AND d.source_id IN (SELECT id FROM source WHERE name = 'League') THEN 1 ELSE 0 END) AS perfect_runs,
SUM(CASE WHEN dsum.finish = 1 THEN 1 ELSE 0 END) AS tournament_wins,
SUM(CASE WHEN dsum.finish <= 8 THEN 1 ELSE 0 END) AS tournament_top8s,
(CASE WHEN ct.name = 'League' THEN 'league' WHEN ct.name = 'Gatherling' THEN 'tournament' ELSE 'other' END) AS deck_type
FROM
deck AS d
INNER JOIN
-- Eliminate maindeck/sideboard double-counting with DISTINCT. See #5493.
(SELECT DISTINCT card, deck_id FROM deck_card) AS dc ON d.id = dc.deck_id
LEFT JOIN
competition AS c ON d.competition_id = c.id
LEFT JOIN
competition_series AS cs ON cs.id = c.competition_series_id
LEFT JOIN
competition_type AS ct ON ct.id = cs.competition_type_id
LEFT JOIN deck_cache AS season ON d.id = season.deck_id
LEFT JOIN
(
SELECT
d.id,
d.created_date,
d.finish,
SUM(CASE WHEN dm.games > IFNULL(odm.games, 0) THEN 1 ELSE 0 END) AS wins, -- IFNULL so we still count byes as wins.
SUM(CASE WHEN dm.games < odm.games THEN 1 ELSE 0 END) AS losses,
SUM(CASE WHEN dm.games = odm.games THEN 1 ELSE 0 END) AS draws
FROM
deck_match AS dm
INNER JOIN
deck_match AS odm ON dm.match_id = odm.match_id AND dm.deck_id <> odm.deck_id
INNER JOIN
deck AS d ON d.id = dm.deck_id
GROUP BY
d.id
) AS dsum ON d.id = dsum.id
WHERE
d.archetype_id IS NOT NULL
GROUP BY
card,
d.archetype_id,
season.season_id,
ct.name
```
[]
(slow_query, 840.4, mysql)
Reported on decksite by mysql-perf
Location Hash: f2991d02069e5a1fa5707770fd42095b662f4c3c
Labels: decksite
Exceeded slow_query limit (897.9 > 60.0) in mysql: ```
CREATE TABLE IF NOT EXISTS _new_card_archetype_stats (
name VARCHAR(190) NOT NULL,
season_id INT NOT NULL,
archetype_id INT NOT NULL,
wins INT NOT NULL,
losses INT NOT NULL,
draws INT NOT NULL,
perfect_runs INT NOT NULL,
tournament_wins INT NOT NULL,
tournament_top8s INT NOT NULL,
deck_type ENUM('league', 'tournament', 'other') NOT NULL,
PRIMARY KEY (season_id, archetype_id, name, deck_type),
FOREIGN KEY (season_id) REFERENCES season (id) ON UPDATE CASCADE ON DELETE CASCADE,
FOREIGN KEY (archetype_id) REFERENCES archetype (id) ON UPDATE CASCADE ON DELETE CASCADE,
INDEX idx_archetype_id_name (archetype_id, name)
) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci AS
SELECT
card AS name,
season.season_id,
d.archetype_id,
SUM(CASE WHEN d.id IS NOT NULL THEN 1 ELSE 0 END) AS num_decks,
IFNULL(SUM(dsum.wins), 0) AS wins,
IFNULL(SUM(dsum.losses), 0) AS losses,
IFNULL(SUM(dsum.draws), 0) AS draws,
SUM(CASE WHEN dsum.wins >= 5 AND dsum.losses = 0 AND d.source_id IN (SELECT id FROM source WHERE name = 'League') THEN 1 ELSE 0 END) AS perfect_runs,
SUM(CASE WHEN dsum.finish = 1 THEN 1 ELSE 0 END) AS tournament_wins,
SUM(CASE WHEN dsum.finish <= 8 THEN 1 ELSE 0 END) AS tournament_top8s,
(CASE WHEN ct.name = 'League' THEN 'league' WHEN ct.name = 'Gatherling' THEN 'tournament' ELSE 'other' END) AS deck_type
FROM
deck AS d
INNER JOIN
-- Eliminate maindeck/sideboard double-counting with DISTINCT. See #5493.
(SELECT DISTINCT card, deck_id FROM deck_card) AS dc ON d.id = dc.deck_id
LEFT JOIN
competition AS c ON d.competition_id = c.id
LEFT JOIN
competition_series AS cs ON cs.id = c.competition_series_id
LEFT JOIN
competition_type AS ct ON ct.id = cs.competition_type_id
LEFT JOIN deck_cache AS season ON d.id = season.deck_id
LEFT JOIN
(
SELECT
d.id,
d.created_date,
d.finish,
SUM(CASE WHEN dm.games > IFNULL(odm.games, 0) THEN 1 ELSE 0 END) AS wins, -- IFNULL so we still count byes as wins.
SUM(CASE WHEN dm.games < odm.games THEN 1 ELSE 0 END) AS losses,
SUM(CASE WHEN dm.games = odm.games THEN 1 ELSE 0 END) AS draws
FROM
deck_match AS dm
INNER JOIN
deck_match AS odm ON dm.match_id = odm.match_id AND dm.deck_id <> odm.deck_id
INNER JOIN
deck AS d ON d.id = dm.deck_id
GROUP BY
d.id
) AS dsum ON d.id = dsum.id
WHERE
d.archetype_id IS NOT NULL
GROUP BY
card,
d.archetype_id,
season.season_id,
ct.name
```
[]
(slow_query, 897.9, mysql)
Reported on decksite by mysql-perf
Location Hash: f2991d02069e5a1fa5707770fd42095b662f4c3c
Labels: decksite
Exceeded slow_query limit (633.4 > 60.0) in mysql: ```
CREATE TABLE IF NOT EXISTS _new_card_archetype_stats (
name VARCHAR(190) NOT NULL,
season_id INT NOT NULL,
archetype_id INT NOT NULL,
wins INT NOT NULL,
losses INT NOT NULL,
draws INT NOT NULL,
perfect_runs INT NOT NULL,
tournament_wins INT NOT NULL,
tournament_top8s INT NOT NULL,
deck_type ENUM('league', 'tournament', 'other') NOT NULL,
PRIMARY KEY (season_id, archetype_id, name, deck_type),
FOREIGN KEY (season_id) REFERENCES season (id) ON UPDATE CASCADE ON DELETE CASCADE,
FOREIGN KEY (archetype_id) REFERENCES archetype (id) ON UPDATE CASCADE ON DELETE CASCADE,
INDEX idx_archetype_id_name (archetype_id, name)
) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci AS
SELECT
card AS name,
season.season_id,
d.archetype_id,
SUM(CASE WHEN d.id IS NOT NULL THEN 1 ELSE 0 END) AS num_decks,
IFNULL(SUM(dsum.wins), 0) AS wins,
IFNULL(SUM(dsum.losses), 0) AS losses,
IFNULL(SUM(dsum.draws), 0) AS draws,
SUM(CASE WHEN dsum.wins >= 5 AND dsum.losses = 0 AND d.source_id IN (SELECT id FROM source WHERE name = 'League') THEN 1 ELSE 0 END) AS perfect_runs,
SUM(CASE WHEN dsum.finish = 1 THEN 1 ELSE 0 END) AS tournament_wins,
SUM(CASE WHEN dsum.finish <= 8 THEN 1 ELSE 0 END) AS tournament_top8s,
(CASE WHEN ct.name = 'League' THEN 'league' WHEN ct.name = 'Gatherling' THEN 'tournament' ELSE 'other' END) AS deck_type
FROM
deck AS d
INNER JOIN
-- Eliminate maindeck/sideboard double-counting with DISTINCT. See #5493.
(SELECT DISTINCT card, deck_id FROM deck_card) AS dc ON d.id = dc.deck_id
LEFT JOIN
competition AS c ON d.competition_id = c.id
LEFT JOIN
competition_series AS cs ON cs.id = c.competition_series_id
LEFT JOIN
competition_type AS ct ON ct.id = cs.competition_type_id
LEFT JOIN deck_cache AS season ON d.id = season.deck_id
LEFT JOIN
(
SELECT
d.id,
d.created_date,
d.finish,
SUM(CASE WHEN dm.games > IFNULL(odm.games, 0) THEN 1 ELSE 0 END) AS wins, -- IFNULL so we still count byes as wins.
SUM(CASE WHEN dm.games < odm.games THEN 1 ELSE 0 END) AS losses,
SUM(CASE WHEN dm.games = odm.games THEN 1 ELSE 0 END) AS draws
FROM
deck_match AS dm
INNER JOIN
deck_match AS odm ON dm.match_id = odm.match_id AND dm.deck_id <> odm.deck_id
INNER JOIN
deck AS d ON d.id = dm.deck_id
GROUP BY
d.id
) AS dsum ON d.id = dsum.id
WHERE
d.archetype_id IS NOT NULL
GROUP BY
card,
d.archetype_id,
season.season_id,
ct.name
```
[]
(slow_query, 633.4, mysql)
Reported on decksite by mysql-perf
Location Hash: f2991d02069e5a1fa5707770fd42095b662f4c3c
Labels: decksite
Exceeded slow_query limit (658.3 > 60.0) in mysql: ```
CREATE TABLE IF NOT EXISTS _new_card_archetype_stats (
name VARCHAR(190) NOT NULL,
season_id INT NOT NULL,
archetype_id INT NOT NULL,
wins INT NOT NULL,
losses INT NOT NULL,
draws INT NOT NULL,
perfect_runs INT NOT NULL,
tournament_wins INT NOT NULL,
tournament_top8s INT NOT NULL,
deck_type ENUM('league', 'tournament', 'other') NOT NULL,
PRIMARY KEY (season_id, archetype_id, name, deck_type),
FOREIGN KEY (season_id) REFERENCES season (id) ON UPDATE CASCADE ON DELETE CASCADE,
FOREIGN KEY (archetype_id) REFERENCES archetype (id) ON UPDATE CASCADE ON DELETE CASCADE,
INDEX idx_archetype_id_name (archetype_id, name)
) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci AS
SELECT
card AS name,
season.season_id,
d.archetype_id,
SUM(CASE WHEN d.id IS NOT NULL THEN 1 ELSE 0 END) AS num_decks,
IFNULL(SUM(dsum.wins), 0) AS wins,
IFNULL(SUM(dsum.losses), 0) AS losses,
IFNULL(SUM(dsum.draws), 0) AS draws,
SUM(CASE WHEN dsum.wins >= 5 AND dsum.losses = 0 AND d.source_id IN (SELECT id FROM source WHERE name = 'League') THEN 1 ELSE 0 END) AS perfect_runs,
SUM(CASE WHEN dsum.finish = 1 THEN 1 ELSE 0 END) AS tournament_wins,
SUM(CASE WHEN dsum.finish <= 8 THEN 1 ELSE 0 END) AS tournament_top8s,
(CASE WHEN ct.name = 'League' THEN 'league' WHEN ct.name = 'Gatherling' THEN 'tournament' ELSE 'other' END) AS deck_type
FROM
deck AS d
INNER JOIN
-- Eliminate maindeck/sideboard double-counting with DISTINCT. See #5493.
(SELECT DISTINCT card, deck_id FROM deck_card) AS dc ON d.id = dc.deck_id
LEFT JOIN
competition AS c ON d.competition_id = c.id
LEFT JOIN
competition_series AS cs ON cs.id = c.competition_series_id
LEFT JOIN
competition_type AS ct ON ct.id = cs.competition_type_id
LEFT JOIN deck_cache AS season ON d.id = season.deck_id
LEFT JOIN
(
SELECT
d.id,
d.created_date,
d.finish,
SUM(CASE WHEN dm.games > IFNULL(odm.games, 0) THEN 1 ELSE 0 END) AS wins, -- IFNULL so we still count byes as wins.
SUM(CASE WHEN dm.games < odm.games THEN 1 ELSE 0 END) AS losses,
SUM(CASE WHEN dm.games = odm.games THEN 1 ELSE 0 END) AS draws
FROM
deck_match AS dm
INNER JOIN
deck_match AS odm ON dm.match_id = odm.match_id AND dm.deck_id <> odm.deck_id
INNER JOIN
deck AS d ON d.id = dm.deck_id
GROUP BY
d.id
) AS dsum ON d.id = dsum.id
WHERE
d.archetype_id IS NOT NULL
GROUP BY
card,
d.archetype_id,
season.season_id,
ct.name
```
[]
(slow_query, 658.3, mysql)
Reported on decksite by mysql-perf
Location Hash: f2991d02069e5a1fa5707770fd42095b662f4c3c
Labels: decksite
Exceeded slow_query limit (679.9 > 60.0) in mysql: ```
CREATE TABLE IF NOT EXISTS _new_card_archetype_stats (
name VARCHAR(190) NOT NULL,
season_id INT NOT NULL,
archetype_id INT NOT NULL,
wins INT NOT NULL,
losses INT NOT NULL,
draws INT NOT NULL,
perfect_runs INT NOT NULL,
tournament_wins INT NOT NULL,
tournament_top8s INT NOT NULL,
deck_type ENUM('league', 'tournament', 'other') NOT NULL,
PRIMARY KEY (season_id, archetype_id, name, deck_type),
FOREIGN KEY (season_id) REFERENCES season (id) ON UPDATE CASCADE ON DELETE CASCADE,
FOREIGN KEY (archetype_id) REFERENCES archetype (id) ON UPDATE CASCADE ON DELETE CASCADE,
INDEX idx_archetype_id_name (archetype_id, name)
) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci AS
SELECT
card AS name,
season.season_id,
d.archetype_id,
SUM(CASE WHEN d.id IS NOT NULL THEN 1 ELSE 0 END) AS num_decks,
IFNULL(SUM(dsum.wins), 0) AS wins,
IFNULL(SUM(dsum.losses), 0) AS losses,
IFNULL(SUM(dsum.draws), 0) AS draws,
SUM(CASE WHEN dsum.wins >= 5 AND dsum.losses = 0 AND d.source_id IN (SELECT id FROM source WHERE name = 'League') THEN 1 ELSE 0 END) AS perfect_runs,
SUM(CASE WHEN dsum.finish = 1 THEN 1 ELSE 0 END) AS tournament_wins,
SUM(CASE WHEN dsum.finish <= 8 THEN 1 ELSE 0 END) AS tournament_top8s,
(CASE WHEN ct.name = 'League' THEN 'league' WHEN ct.name = 'Gatherling' THEN 'tournament' ELSE 'other' END) AS deck_type
FROM
deck AS d
INNER JOIN
-- Eliminate maindeck/sideboard double-counting with DISTINCT. See #5493.
(SELECT DISTINCT card, deck_id FROM deck_card) AS dc ON d.id = dc.deck_id
LEFT JOIN
competition AS c ON d.competition_id = c.id
LEFT JOIN
competition_series AS cs ON cs.id = c.competition_series_id
LEFT JOIN
competition_type AS ct ON ct.id = cs.competition_type_id
LEFT JOIN deck_cache AS season ON d.id = season.deck_id
LEFT JOIN
(
SELECT
d.id,
d.created_date,
d.finish,
SUM(CASE WHEN dm.games > IFNULL(odm.games, 0) THEN 1 ELSE 0 END) AS wins, -- IFNULL so we still count byes as wins.
SUM(CASE WHEN dm.games < odm.games THEN 1 ELSE 0 END) AS losses,
SUM(CASE WHEN dm.games = odm.games THEN 1 ELSE 0 END) AS draws
FROM
deck_match AS dm
INNER JOIN
deck_match AS odm ON dm.match_id = odm.match_id AND dm.deck_id <> odm.deck_id
INNER JOIN
deck AS d ON d.id = dm.deck_id
GROUP BY
d.id
) AS dsum ON d.id = dsum.id
WHERE
d.archetype_id IS NOT NULL
GROUP BY
card,
d.archetype_id,
season.season_id,
ct.name
```
[]
(slow_query, 679.9, mysql)
Reported on decksite by mysql-perf
Location Hash: f2991d02069e5a1fa5707770fd42095b662f4c3c
Labels: decksite
Exceeded slow_query limit (653.8 > 60.0) in mysql: ```
CREATE TABLE IF NOT EXISTS _new_card_archetype_stats (
name VARCHAR(190) NOT NULL,
season_id INT NOT NULL,
archetype_id INT NOT NULL,
wins INT NOT NULL,
losses INT NOT NULL,
draws INT NOT NULL,
perfect_runs INT NOT NULL,
tournament_wins INT NOT NULL,
tournament_top8s INT NOT NULL,
deck_type ENUM('league', 'tournament', 'other') NOT NULL,
PRIMARY KEY (season_id, archetype_id, name, deck_type),
FOREIGN KEY (season_id) REFERENCES season (id) ON UPDATE CASCADE ON DELETE CASCADE,
FOREIGN KEY (archetype_id) REFERENCES archetype (id) ON UPDATE CASCADE ON DELETE CASCADE,
INDEX idx_archetype_id_name (archetype_id, name)
) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci AS
SELECT
card AS name,
season.season_id,
d.archetype_id,
SUM(CASE WHEN d.id IS NOT NULL THEN 1 ELSE 0 END) AS num_decks,
IFNULL(SUM(dsum.wins), 0) AS wins,
IFNULL(SUM(dsum.losses), 0) AS losses,
IFNULL(SUM(dsum.draws), 0) AS draws,
SUM(CASE WHEN dsum.wins >= 5 AND dsum.losses = 0 AND d.source_id IN (SELECT id FROM source WHERE name = 'League') THEN 1 ELSE 0 END) AS perfect_runs,
SUM(CASE WHEN dsum.finish = 1 THEN 1 ELSE 0 END) AS tournament_wins,
SUM(CASE WHEN dsum.finish <= 8 THEN 1 ELSE 0 END) AS tournament_top8s,
(CASE WHEN ct.name = 'League' THEN 'league' WHEN ct.name = 'Gatherling' THEN 'tournament' ELSE 'other' END) AS deck_type
FROM
deck AS d
INNER JOIN
-- Eliminate maindeck/sideboard double-counting with DISTINCT. See #5493.
(SELECT DISTINCT card, deck_id FROM deck_card) AS dc ON d.id = dc.deck_id
LEFT JOIN
competition AS c ON d.competition_id = c.id
LEFT JOIN
competition_series AS cs ON cs.id = c.competition_series_id
LEFT JOIN
competition_type AS ct ON ct.id = cs.competition_type_id
LEFT JOIN deck_cache AS season ON d.id = season.deck_id
LEFT JOIN
(
SELECT
d.id,
d.created_date,
d.finish,
SUM(CASE WHEN dm.games > IFNULL(odm.games, 0) THEN 1 ELSE 0 END) AS wins, -- IFNULL so we still count byes as wins.
SUM(CASE WHEN dm.games < odm.games THEN 1 ELSE 0 END) AS losses,
SUM(CASE WHEN dm.games = odm.games THEN 1 ELSE 0 END) AS draws
FROM
deck_match AS dm
INNER JOIN
deck_match AS odm ON dm.match_id = odm.match_id AND dm.deck_id <> odm.deck_id
INNER JOIN
deck AS d ON d.id = dm.deck_id
GROUP BY
d.id
) AS dsum ON d.id = dsum.id
WHERE
d.archetype_id IS NOT NULL
GROUP BY
card,
d.archetype_id,
season.season_id,
ct.name
```
[]
(slow_query, 653.8, mysql)
Reported on decksite by mysql-perf
Location Hash: f2991d02069e5a1fa5707770fd42095b662f4c3c
Labels: decksite
Exceeded slow_query limit (659.2 > 60.0) in mysql: ```
CREATE TABLE IF NOT EXISTS _new_card_archetype_stats (
name VARCHAR(190) NOT NULL,
season_id INT NOT NULL,
archetype_id INT NOT NULL,
wins INT NOT NULL,
losses INT NOT NULL,
draws INT NOT NULL,
perfect_runs INT NOT NULL,
tournament_wins INT NOT NULL,
tournament_top8s INT NOT NULL,
deck_type ENUM('league', 'tournament', 'other') NOT NULL,
PRIMARY KEY (season_id, archetype_id, name, deck_type),
FOREIGN KEY (season_id) REFERENCES season (id) ON UPDATE CASCADE ON DELETE CASCADE,
FOREIGN KEY (archetype_id) REFERENCES archetype (id) ON UPDATE CASCADE ON DELETE CASCADE,
INDEX idx_archetype_id_name (archetype_id, name)
) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci AS
SELECT
card AS name,
season.season_id,
d.archetype_id,
SUM(CASE WHEN d.id IS NOT NULL THEN 1 ELSE 0 END) AS num_decks,
IFNULL(SUM(dsum.wins), 0) AS wins,
IFNULL(SUM(dsum.losses), 0) AS losses,
IFNULL(SUM(dsum.draws), 0) AS draws,
SUM(CASE WHEN dsum.wins >= 5 AND dsum.losses = 0 AND d.source_id IN (SELECT id FROM source WHERE name = 'League') THEN 1 ELSE 0 END) AS perfect_runs,
SUM(CASE WHEN dsum.finish = 1 THEN 1 ELSE 0 END) AS tournament_wins,
SUM(CASE WHEN dsum.finish <= 8 THEN 1 ELSE 0 END) AS tournament_top8s,
(CASE WHEN ct.name = 'League' THEN 'league' WHEN ct.name = 'Gatherling' THEN 'tournament' ELSE 'other' END) AS deck_type
FROM
deck AS d
INNER JOIN
-- Eliminate maindeck/sideboard double-counting with DISTINCT. See #5493.
(SELECT DISTINCT card, deck_id FROM deck_card) AS dc ON d.id = dc.deck_id
LEFT JOIN
competition AS c ON d.competition_id = c.id
LEFT JOIN
competition_series AS cs ON cs.id = c.competition_series_id
LEFT JOIN
competition_type AS ct ON ct.id = cs.competition_type_id
LEFT JOIN deck_cache AS season ON d.id = season.deck_id
LEFT JOIN
(
SELECT
d.id,
d.created_date,
d.finish,
SUM(CASE WHEN dm.games > IFNULL(odm.games, 0) THEN 1 ELSE 0 END) AS wins, -- IFNULL so we still count byes as wins.
SUM(CASE WHEN dm.games < odm.games THEN 1 ELSE 0 END) AS losses,
SUM(CASE WHEN dm.games = odm.games THEN 1 ELSE 0 END) AS draws
FROM
deck_match AS dm
INNER JOIN
deck_match AS odm ON dm.match_id = odm.match_id AND dm.deck_id <> odm.deck_id
INNER JOIN
deck AS d ON d.id = dm.deck_id
GROUP BY
d.id
) AS dsum ON d.id = dsum.id
WHERE
d.archetype_id IS NOT NULL
GROUP BY
card,
d.archetype_id,
season.season_id,
ct.name
```
[]
(slow_query, 659.2, mysql)
Reported on decksite by mysql-perf
Location Hash: f2991d02069e5a1fa5707770fd42095b662f4c3c
Labels: decksite
Exceeded slow_query limit (605.6 > 60.0) in mysql: ```
CREATE TABLE IF NOT EXISTS _new_card_archetype_stats (
name VARCHAR(190) NOT NULL,
season_id INT NOT NULL,
archetype_id INT NOT NULL,
wins INT NOT NULL,
losses INT NOT NULL,
draws INT NOT NULL,
perfect_runs INT NOT NULL,
tournament_wins INT NOT NULL,
tournament_top8s INT NOT NULL,
deck_type ENUM('league', 'tournament', 'other') NOT NULL,
PRIMARY KEY (season_id, archetype_id, name, deck_type),
FOREIGN KEY (season_id) REFERENCES season (id) ON UPDATE CASCADE ON DELETE CASCADE,
FOREIGN KEY (archetype_id) REFERENCES archetype (id) ON UPDATE CASCADE ON DELETE CASCADE,
INDEX idx_archetype_id_name (archetype_id, name)
) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci AS
SELECT
card AS name,
season.season_id,
d.archetype_id,
SUM(CASE WHEN d.id IS NOT NULL THEN 1 ELSE 0 END) AS num_decks,
IFNULL(SUM(dsum.wins), 0) AS wins,
IFNULL(SUM(dsum.losses), 0) AS losses,
IFNULL(SUM(dsum.draws), 0) AS draws,
SUM(CASE WHEN dsum.wins >= 5 AND dsum.losses = 0 AND d.source_id IN (SELECT id FROM source WHERE name = 'League') THEN 1 ELSE 0 END) AS perfect_runs,
SUM(CASE WHEN dsum.finish = 1 THEN 1 ELSE 0 END) AS tournament_wins,
SUM(CASE WHEN dsum.finish <= 8 THEN 1 ELSE 0 END) AS tournament_top8s,
(CASE WHEN ct.name = 'League' THEN 'league' WHEN ct.name = 'Gatherling' THEN 'tournament' ELSE 'other' END) AS deck_type
FROM
deck AS d
INNER JOIN
-- Eliminate maindeck/sideboard double-counting with DISTINCT. See #5493.
(SELECT DISTINCT card, deck_id FROM deck_card) AS dc ON d.id = dc.deck_id
LEFT JOIN
competition AS c ON d.competition_id = c.id
LEFT JOIN
competition_series AS cs ON cs.id = c.competition_series_id
LEFT JOIN
competition_type AS ct ON ct.id = cs.competition_type_id
LEFT JOIN deck_cache AS season ON d.id = season.deck_id
LEFT JOIN
(
SELECT
d.id,
d.created_date,
d.finish,
SUM(CASE WHEN dm.games > IFNULL(odm.games, 0) THEN 1 ELSE 0 END) AS wins, -- IFNULL so we still count byes as wins.
SUM(CASE WHEN dm.games < odm.games THEN 1 ELSE 0 END) AS losses,
SUM(CASE WHEN dm.games = odm.games THEN 1 ELSE 0 END) AS draws
FROM
deck_match AS dm
INNER JOIN
deck_match AS odm ON dm.match_id = odm.match_id AND dm.deck_id <> odm.deck_id
INNER JOIN
deck AS d ON d.id = dm.deck_id
GROUP BY
d.id
) AS dsum ON d.id = dsum.id
WHERE
d.archetype_id IS NOT NULL
GROUP BY
card,
d.archetype_id,
season.season_id,
ct.name
```
[]
(slow_query, 605.6, mysql)
Reported on decksite by mysql-perf
Location Hash: f2991d02069e5a1fa5707770fd42095b662f4c3c
Labels: decksite
Exceeded slow_query limit (668.1 > 60.0) in mysql: ```
CREATE TABLE IF NOT EXISTS _new_card_archetype_stats (
name VARCHAR(190) NOT NULL,
season_id INT NOT NULL,
archetype_id INT NOT NULL,
wins INT NOT NULL,
losses INT NOT NULL,
draws INT NOT NULL,
perfect_runs INT NOT NULL,
tournament_wins INT NOT NULL,
tournament_top8s INT NOT NULL,
deck_type ENUM('league', 'tournament', 'other') NOT NULL,
PRIMARY KEY (season_id, archetype_id, name, deck_type),
FOREIGN KEY (season_id) REFERENCES season (id) ON UPDATE CASCADE ON DELETE CASCADE,
FOREIGN KEY (archetype_id) REFERENCES archetype (id) ON UPDATE CASCADE ON DELETE CASCADE,
INDEX idx_archetype_id_name (archetype_id, name)
) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci AS
SELECT
card AS name,
season.season_id,
d.archetype_id,
SUM(CASE WHEN d.id IS NOT NULL THEN 1 ELSE 0 END) AS num_decks,
IFNULL(SUM(dsum.wins), 0) AS wins,
IFNULL(SUM(dsum.losses), 0) AS losses,
IFNULL(SUM(dsum.draws), 0) AS draws,
SUM(CASE WHEN dsum.wins >= 5 AND dsum.losses = 0 AND d.source_id IN (SELECT id FROM source WHERE name = 'League') THEN 1 ELSE 0 END) AS perfect_runs,
SUM(CASE WHEN dsum.finish = 1 THEN 1 ELSE 0 END) AS tournament_wins,
SUM(CASE WHEN dsum.finish <= 8 THEN 1 ELSE 0 END) AS tournament_top8s,
(CASE WHEN ct.name = 'League' THEN 'league' WHEN ct.name = 'Gatherling' THEN 'tournament' ELSE 'other' END) AS deck_type
FROM
deck AS d
INNER JOIN
-- Eliminate maindeck/sideboard double-counting with DISTINCT. See #5493.
(SELECT DISTINCT card, deck_id FROM deck_card) AS dc ON d.id = dc.deck_id
LEFT JOIN
competition AS c ON d.competition_id = c.id
LEFT JOIN
competition_series AS cs ON cs.id = c.competition_series_id
LEFT JOIN
competition_type AS ct ON ct.id = cs.competition_type_id
LEFT JOIN deck_cache AS season ON d.id = season.deck_id
LEFT JOIN
(
SELECT
d.id,
d.created_date,
d.finish,
SUM(CASE WHEN dm.games > IFNULL(odm.games, 0) THEN 1 ELSE 0 END) AS wins, -- IFNULL so we still count byes as wins.
SUM(CASE WHEN dm.games < odm.games THEN 1 ELSE 0 END) AS losses,
SUM(CASE WHEN dm.games = odm.games THEN 1 ELSE 0 END) AS draws
FROM
deck_match AS dm
INNER JOIN
deck_match AS odm ON dm.match_id = odm.match_id AND dm.deck_id <> odm.deck_id
INNER JOIN
deck AS d ON d.id = dm.deck_id
GROUP BY
d.id
) AS dsum ON d.id = dsum.id
WHERE
d.archetype_id IS NOT NULL
GROUP BY
card,
d.archetype_id,
season.season_id,
ct.name
```
[]
(slow_query, 668.1, mysql)
Reported on decksite by mysql-perf
Location Hash: f2991d02069e5a1fa5707770fd42095b662f4c3c
Labels: decksite
Exceeded slow_query limit (702.8 > 60.0) in mysql: ```
CREATE TABLE IF NOT EXISTS _new_card_archetype_stats (
name VARCHAR(190) NOT NULL,
season_id INT NOT NULL,
archetype_id INT NOT NULL,
wins INT NOT NULL,
losses INT NOT NULL,
draws INT NOT NULL,
perfect_runs INT NOT NULL,
tournament_wins INT NOT NULL,
tournament_top8s INT NOT NULL,
deck_type ENUM('league', 'tournament', 'other') NOT NULL,
PRIMARY KEY (season_id, archetype_id, name, deck_type),
FOREIGN KEY (season_id) REFERENCES season (id) ON UPDATE CASCADE ON DELETE CASCADE,
FOREIGN KEY (archetype_id) REFERENCES archetype (id) ON UPDATE CASCADE ON DELETE CASCADE,
INDEX idx_archetype_id_name (archetype_id, name)
) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci AS
SELECT
card AS name,
season.season_id,
d.archetype_id,
SUM(CASE WHEN d.id IS NOT NULL THEN 1 ELSE 0 END) AS num_decks,
IFNULL(SUM(dsum.wins), 0) AS wins,
IFNULL(SUM(dsum.losses), 0) AS losses,
IFNULL(SUM(dsum.draws), 0) AS draws,
SUM(CASE WHEN dsum.wins >= 5 AND dsum.losses = 0 AND d.source_id IN (SELECT id FROM source WHERE name = 'League') THEN 1 ELSE 0 END) AS perfect_runs,
SUM(CASE WHEN dsum.finish = 1 THEN 1 ELSE 0 END) AS tournament_wins,
SUM(CASE WHEN dsum.finish <= 8 THEN 1 ELSE 0 END) AS tournament_top8s,
(CASE WHEN ct.name = 'League' THEN 'league' WHEN ct.name = 'Gatherling' THEN 'tournament' ELSE 'other' END) AS deck_type
FROM
deck AS d
INNER JOIN
-- Eliminate maindeck/sideboard double-counting with DISTINCT. See #5493.
(SELECT DISTINCT card, deck_id FROM deck_card) AS dc ON d.id = dc.deck_id
LEFT JOIN
competition AS c ON d.competition_id = c.id
LEFT JOIN
competition_series AS cs ON cs.id = c.competition_series_id
LEFT JOIN
competition_type AS ct ON ct.id = cs.competition_type_id
LEFT JOIN deck_cache AS season ON d.id = season.deck_id
LEFT JOIN
(
SELECT
d.id,
d.created_date,
d.finish,
SUM(CASE WHEN dm.games > IFNULL(odm.games, 0) THEN 1 ELSE 0 END) AS wins, -- IFNULL so we still count byes as wins.
SUM(CASE WHEN dm.games < odm.games THEN 1 ELSE 0 END) AS losses,
SUM(CASE WHEN dm.games = odm.games THEN 1 ELSE 0 END) AS draws
FROM
deck_match AS dm
INNER JOIN
deck_match AS odm ON dm.match_id = odm.match_id AND dm.deck_id <> odm.deck_id
INNER JOIN
deck AS d ON d.id = dm.deck_id
GROUP BY
d.id
) AS dsum ON d.id = dsum.id
WHERE
d.archetype_id IS NOT NULL
GROUP BY
card,
d.archetype_id,
season.season_id,
ct.name
```
[]
(slow_query, 702.8, mysql)
Reported on decksite by mysql-perf
Location Hash: f2991d02069e5a1fa5707770fd42095b662f4c3c
Labels: decksite
Exceeded slow_query limit (665.8 > 60.0) in mysql: ```
CREATE TABLE IF NOT EXISTS _new_card_archetype_stats (
name VARCHAR(190) NOT NULL,
season_id INT NOT NULL,
archetype_id INT NOT NULL,
wins INT NOT NULL,
losses INT NOT NULL,
draws INT NOT NULL,
perfect_runs INT NOT NULL,
tournament_wins INT NOT NULL,
tournament_top8s INT NOT NULL,
deck_type ENUM('league', 'tournament', 'other') NOT NULL,
PRIMARY KEY (season_id, archetype_id, name, deck_type),
FOREIGN KEY (season_id) REFERENCES season (id) ON UPDATE CASCADE ON DELETE CASCADE,
FOREIGN KEY (archetype_id) REFERENCES archetype (id) ON UPDATE CASCADE ON DELETE CASCADE,
INDEX idx_archetype_id_name (archetype_id, name)
) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci AS
SELECT
card AS name,
season.season_id,
d.archetype_id,
SUM(CASE WHEN d.id IS NOT NULL THEN 1 ELSE 0 END) AS num_decks,
IFNULL(SUM(dsum.wins), 0) AS wins,
IFNULL(SUM(dsum.losses), 0) AS losses,
IFNULL(SUM(dsum.draws), 0) AS draws,
SUM(CASE WHEN dsum.wins >= 5 AND dsum.losses = 0 AND d.source_id IN (SELECT id FROM source WHERE name = 'League') THEN 1 ELSE 0 END) AS perfect_runs,
SUM(CASE WHEN dsum.finish = 1 THEN 1 ELSE 0 END) AS tournament_wins,
SUM(CASE WHEN dsum.finish <= 8 THEN 1 ELSE 0 END) AS tournament_top8s,
(CASE WHEN ct.name = 'League' THEN 'league' WHEN ct.name = 'Gatherling' THEN 'tournament' ELSE 'other' END) AS deck_type
FROM
deck AS d
INNER JOIN
-- Eliminate maindeck/sideboard double-counting with DISTINCT. See #5493.
(SELECT DISTINCT card, deck_id FROM deck_card) AS dc ON d.id = dc.deck_id
LEFT JOIN
competition AS c ON d.competition_id = c.id
LEFT JOIN
competition_series AS cs ON cs.id = c.competition_series_id
LEFT JOIN
competition_type AS ct ON ct.id = cs.competition_type_id
LEFT JOIN deck_cache AS season ON d.id = season.deck_id
LEFT JOIN
(
SELECT
d.id,
d.created_date,
d.finish,
SUM(CASE WHEN dm.games > IFNULL(odm.games, 0) THEN 1 ELSE 0 END) AS wins, -- IFNULL so we still count byes as wins.
SUM(CASE WHEN dm.games < odm.games THEN 1 ELSE 0 END) AS losses,
SUM(CASE WHEN dm.games = odm.games THEN 1 ELSE 0 END) AS draws
FROM
deck_match AS dm
INNER JOIN
deck_match AS odm ON dm.match_id = odm.match_id AND dm.deck_id <> odm.deck_id
INNER JOIN
deck AS d ON d.id = dm.deck_id
GROUP BY
d.id
) AS dsum ON d.id = dsum.id
WHERE
d.archetype_id IS NOT NULL
GROUP BY
card,
d.archetype_id,
season.season_id,
ct.name
```
[]
(slow_query, 665.8, mysql)
Reported on decksite by mysql-perf
Location Hash: f2991d02069e5a1fa5707770fd42095b662f4c3c
Labels: decksite
Exceeded slow_query limit (658.6 > 60.0) in mysql: ```
CREATE TABLE IF NOT EXISTS _new_card_archetype_stats (
name VARCHAR(190) NOT NULL,
season_id INT NOT NULL,
archetype_id INT NOT NULL,
wins INT NOT NULL,
losses INT NOT NULL,
draws INT NOT NULL,
perfect_runs INT NOT NULL,
tournament_wins INT NOT NULL,
tournament_top8s INT NOT NULL,
deck_type ENUM('league', 'tournament', 'other') NOT NULL,
PRIMARY KEY (season_id, archetype_id, name, deck_type),
FOREIGN KEY (season_id) REFERENCES season (id) ON UPDATE CASCADE ON DELETE CASCADE,
FOREIGN KEY (archetype_id) REFERENCES archetype (id) ON UPDATE CASCADE ON DELETE CASCADE,
INDEX idx_archetype_id_name (archetype_id, name)
) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci AS
SELECT
card AS name,
season.season_id,
d.archetype_id,
SUM(CASE WHEN d.id IS NOT NULL THEN 1 ELSE 0 END) AS num_decks,
IFNULL(SUM(dsum.wins), 0) AS wins,
IFNULL(SUM(dsum.losses), 0) AS losses,
IFNULL(SUM(dsum.draws), 0) AS draws,
SUM(CASE WHEN dsum.wins >= 5 AND dsum.losses = 0 AND d.source_id IN (SELECT id FROM source WHERE name = 'League') THEN 1 ELSE 0 END) AS perfect_runs,
SUM(CASE WHEN dsum.finish = 1 THEN 1 ELSE 0 END) AS tournament_wins,
SUM(CASE WHEN dsum.finish <= 8 THEN 1 ELSE 0 END) AS tournament_top8s,
(CASE WHEN ct.name = 'League' THEN 'league' WHEN ct.name = 'Gatherling' THEN 'tournament' ELSE 'other' END) AS deck_type
FROM
deck AS d
INNER JOIN
-- Eliminate maindeck/sideboard double-counting with DISTINCT. See #5493.
(SELECT DISTINCT card, deck_id FROM deck_card) AS dc ON d.id = dc.deck_id
LEFT JOIN
competition AS c ON d.competition_id = c.id
LEFT JOIN
competition_series AS cs ON cs.id = c.competition_series_id
LEFT JOIN
competition_type AS ct ON ct.id = cs.competition_type_id
LEFT JOIN deck_cache AS season ON d.id = season.deck_id
LEFT JOIN
(
SELECT
d.id,
d.created_date,
d.finish,
SUM(CASE WHEN dm.games > IFNULL(odm.games, 0) THEN 1 ELSE 0 END) AS wins, -- IFNULL so we still count byes as wins.
SUM(CASE WHEN dm.games < odm.games THEN 1 ELSE 0 END) AS losses,
SUM(CASE WHEN dm.games = odm.games THEN 1 ELSE 0 END) AS draws
FROM
deck_match AS dm
INNER JOIN
deck_match AS odm ON dm.match_id = odm.match_id AND dm.deck_id <> odm.deck_id
INNER JOIN
deck AS d ON d.id = dm.deck_id
GROUP BY
d.id
) AS dsum ON d.id = dsum.id
WHERE
d.archetype_id IS NOT NULL
GROUP BY
card,
d.archetype_id,
season.season_id,
ct.name
```
[]
(slow_query, 658.6, mysql)
Reported on decksite by mysql-perf
Location Hash: f2991d02069e5a1fa5707770fd42095b662f4c3c
Labels: decksite
Exceeded slow_query limit (656.6 > 60.0) in mysql: ```
CREATE TABLE IF NOT EXISTS _new_card_archetype_stats (
name VARCHAR(190) NOT NULL,
season_id INT NOT NULL,
archetype_id INT NOT NULL,
wins INT NOT NULL,
losses INT NOT NULL,
draws INT NOT NULL,
perfect_runs INT NOT NULL,
tournament_wins INT NOT NULL,
tournament_top8s INT NOT NULL,
deck_type ENUM('league', 'tournament', 'other') NOT NULL,
PRIMARY KEY (season_id, archetype_id, name, deck_type),
FOREIGN KEY (season_id) REFERENCES season (id) ON UPDATE CASCADE ON DELETE CASCADE,
FOREIGN KEY (archetype_id) REFERENCES archetype (id) ON UPDATE CASCADE ON DELETE CASCADE,
INDEX idx_archetype_id_name (archetype_id, name)
) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci AS
SELECT
card AS name,
season.season_id,
d.archetype_id,
SUM(CASE WHEN d.id IS NOT NULL THEN 1 ELSE 0 END) AS num_decks,
IFNULL(SUM(dsum.wins), 0) AS wins,
IFNULL(SUM(dsum.losses), 0) AS losses,
IFNULL(SUM(dsum.draws), 0) AS draws,
SUM(CASE WHEN dsum.wins >= 5 AND dsum.losses = 0 AND d.source_id IN (SELECT id FROM source WHERE name = 'League') THEN 1 ELSE 0 END) AS perfect_runs,
SUM(CASE WHEN dsum.finish = 1 THEN 1 ELSE 0 END) AS tournament_wins,
SUM(CASE WHEN dsum.finish <= 8 THEN 1 ELSE 0 END) AS tournament_top8s,
(CASE WHEN ct.name = 'League' THEN 'league' WHEN ct.name = 'Gatherling' THEN 'tournament' ELSE 'other' END) AS deck_type
FROM
deck AS d
INNER JOIN
-- Eliminate maindeck/sideboard double-counting with DISTINCT. See #5493.
(SELECT DISTINCT card, deck_id FROM deck_card) AS dc ON d.id = dc.deck_id
LEFT JOIN
competition AS c ON d.competition_id = c.id
LEFT JOIN
competition_series AS cs ON cs.id = c.competition_series_id
LEFT JOIN
competition_type AS ct ON ct.id = cs.competition_type_id
LEFT JOIN deck_cache AS season ON d.id = season.deck_id
LEFT JOIN
(
SELECT
d.id,
d.created_date,
d.finish,
SUM(CASE WHEN dm.games > IFNULL(odm.games, 0) THEN 1 ELSE 0 END) AS wins, -- IFNULL so we still count byes as wins.
SUM(CASE WHEN dm.games < odm.games THEN 1 ELSE 0 END) AS losses,
SUM(CASE WHEN dm.games = odm.games THEN 1 ELSE 0 END) AS draws
FROM
deck_match AS dm
INNER JOIN
deck_match AS odm ON dm.match_id = odm.match_id AND dm.deck_id <> odm.deck_id
INNER JOIN
deck AS d ON d.id = dm.deck_id
GROUP BY
d.id
) AS dsum ON d.id = dsum.id
WHERE
d.archetype_id IS NOT NULL
GROUP BY
card,
d.archetype_id,
season.season_id,
ct.name
```
[]
(slow_query, 656.6, mysql)
Reported on decksite by mysql-perf
Location Hash: f2991d02069e5a1fa5707770fd42095b662f4c3c
Labels: decksite
Exceeded slow_query limit (663.5 > 60.0) in mysql: ```
CREATE TABLE IF NOT EXISTS _new_card_archetype_stats (
name VARCHAR(190) NOT NULL,
season_id INT NOT NULL,
archetype_id INT NOT NULL,
wins INT NOT NULL,
losses INT NOT NULL,
draws INT NOT NULL,
perfect_runs INT NOT NULL,
tournament_wins INT NOT NULL,
tournament_top8s INT NOT NULL,
deck_type ENUM('league', 'tournament', 'other') NOT NULL,
PRIMARY KEY (season_id, archetype_id, name, deck_type),
FOREIGN KEY (season_id) REFERENCES season (id) ON UPDATE CASCADE ON DELETE CASCADE,
FOREIGN KEY (archetype_id) REFERENCES archetype (id) ON UPDATE CASCADE ON DELETE CASCADE,
INDEX idx_archetype_id_name (archetype_id, name)
) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci AS
SELECT
card AS name,
season.season_id,
d.archetype_id,
SUM(CASE WHEN d.id IS NOT NULL THEN 1 ELSE 0 END) AS num_decks,
IFNULL(SUM(dsum.wins), 0) AS wins,
IFNULL(SUM(dsum.losses), 0) AS losses,
IFNULL(SUM(dsum.draws), 0) AS draws,
SUM(CASE WHEN dsum.wins >= 5 AND dsum.losses = 0 AND d.source_id IN (SELECT id FROM source WHERE name = 'League') THEN 1 ELSE 0 END) AS perfect_runs,
SUM(CASE WHEN dsum.finish = 1 THEN 1 ELSE 0 END) AS tournament_wins,
SUM(CASE WHEN dsum.finish <= 8 THEN 1 ELSE 0 END) AS tournament_top8s,
(CASE WHEN ct.name = 'League' THEN 'league' WHEN ct.name = 'Gatherling' THEN 'tournament' ELSE 'other' END) AS deck_type
FROM
deck AS d
INNER JOIN
-- Eliminate maindeck/sideboard double-counting with DISTINCT. See #5493.
(SELECT DISTINCT card, deck_id FROM deck_card) AS dc ON d.id = dc.deck_id
LEFT JOIN
competition AS c ON d.competition_id = c.id
LEFT JOIN
competition_series AS cs ON cs.id = c.competition_series_id
LEFT JOIN
competition_type AS ct ON ct.id = cs.competition_type_id
LEFT JOIN deck_cache AS season ON d.id = season.deck_id
LEFT JOIN
(
SELECT
d.id,
d.created_date,
d.finish,
SUM(CASE WHEN dm.games > IFNULL(odm.games, 0) THEN 1 ELSE 0 END) AS wins, -- IFNULL so we still count byes as wins.
SUM(CASE WHEN dm.games < odm.games THEN 1 ELSE 0 END) AS losses,
SUM(CASE WHEN dm.games = odm.games THEN 1 ELSE 0 END) AS draws
FROM
deck_match AS dm
INNER JOIN
deck_match AS odm ON dm.match_id = odm.match_id AND dm.deck_id <> odm.deck_id
INNER JOIN
deck AS d ON d.id = dm.deck_id
GROUP BY
d.id
) AS dsum ON d.id = dsum.id
WHERE
d.archetype_id IS NOT NULL
GROUP BY
card,
d.archetype_id,
season.season_id,
ct.name
```
[]
(slow_query, 663.5, mysql)
Reported on decksite by mysql-perf
Location Hash: f2991d02069e5a1fa5707770fd42095b662f4c3c
Labels: decksite
Exceeded slow_query limit (662.0 > 60.0) in mysql: ```
CREATE TABLE IF NOT EXISTS _new_card_archetype_stats (
name VARCHAR(190) NOT NULL,
season_id INT NOT NULL,
archetype_id INT NOT NULL,
wins INT NOT NULL,
losses INT NOT NULL,
draws INT NOT NULL,
perfect_runs INT NOT NULL,
tournament_wins INT NOT NULL,
tournament_top8s INT NOT NULL,
deck_type ENUM('league', 'tournament', 'other') NOT NULL,
PRIMARY KEY (season_id, archetype_id, name, deck_type),
FOREIGN KEY (season_id) REFERENCES season (id) ON UPDATE CASCADE ON DELETE CASCADE,
FOREIGN KEY (archetype_id) REFERENCES archetype (id) ON UPDATE CASCADE ON DELETE CASCADE,
INDEX idx_archetype_id_name (archetype_id, name)
) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci AS
SELECT
card AS name,
season.season_id,
d.archetype_id,
SUM(CASE WHEN d.id IS NOT NULL THEN 1 ELSE 0 END) AS num_decks,
IFNULL(SUM(dsum.wins), 0) AS wins,
IFNULL(SUM(dsum.losses), 0) AS losses,
IFNULL(SUM(dsum.draws), 0) AS draws,
SUM(CASE WHEN dsum.wins >= 5 AND dsum.losses = 0 AND d.source_id IN (SELECT id FROM source WHERE name = 'League') THEN 1 ELSE 0 END) AS perfect_runs,
SUM(CASE WHEN dsum.finish = 1 THEN 1 ELSE 0 END) AS tournament_wins,
SUM(CASE WHEN dsum.finish <= 8 THEN 1 ELSE 0 END) AS tournament_top8s,
(CASE WHEN ct.name = 'League' THEN 'league' WHEN ct.name = 'Gatherling' THEN 'tournament' ELSE 'other' END) AS deck_type
FROM
deck AS d
INNER JOIN
-- Eliminate maindeck/sideboard double-counting with DISTINCT. See #5493.
(SELECT DISTINCT card, deck_id FROM deck_card) AS dc ON d.id = dc.deck_id
LEFT JOIN
competition AS c ON d.competition_id = c.id
LEFT JOIN
competition_series AS cs ON cs.id = c.competition_series_id
LEFT JOIN
competition_type AS ct ON ct.id = cs.competition_type_id
LEFT JOIN deck_cache AS season ON d.id = season.deck_id
LEFT JOIN
(
SELECT
d.id,
d.created_date,
d.finish,
SUM(CASE WHEN dm.games > IFNULL(odm.games, 0) THEN 1 ELSE 0 END) AS wins, -- IFNULL so we still count byes as wins.
SUM(CASE WHEN dm.games < odm.games THEN 1 ELSE 0 END) AS losses,
SUM(CASE WHEN dm.games = odm.games THEN 1 ELSE 0 END) AS draws
FROM
deck_match AS dm
INNER JOIN
deck_match AS odm ON dm.match_id = odm.match_id AND dm.deck_id <> odm.deck_id
INNER JOIN
deck AS d ON d.id = dm.deck_id
GROUP BY
d.id
) AS dsum ON d.id = dsum.id
WHERE
d.archetype_id IS NOT NULL
GROUP BY
card,
d.archetype_id,
season.season_id,
ct.name
```
[]
(slow_query, 662.0, mysql)
Reported on decksite by mysql-perf
Location Hash: f2991d02069e5a1fa5707770fd42095b662f4c3c
Labels: decksite
Exceeded slow_query limit (681.9 > 60.0) in mysql: ```
CREATE TABLE IF NOT EXISTS _new_card_archetype_stats (
name VARCHAR(190) NOT NULL,
season_id INT NOT NULL,
archetype_id INT NOT NULL,
wins INT NOT NULL,
losses INT NOT NULL,
draws INT NOT NULL,
perfect_runs INT NOT NULL,
tournament_wins INT NOT NULL,
tournament_top8s INT NOT NULL,
deck_type ENUM('league', 'tournament', 'other') NOT NULL,
PRIMARY KEY (season_id, archetype_id, name, deck_type),
FOREIGN KEY (season_id) REFERENCES season (id) ON UPDATE CASCADE ON DELETE CASCADE,
FOREIGN KEY (archetype_id) REFERENCES archetype (id) ON UPDATE CASCADE ON DELETE CASCADE,
INDEX idx_archetype_id_name (archetype_id, name)
) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci AS
SELECT
card AS name,
season.season_id,
d.archetype_id,
SUM(CASE WHEN d.id IS NOT NULL THEN 1 ELSE 0 END) AS num_decks,
IFNULL(SUM(dsum.wins), 0) AS wins,
IFNULL(SUM(dsum.losses), 0) AS losses,
IFNULL(SUM(dsum.draws), 0) AS draws,
SUM(CASE WHEN dsum.wins >= 5 AND dsum.losses = 0 AND d.source_id IN (SELECT id FROM source WHERE name = 'League') THEN 1 ELSE 0 END) AS perfect_runs,
SUM(CASE WHEN dsum.finish = 1 THEN 1 ELSE 0 END) AS tournament_wins,
SUM(CASE WHEN dsum.finish <= 8 THEN 1 ELSE 0 END) AS tournament_top8s,
(CASE WHEN ct.name = 'League' THEN 'league' WHEN ct.name = 'Gatherling' THEN 'tournament' ELSE 'other' END) AS deck_type
FROM
deck AS d
INNER JOIN
-- Eliminate maindeck/sideboard double-counting with DISTINCT. See #5493.
(SELECT DISTINCT card, deck_id FROM deck_card) AS dc ON d.id = dc.deck_id
LEFT JOIN
competition AS c ON d.competition_id = c.id
LEFT JOIN
competition_series AS cs ON cs.id = c.competition_series_id
LEFT JOIN
competition_type AS ct ON ct.id = cs.competition_type_id
LEFT JOIN deck_cache AS season ON d.id = season.deck_id
LEFT JOIN
(
SELECT
d.id,
d.created_date,
d.finish,
SUM(CASE WHEN dm.games > IFNULL(odm.games, 0) THEN 1 ELSE 0 END) AS wins, -- IFNULL so we still count byes as wins.
SUM(CASE WHEN dm.games < odm.games THEN 1 ELSE 0 END) AS losses,
SUM(CASE WHEN dm.games = odm.games THEN 1 ELSE 0 END) AS draws
FROM
deck_match AS dm
INNER JOIN
deck_match AS odm ON dm.match_id = odm.match_id AND dm.deck_id <> odm.deck_id
INNER JOIN
deck AS d ON d.id = dm.deck_id
GROUP BY
d.id
) AS dsum ON d.id = dsum.id
WHERE
d.archetype_id IS NOT NULL
GROUP BY
card,
d.archetype_id,
season.season_id,
ct.name
```
[]
(slow_query, 681.9, mysql)
Reported on decksite by mysql-perf
Location Hash: f2991d02069e5a1fa5707770fd42095b662f4c3c
Labels: decksite
Exceeded slow_query limit (702.7 > 60.0) in mysql: ```
CREATE TABLE IF NOT EXISTS _new_card_archetype_stats (
name VARCHAR(190) NOT NULL,
season_id INT NOT NULL,
archetype_id INT NOT NULL,
wins INT NOT NULL,
losses INT NOT NULL,
draws INT NOT NULL,
perfect_runs INT NOT NULL,
tournament_wins INT NOT NULL,
tournament_top8s INT NOT NULL,
deck_type ENUM('league', 'tournament', 'other') NOT NULL,
PRIMARY KEY (season_id, archetype_id, name, deck_type),
FOREIGN KEY (season_id) REFERENCES season (id) ON UPDATE CASCADE ON DELETE CASCADE,
FOREIGN KEY (archetype_id) REFERENCES archetype (id) ON UPDATE CASCADE ON DELETE CASCADE,
INDEX idx_archetype_id_name (archetype_id, name)
) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci AS
SELECT
card AS name,
season.season_id,
d.archetype_id,
SUM(CASE WHEN d.id IS NOT NULL THEN 1 ELSE 0 END) AS num_decks,
IFNULL(SUM(dsum.wins), 0) AS wins,
IFNULL(SUM(dsum.losses), 0) AS losses,
IFNULL(SUM(dsum.draws), 0) AS draws,
SUM(CASE WHEN dsum.wins >= 5 AND dsum.losses = 0 AND d.source_id IN (SELECT id FROM source WHERE name = 'League') THEN 1 ELSE 0 END) AS perfect_runs,
SUM(CASE WHEN dsum.finish = 1 THEN 1 ELSE 0 END) AS tournament_wins,
SUM(CASE WHEN dsum.finish <= 8 THEN 1 ELSE 0 END) AS tournament_top8s,
(CASE WHEN ct.name = 'League' THEN 'league' WHEN ct.name = 'Gatherling' THEN 'tournament' ELSE 'other' END) AS deck_type
FROM
deck AS d
INNER JOIN
-- Eliminate maindeck/sideboard double-counting with DISTINCT. See #5493.
(SELECT DISTINCT card, deck_id FROM deck_card) AS dc ON d.id = dc.deck_id
LEFT JOIN
competition AS c ON d.competition_id = c.id
LEFT JOIN
competition_series AS cs ON cs.id = c.competition_series_id
LEFT JOIN
competition_type AS ct ON ct.id = cs.competition_type_id
LEFT JOIN deck_cache AS season ON d.id = season.deck_id
LEFT JOIN
(
SELECT
d.id,
d.created_date,
d.finish,
SUM(CASE WHEN dm.games > IFNULL(odm.games, 0) THEN 1 ELSE 0 END) AS wins, -- IFNULL so we still count byes as wins.
SUM(CASE WHEN dm.games < odm.games THEN 1 ELSE 0 END) AS losses,
SUM(CASE WHEN dm.games = odm.games THEN 1 ELSE 0 END) AS draws
FROM
deck_match AS dm
INNER JOIN
deck_match AS odm ON dm.match_id = odm.match_id AND dm.deck_id <> odm.deck_id
INNER JOIN
deck AS d ON d.id = dm.deck_id
GROUP BY
d.id
) AS dsum ON d.id = dsum.id
WHERE
d.archetype_id IS NOT NULL
GROUP BY
card,
d.archetype_id,
season.season_id,
ct.name
```
[]
(slow_query, 702.7, mysql)
Reported on decksite by mysql-perf
Location Hash: f2991d02069e5a1fa5707770fd42095b662f4c3c
Labels: decksite
Exceeded slow_query limit (673.0 > 60.0) in mysql: ```
CREATE TABLE IF NOT EXISTS _new_card_archetype_stats (
name VARCHAR(190) NOT NULL,
season_id INT NOT NULL,
archetype_id INT NOT NULL,
wins INT NOT NULL,
losses INT NOT NULL,
draws INT NOT NULL,
perfect_runs INT NOT NULL,
tournament_wins INT NOT NULL,
tournament_top8s INT NOT NULL,
deck_type ENUM('league', 'tournament', 'other') NOT NULL,
PRIMARY KEY (season_id, archetype_id, name, deck_type),
FOREIGN KEY (season_id) REFERENCES season (id) ON UPDATE CASCADE ON DELETE CASCADE,
FOREIGN KEY (archetype_id) REFERENCES archetype (id) ON UPDATE CASCADE ON DELETE CASCADE,
INDEX idx_archetype_id_name (archetype_id, name)
) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci AS
SELECT
card AS name,
season.season_id,
d.archetype_id,
SUM(CASE WHEN d.id IS NOT NULL THEN 1 ELSE 0 END) AS num_decks,
IFNULL(SUM(dsum.wins), 0) AS wins,
IFNULL(SUM(dsum.losses), 0) AS losses,
IFNULL(SUM(dsum.draws), 0) AS draws,
SUM(CASE WHEN dsum.wins >= 5 AND dsum.losses = 0 AND d.source_id IN (SELECT id FROM source WHERE name = 'League') THEN 1 ELSE 0 END) AS perfect_runs,
SUM(CASE WHEN dsum.finish = 1 THEN 1 ELSE 0 END) AS tournament_wins,
SUM(CASE WHEN dsum.finish <= 8 THEN 1 ELSE 0 END) AS tournament_top8s,
(CASE WHEN ct.name = 'League' THEN 'league' WHEN ct.name = 'Gatherling' THEN 'tournament' ELSE 'other' END) AS deck_type
FROM
deck AS d
INNER JOIN
-- Eliminate maindeck/sideboard double-counting with DISTINCT. See #5493.
(SELECT DISTINCT card, deck_id FROM deck_card) AS dc ON d.id = dc.deck_id
LEFT JOIN
competition AS c ON d.competition_id = c.id
LEFT JOIN
competition_series AS cs ON cs.id = c.competition_series_id
LEFT JOIN
competition_type AS ct ON ct.id = cs.competition_type_id
LEFT JOIN deck_cache AS season ON d.id = season.deck_id
LEFT JOIN
(
SELECT
d.id,
d.created_date,
d.finish,
SUM(CASE WHEN dm.games > IFNULL(odm.games, 0) THEN 1 ELSE 0 END) AS wins, -- IFNULL so we still count byes as wins.
SUM(CASE WHEN dm.games < odm.games THEN 1 ELSE 0 END) AS losses,
SUM(CASE WHEN dm.games = odm.games THEN 1 ELSE 0 END) AS draws
FROM
deck_match AS dm
INNER JOIN
deck_match AS odm ON dm.match_id = odm.match_id AND dm.deck_id <> odm.deck_id
INNER JOIN
deck AS d ON d.id = dm.deck_id
GROUP BY
d.id
) AS dsum ON d.id = dsum.id
WHERE
d.archetype_id IS NOT NULL
GROUP BY
card,
d.archetype_id,
season.season_id,
ct.name
```
[]
(slow_query, 673.0, mysql)
Reported on decksite by mysql-perf
Location Hash: f2991d02069e5a1fa5707770fd42095b662f4c3c
Labels: decksite
Exceeded slow_query limit (725.4 > 60.0) in mysql: ```
CREATE TABLE IF NOT EXISTS _new_card_archetype_stats (
name VARCHAR(190) NOT NULL,
season_id INT NOT NULL,
archetype_id INT NOT NULL,
wins INT NOT NULL,
losses INT NOT NULL,
draws INT NOT NULL,
perfect_runs INT NOT NULL,
tournament_wins INT NOT NULL,
tournament_top8s INT NOT NULL,
deck_type ENUM('league', 'tournament', 'other') NOT NULL,
PRIMARY KEY (season_id, archetype_id, name, deck_type),
FOREIGN KEY (season_id) REFERENCES season (id) ON UPDATE CASCADE ON DELETE CASCADE,
FOREIGN KEY (archetype_id) REFERENCES archetype (id) ON UPDATE CASCADE ON DELETE CASCADE,
INDEX idx_archetype_id_name (archetype_id, name)
) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci AS
SELECT
card AS name,
season.season_id,
d.archetype_id,
SUM(CASE WHEN d.id IS NOT NULL THEN 1 ELSE 0 END) AS num_decks,
IFNULL(SUM(dsum.wins), 0) AS wins,
IFNULL(SUM(dsum.losses), 0) AS losses,
IFNULL(SUM(dsum.draws), 0) AS draws,
SUM(CASE WHEN dsum.wins >= 5 AND dsum.losses = 0 AND d.source_id IN (SELECT id FROM source WHERE name = 'League') THEN 1 ELSE 0 END) AS perfect_runs,
SUM(CASE WHEN dsum.finish = 1 THEN 1 ELSE 0 END) AS tournament_wins,
SUM(CASE WHEN dsum.finish <= 8 THEN 1 ELSE 0 END) AS tournament_top8s,
(CASE WHEN ct.name = 'League' THEN 'league' WHEN ct.name = 'Gatherling' THEN 'tournament' ELSE 'other' END) AS deck_type
FROM
deck AS d
INNER JOIN
-- Eliminate maindeck/sideboard double-counting with DISTINCT. See #5493.
(SELECT DISTINCT card, deck_id FROM deck_card) AS dc ON d.id = dc.deck_id
LEFT JOIN
competition AS c ON d.competition_id = c.id
LEFT JOIN
competition_series AS cs ON cs.id = c.competition_series_id
LEFT JOIN
competition_type AS ct ON ct.id = cs.competition_type_id
LEFT JOIN deck_cache AS season ON d.id = season.deck_id
LEFT JOIN
(
SELECT
d.id,
d.created_date,
d.finish,
SUM(CASE WHEN dm.games > IFNULL(odm.games, 0) THEN 1 ELSE 0 END) AS wins, -- IFNULL so we still count byes as wins.
SUM(CASE WHEN dm.games < odm.games THEN 1 ELSE 0 END) AS losses,
SUM(CASE WHEN dm.games = odm.games THEN 1 ELSE 0 END) AS draws
FROM
deck_match AS dm
INNER JOIN
deck_match AS odm ON dm.match_id = odm.match_id AND dm.deck_id <> odm.deck_id
INNER JOIN
deck AS d ON d.id = dm.deck_id
GROUP BY
d.id
) AS dsum ON d.id = dsum.id
WHERE
d.archetype_id IS NOT NULL
GROUP BY
card,
d.archetype_id,
season.season_id,
ct.name
```
[]
(slow_query, 725.4, mysql)
Reported on decksite by mysql-perf
Location Hash: f2991d02069e5a1fa5707770fd42095b662f4c3c
Labels: decksite
Exceeded slow_query limit (687.4 > 60.0) in mysql: ```
CREATE TABLE IF NOT EXISTS _new_card_archetype_stats (
name VARCHAR(190) NOT NULL,
season_id INT NOT NULL,
archetype_id INT NOT NULL,
wins INT NOT NULL,
losses INT NOT NULL,
draws INT NOT NULL,
perfect_runs INT NOT NULL,
tournament_wins INT NOT NULL,
tournament_top8s INT NOT NULL,
deck_type ENUM('league', 'tournament', 'other') NOT NULL,
PRIMARY KEY (season_id, archetype_id, name, deck_type),
FOREIGN KEY (season_id) REFERENCES season (id) ON UPDATE CASCADE ON DELETE CASCADE,
FOREIGN KEY (archetype_id) REFERENCES archetype (id) ON UPDATE CASCADE ON DELETE CASCADE,
INDEX idx_archetype_id_name (archetype_id, name)
) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci AS
SELECT
card AS name,
season.season_id,
d.archetype_id,
SUM(CASE WHEN d.id IS NOT NULL THEN 1 ELSE 0 END) AS num_decks,
IFNULL(SUM(dsum.wins), 0) AS wins,
IFNULL(SUM(dsum.losses), 0) AS losses,
IFNULL(SUM(dsum.draws), 0) AS draws,
SUM(CASE WHEN dsum.wins >= 5 AND dsum.losses = 0 AND d.source_id IN (SELECT id FROM source WHERE name = 'League') THEN 1 ELSE 0 END) AS perfect_runs,
SUM(CASE WHEN dsum.finish = 1 THEN 1 ELSE 0 END) AS tournament_wins,
SUM(CASE WHEN dsum.finish <= 8 THEN 1 ELSE 0 END) AS tournament_top8s,
(CASE WHEN ct.name = 'League' THEN 'league' WHEN ct.name = 'Gatherling' THEN 'tournament' ELSE 'other' END) AS deck_type
FROM
deck AS d
INNER JOIN
-- Eliminate maindeck/sideboard double-counting with DISTINCT. See #5493.
(SELECT DISTINCT card, deck_id FROM deck_card) AS dc ON d.id = dc.deck_id
LEFT JOIN
competition AS c ON d.competition_id = c.id
LEFT JOIN
competition_series AS cs ON cs.id = c.competition_series_id
LEFT JOIN
competition_type AS ct ON ct.id = cs.competition_type_id
LEFT JOIN deck_cache AS season ON d.id = season.deck_id
LEFT JOIN
(
SELECT
d.id,
d.created_date,
d.finish,
SUM(CASE WHEN dm.games > IFNULL(odm.games, 0) THEN 1 ELSE 0 END) AS wins, -- IFNULL so we still count byes as wins.
SUM(CASE WHEN dm.games < odm.games THEN 1 ELSE 0 END) AS losses,
SUM(CASE WHEN dm.games = odm.games THEN 1 ELSE 0 END) AS draws
FROM
deck_match AS dm
INNER JOIN
deck_match AS odm ON dm.match_id = odm.match_id AND dm.deck_id <> odm.deck_id
INNER JOIN
deck AS d ON d.id = dm.deck_id
GROUP BY
d.id
) AS dsum ON d.id = dsum.id
WHERE
d.archetype_id IS NOT NULL
GROUP BY
card,
d.archetype_id,
season.season_id,
ct.name
```
[]
(slow_query, 687.4, mysql)
Reported on decksite by mysql-perf
Location Hash: f2991d02069e5a1fa5707770fd42095b662f4c3c
Labels: decksite
Exceeded slow_query limit (664.5 > 60.0) in mysql: ```
CREATE TABLE IF NOT EXISTS _new_card_archetype_stats (
name VARCHAR(190) NOT NULL,
season_id INT NOT NULL,
archetype_id INT NOT NULL,
wins INT NOT NULL,
losses INT NOT NULL,
draws INT NOT NULL,
perfect_runs INT NOT NULL,
tournament_wins INT NOT NULL,
tournament_top8s INT NOT NULL,
deck_type ENUM('league', 'tournament', 'other') NOT NULL,
PRIMARY KEY (season_id, archetype_id, name, deck_type),
FOREIGN KEY (season_id) REFERENCES season (id) ON UPDATE CASCADE ON DELETE CASCADE,
FOREIGN KEY (archetype_id) REFERENCES archetype (id) ON UPDATE CASCADE ON DELETE CASCADE,
INDEX idx_archetype_id_name (archetype_id, name)
) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci AS
SELECT
card AS name,
season.season_id,
d.archetype_id,
SUM(CASE WHEN d.id IS NOT NULL THEN 1 ELSE 0 END) AS num_decks,
IFNULL(SUM(dsum.wins), 0) AS wins,
IFNULL(SUM(dsum.losses), 0) AS losses,
IFNULL(SUM(dsum.draws), 0) AS draws,
SUM(CASE WHEN dsum.wins >= 5 AND dsum.losses = 0 AND d.source_id IN (SELECT id FROM source WHERE name = 'League') THEN 1 ELSE 0 END) AS perfect_runs,
SUM(CASE WHEN dsum.finish = 1 THEN 1 ELSE 0 END) AS tournament_wins,
SUM(CASE WHEN dsum.finish <= 8 THEN 1 ELSE 0 END) AS tournament_top8s,
(CASE WHEN ct.name = 'League' THEN 'league' WHEN ct.name = 'Gatherling' THEN 'tournament' ELSE 'other' END) AS deck_type
FROM
deck AS d
INNER JOIN
-- Eliminate maindeck/sideboard double-counting with DISTINCT. See #5493.
(SELECT DISTINCT card, deck_id FROM deck_card) AS dc ON d.id = dc.deck_id
LEFT JOIN
competition AS c ON d.competition_id = c.id
LEFT JOIN
competition_series AS cs ON cs.id = c.competition_series_id
LEFT JOIN
competition_type AS ct ON ct.id = cs.competition_type_id
LEFT JOIN deck_cache AS season ON d.id = season.deck_id
LEFT JOIN
(
SELECT
d.id,
d.created_date,
d.finish,
SUM(CASE WHEN dm.games > IFNULL(odm.games, 0) THEN 1 ELSE 0 END) AS wins, -- IFNULL so we still count byes as wins.
SUM(CASE WHEN dm.games < odm.games THEN 1 ELSE 0 END) AS losses,
SUM(CASE WHEN dm.games = odm.games THEN 1 ELSE 0 END) AS draws
FROM
deck_match AS dm
INNER JOIN
deck_match AS odm ON dm.match_id = odm.match_id AND dm.deck_id <> odm.deck_id
INNER JOIN
deck AS d ON d.id = dm.deck_id
GROUP BY
d.id
) AS dsum ON d.id = dsum.id
WHERE
d.archetype_id IS NOT NULL
GROUP BY
card,
d.archetype_id,
season.season_id,
ct.name
```
[]
(slow_query, 664.5, mysql)
Reported on decksite by mysql-perf
Location Hash: f2991d02069e5a1fa5707770fd42095b662f4c3c
Labels: decksite
Exceeded slow_query limit (681.7 > 60.0) in mysql: ```
CREATE TABLE IF NOT EXISTS _new_card_archetype_stats (
name VARCHAR(190) NOT NULL,
season_id INT NOT NULL,
archetype_id INT NOT NULL,
wins INT NOT NULL,
losses INT NOT NULL,
draws INT NOT NULL,
perfect_runs INT NOT NULL,
tournament_wins INT NOT NULL,
tournament_top8s INT NOT NULL,
deck_type ENUM('league', 'tournament', 'other') NOT NULL,
PRIMARY KEY (season_id, archetype_id, name, deck_type),
FOREIGN KEY (season_id) REFERENCES season (id) ON UPDATE CASCADE ON DELETE CASCADE,
FOREIGN KEY (archetype_id) REFERENCES archetype (id) ON UPDATE CASCADE ON DELETE CASCADE,
INDEX idx_archetype_id_name (archetype_id, name)
) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci AS
SELECT
card AS name,
season.season_id,
d.archetype_id,
SUM(CASE WHEN d.id IS NOT NULL THEN 1 ELSE 0 END) AS num_decks,
IFNULL(SUM(dsum.wins), 0) AS wins,
IFNULL(SUM(dsum.losses), 0) AS losses,
IFNULL(SUM(dsum.draws), 0) AS draws,
SUM(CASE WHEN dsum.wins >= 5 AND dsum.losses = 0 AND d.source_id IN (SELECT id FROM source WHERE name = 'League') THEN 1 ELSE 0 END) AS perfect_runs,
SUM(CASE WHEN dsum.finish = 1 THEN 1 ELSE 0 END) AS tournament_wins,
SUM(CASE WHEN dsum.finish <= 8 THEN 1 ELSE 0 END) AS tournament_top8s,
(CASE WHEN ct.name = 'League' THEN 'league' WHEN ct.name = 'Gatherling' THEN 'tournament' ELSE 'other' END) AS deck_type
FROM
deck AS d
INNER JOIN
-- Eliminate maindeck/sideboard double-counting with DISTINCT. See #5493.
(SELECT DISTINCT card, deck_id FROM deck_card) AS dc ON d.id = dc.deck_id
LEFT JOIN
competition AS c ON d.competition_id = c.id
LEFT JOIN
competition_series AS cs ON cs.id = c.competition_series_id
LEFT JOIN
competition_type AS ct ON ct.id = cs.competition_type_id
LEFT JOIN deck_cache AS season ON d.id = season.deck_id
LEFT JOIN
(
SELECT
d.id,
d.created_date,
d.finish,
SUM(CASE WHEN dm.games > IFNULL(odm.games, 0) THEN 1 ELSE 0 END) AS wins, -- IFNULL so we still count byes as wins.
SUM(CASE WHEN dm.games < odm.games THEN 1 ELSE 0 END) AS losses,
SUM(CASE WHEN dm.games = odm.games THEN 1 ELSE 0 END) AS draws
FROM
deck_match AS dm
INNER JOIN
deck_match AS odm ON dm.match_id = odm.match_id AND dm.deck_id <> odm.deck_id
INNER JOIN
deck AS d ON d.id = dm.deck_id
GROUP BY
d.id
) AS dsum ON d.id = dsum.id
WHERE
d.archetype_id IS NOT NULL
GROUP BY
card,
d.archetype_id,
season.season_id,
ct.name
```
[]
(slow_query, 681.7, mysql)
Reported on decksite by mysql-perf
Location Hash: f2991d02069e5a1fa5707770fd42095b662f4c3c
Labels: decksite
Exceeded slow_query limit (658.5 > 60.0) in mysql: ```
CREATE TABLE IF NOT EXISTS _new_card_archetype_stats (
name VARCHAR(190) NOT NULL,
season_id INT NOT NULL,
archetype_id INT NOT NULL,
wins INT NOT NULL,
losses INT NOT NULL,
draws INT NOT NULL,
perfect_runs INT NOT NULL,
tournament_wins INT NOT NULL,
tournament_top8s INT NOT NULL,
deck_type ENUM('league', 'tournament', 'other') NOT NULL,
PRIMARY KEY (season_id, archetype_id, name, deck_type),
FOREIGN KEY (season_id) REFERENCES season (id) ON UPDATE CASCADE ON DELETE CASCADE,
FOREIGN KEY (archetype_id) REFERENCES archetype (id) ON UPDATE CASCADE ON DELETE CASCADE,
INDEX idx_archetype_id_name (archetype_id, name)
) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci AS
SELECT
card AS name,
season.season_id,
d.archetype_id,
SUM(CASE WHEN d.id IS NOT NULL THEN 1 ELSE 0 END) AS num_decks,
IFNULL(SUM(dsum.wins), 0) AS wins,
IFNULL(SUM(dsum.losses), 0) AS losses,
IFNULL(SUM(dsum.draws), 0) AS draws,
SUM(CASE WHEN dsum.wins >= 5 AND dsum.losses = 0 AND d.source_id IN (SELECT id FROM source WHERE name = 'League') THEN 1 ELSE 0 END) AS perfect_runs,
SUM(CASE WHEN dsum.finish = 1 THEN 1 ELSE 0 END) AS tournament_wins,
SUM(CASE WHEN dsum.finish <= 8 THEN 1 ELSE 0 END) AS tournament_top8s,
(CASE WHEN ct.name = 'League' THEN 'league' WHEN ct.name = 'Gatherling' THEN 'tournament' ELSE 'other' END) AS deck_type
FROM
deck AS d
INNER JOIN
-- Eliminate maindeck/sideboard double-counting with DISTINCT. See #5493.
(SELECT DISTINCT card, deck_id FROM deck_card) AS dc ON d.id = dc.deck_id
LEFT JOIN
competition AS c ON d.competition_id = c.id
LEFT JOIN
competition_series AS cs ON cs.id = c.competition_series_id
LEFT JOIN
competition_type AS ct ON ct.id = cs.competition_type_id
LEFT JOIN deck_cache AS season ON d.id = season.deck_id
LEFT JOIN
(
SELECT
d.id,
d.created_date,
d.finish,
SUM(CASE WHEN dm.games > IFNULL(odm.games, 0) THEN 1 ELSE 0 END) AS wins, -- IFNULL so we still count byes as wins.
SUM(CASE WHEN dm.games < odm.games THEN 1 ELSE 0 END) AS losses,
SUM(CASE WHEN dm.games = odm.games THEN 1 ELSE 0 END) AS draws
FROM
deck_match AS dm
INNER JOIN
deck_match AS odm ON dm.match_id = odm.match_id AND dm.deck_id <> odm.deck_id
INNER JOIN
deck AS d ON d.id = dm.deck_id
GROUP BY
d.id
) AS dsum ON d.id = dsum.id
WHERE
d.archetype_id IS NOT NULL
GROUP BY
card,
d.archetype_id,
season.season_id,
ct.name
```
[]
(slow_query, 658.5, mysql)
Reported on decksite by mysql-perf
Location Hash: f2991d02069e5a1fa5707770fd42095b662f4c3c
Labels: decksite
Exceeded slow_query limit (671.3 > 60.0) in mysql: ```
CREATE TABLE IF NOT EXISTS _new_card_archetype_stats (
name VARCHAR(190) NOT NULL,
season_id INT NOT NULL,
archetype_id INT NOT NULL,
wins INT NOT NULL,
losses INT NOT NULL,
draws INT NOT NULL,
perfect_runs INT NOT NULL,
tournament_wins INT NOT NULL,
tournament_top8s INT NOT NULL,
deck_type ENUM('league', 'tournament', 'other') NOT NULL,
PRIMARY KEY (season_id, archetype_id, name, deck_type),
FOREIGN KEY (season_id) REFERENCES season (id) ON UPDATE CASCADE ON DELETE CASCADE,
FOREIGN KEY (archetype_id) REFERENCES archetype (id) ON UPDATE CASCADE ON DELETE CASCADE,
INDEX idx_archetype_id_name (archetype_id, name)
) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci AS
SELECT
card AS name,
season.season_id,
d.archetype_id,
SUM(CASE WHEN d.id IS NOT NULL THEN 1 ELSE 0 END) AS num_decks,
IFNULL(SUM(dsum.wins), 0) AS wins,
IFNULL(SUM(dsum.losses), 0) AS losses,
IFNULL(SUM(dsum.draws), 0) AS draws,
SUM(CASE WHEN dsum.wins >= 5 AND dsum.losses = 0 AND d.source_id IN (SELECT id FROM source WHERE name = 'League') THEN 1 ELSE 0 END) AS perfect_runs,
SUM(CASE WHEN dsum.finish = 1 THEN 1 ELSE 0 END) AS tournament_wins,
SUM(CASE WHEN dsum.finish <= 8 THEN 1 ELSE 0 END) AS tournament_top8s,
(CASE WHEN ct.name = 'League' THEN 'league' WHEN ct.name = 'Gatherling' THEN 'tournament' ELSE 'other' END) AS deck_type
FROM
deck AS d
INNER JOIN
-- Eliminate maindeck/sideboard double-counting with DISTINCT. See #5493.
(SELECT DISTINCT card, deck_id FROM deck_card) AS dc ON d.id = dc.deck_id
LEFT JOIN
competition AS c ON d.competition_id = c.id
LEFT JOIN
competition_series AS cs ON cs.id = c.competition_series_id
LEFT JOIN
competition_type AS ct ON ct.id = cs.competition_type_id
LEFT JOIN deck_cache AS season ON d.id = season.deck_id
LEFT JOIN
(
SELECT
d.id,
d.created_date,
d.finish,
SUM(CASE WHEN dm.games > IFNULL(odm.games, 0) THEN 1 ELSE 0 END) AS wins, -- IFNULL so we still count byes as wins.
SUM(CASE WHEN dm.games < odm.games THEN 1 ELSE 0 END) AS losses,
SUM(CASE WHEN dm.games = odm.games THEN 1 ELSE 0 END) AS draws
FROM
deck_match AS dm
INNER JOIN
deck_match AS odm ON dm.match_id = odm.match_id AND dm.deck_id <> odm.deck_id
INNER JOIN
deck AS d ON d.id = dm.deck_id
GROUP BY
d.id
) AS dsum ON d.id = dsum.id
WHERE
d.archetype_id IS NOT NULL
GROUP BY
card,
d.archetype_id,
season.season_id,
ct.name
```
[]
(slow_query, 671.3, mysql)
Reported on decksite by mysql-perf
Location Hash: f2991d02069e5a1fa5707770fd42095b662f4c3c
Labels: decksite
Exceeded slow_query limit (701.4 > 60.0) in mysql: ```
CREATE TABLE IF NOT EXISTS _new_card_archetype_stats (
name VARCHAR(190) NOT NULL,
season_id INT NOT NULL,
archetype_id INT NOT NULL,
wins INT NOT NULL,
losses INT NOT NULL,
draws INT NOT NULL,
perfect_runs INT NOT NULL,
tournament_wins INT NOT NULL,
tournament_top8s INT NOT NULL,
deck_type ENUM('league', 'tournament', 'other') NOT NULL,
PRIMARY KEY (season_id, archetype_id, name, deck_type),
FOREIGN KEY (season_id) REFERENCES season (id) ON UPDATE CASCADE ON DELETE CASCADE,
FOREIGN KEY (archetype_id) REFERENCES archetype (id) ON UPDATE CASCADE ON DELETE CASCADE,
INDEX idx_archetype_id_name (archetype_id, name)
) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci AS
SELECT
card AS name,
season.season_id,
d.archetype_id,
SUM(CASE WHEN d.id IS NOT NULL THEN 1 ELSE 0 END) AS num_decks,
IFNULL(SUM(dsum.wins), 0) AS wins,
IFNULL(SUM(dsum.losses), 0) AS losses,
IFNULL(SUM(dsum.draws), 0) AS draws,
SUM(CASE WHEN dsum.wins >= 5 AND dsum.losses = 0 AND d.source_id IN (SELECT id FROM source WHERE name = 'League') THEN 1 ELSE 0 END) AS perfect_runs,
SUM(CASE WHEN dsum.finish = 1 THEN 1 ELSE 0 END) AS tournament_wins,
SUM(CASE WHEN dsum.finish <= 8 THEN 1 ELSE 0 END) AS tournament_top8s,
(CASE WHEN ct.name = 'League' THEN 'league' WHEN ct.name = 'Gatherling' THEN 'tournament' ELSE 'other' END) AS deck_type
FROM
deck AS d
INNER JOIN
-- Eliminate maindeck/sideboard double-counting with DISTINCT. See #5493.
(SELECT DISTINCT card, deck_id FROM deck_card) AS dc ON d.id = dc.deck_id
LEFT JOIN
competition AS c ON d.competition_id = c.id
LEFT JOIN
competition_series AS cs ON cs.id = c.competition_series_id
LEFT JOIN
competition_type AS ct ON ct.id = cs.competition_type_id
LEFT JOIN deck_cache AS season ON d.id = season.deck_id
LEFT JOIN
(
SELECT
d.id,
d.created_date,
d.finish,
SUM(CASE WHEN dm.games > IFNULL(odm.games, 0) THEN 1 ELSE 0 END) AS wins, -- IFNULL so we still count byes as wins.
SUM(CASE WHEN dm.games < odm.games THEN 1 ELSE 0 END) AS losses,
SUM(CASE WHEN dm.games = odm.games THEN 1 ELSE 0 END) AS draws
FROM
deck_match AS dm
INNER JOIN
deck_match AS odm ON dm.match_id = odm.match_id AND dm.deck_id <> odm.deck_id
INNER JOIN
deck AS d ON d.id = dm.deck_id
GROUP BY
d.id
) AS dsum ON d.id = dsum.id
WHERE
d.archetype_id IS NOT NULL
GROUP BY
card,
d.archetype_id,
season.season_id,
ct.name
```
[]
(slow_query, 701.4, mysql)
Reported on decksite by mysql-perf
Location Hash: f2991d02069e5a1fa5707770fd42095b662f4c3c
Labels: decksite
Exceeded slow_query limit (692.7 > 60.0) in mysql: ```
CREATE TABLE IF NOT EXISTS _new_card_archetype_stats (
name VARCHAR(190) NOT NULL,
season_id INT NOT NULL,
archetype_id INT NOT NULL,
wins INT NOT NULL,
losses INT NOT NULL,
draws INT NOT NULL,
perfect_runs INT NOT NULL,
tournament_wins INT NOT NULL,
tournament_top8s INT NOT NULL,
deck_type ENUM('league', 'tournament', 'other') NOT NULL,
PRIMARY KEY (season_id, archetype_id, name, deck_type),
FOREIGN KEY (season_id) REFERENCES season (id) ON UPDATE CASCADE ON DELETE CASCADE,
FOREIGN KEY (archetype_id) REFERENCES archetype (id) ON UPDATE CASCADE ON DELETE CASCADE,
INDEX idx_archetype_id_name (archetype_id, name)
) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci AS
SELECT
card AS name,
season.season_id,
d.archetype_id,
SUM(CASE WHEN d.id IS NOT NULL THEN 1 ELSE 0 END) AS num_decks,
IFNULL(SUM(dsum.wins), 0) AS wins,
IFNULL(SUM(dsum.losses), 0) AS losses,
IFNULL(SUM(dsum.draws), 0) AS draws,
SUM(CASE WHEN dsum.wins >= 5 AND dsum.losses = 0 AND d.source_id IN (SELECT id FROM source WHERE name = 'League') THEN 1 ELSE 0 END) AS perfect_runs,
SUM(CASE WHEN dsum.finish = 1 THEN 1 ELSE 0 END) AS tournament_wins,
SUM(CASE WHEN dsum.finish <= 8 THEN 1 ELSE 0 END) AS tournament_top8s,
(CASE WHEN ct.name = 'League' THEN 'league' WHEN ct.name = 'Gatherling' THEN 'tournament' ELSE 'other' END) AS deck_type
FROM
deck AS d
INNER JOIN
-- Eliminate maindeck/sideboard double-counting with DISTINCT. See #5493.
(SELECT DISTINCT card, deck_id FROM deck_card) AS dc ON d.id = dc.deck_id
LEFT JOIN
competition AS c ON d.competition_id = c.id
LEFT JOIN
competition_series AS cs ON cs.id = c.competition_series_id
LEFT JOIN
competition_type AS ct ON ct.id = cs.competition_type_id
LEFT JOIN deck_cache AS season ON d.id = season.deck_id
LEFT JOIN
(
SELECT
d.id,
d.created_date,
d.finish,
SUM(CASE WHEN dm.games > IFNULL(odm.games, 0) THEN 1 ELSE 0 END) AS wins, -- IFNULL so we still count byes as wins.
SUM(CASE WHEN dm.games < odm.games THEN 1 ELSE 0 END) AS losses,
SUM(CASE WHEN dm.games = odm.games THEN 1 ELSE 0 END) AS draws
FROM
deck_match AS dm
INNER JOIN
deck_match AS odm ON dm.match_id = odm.match_id AND dm.deck_id <> odm.deck_id
INNER JOIN
deck AS d ON d.id = dm.deck_id
GROUP BY
d.id
) AS dsum ON d.id = dsum.id
WHERE
d.archetype_id IS NOT NULL
GROUP BY
card,
d.archetype_id,
season.season_id,
ct.name
```
[]
(slow_query, 692.7, mysql)
Reported on decksite by mysql-perf
Location Hash: f2991d02069e5a1fa5707770fd42095b662f4c3c
Labels: decksite
Exceeded slow_query limit (701.1 > 60.0) in mysql: ```
CREATE TABLE IF NOT EXISTS _new_card_archetype_stats (
name VARCHAR(190) NOT NULL,
season_id INT NOT NULL,
archetype_id INT NOT NULL,
wins INT NOT NULL,
losses INT NOT NULL,
draws INT NOT NULL,
perfect_runs INT NOT NULL,
tournament_wins INT NOT NULL,
tournament_top8s INT NOT NULL,
deck_type ENUM('league', 'tournament', 'other') NOT NULL,
PRIMARY KEY (season_id, archetype_id, name, deck_type),
FOREIGN KEY (season_id) REFERENCES season (id) ON UPDATE CASCADE ON DELETE CASCADE,
FOREIGN KEY (archetype_id) REFERENCES archetype (id) ON UPDATE CASCADE ON DELETE CASCADE,
INDEX idx_archetype_id_name (archetype_id, name)
) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci AS
SELECT
card AS name,
season.season_id,
d.archetype_id,
SUM(CASE WHEN d.id IS NOT NULL THEN 1 ELSE 0 END) AS num_decks,
IFNULL(SUM(dsum.wins), 0) AS wins,
IFNULL(SUM(dsum.losses), 0) AS losses,
IFNULL(SUM(dsum.draws), 0) AS draws,
SUM(CASE WHEN dsum.wins >= 5 AND dsum.losses = 0 AND d.source_id IN (SELECT id FROM source WHERE name = 'League') THEN 1 ELSE 0 END) AS perfect_runs,
SUM(CASE WHEN dsum.finish = 1 THEN 1 ELSE 0 END) AS tournament_wins,
SUM(CASE WHEN dsum.finish <= 8 THEN 1 ELSE 0 END) AS tournament_top8s,
(CASE WHEN ct.name = 'League' THEN 'league' WHEN ct.name = 'Gatherling' THEN 'tournament' ELSE 'other' END) AS deck_type
FROM
deck AS d
INNER JOIN
-- Eliminate maindeck/sideboard double-counting with DISTINCT. See #5493.
(SELECT DISTINCT card, deck_id FROM deck_card) AS dc ON d.id = dc.deck_id
LEFT JOIN
competition AS c ON d.competition_id = c.id
LEFT JOIN
competition_series AS cs ON cs.id = c.competition_series_id
LEFT JOIN
competition_type AS ct ON ct.id = cs.competition_type_id
LEFT JOIN deck_cache AS season ON d.id = season.deck_id
LEFT JOIN
(
SELECT
d.id,
d.created_date,
d.finish,
SUM(CASE WHEN dm.games > IFNULL(odm.games, 0) THEN 1 ELSE 0 END) AS wins, -- IFNULL so we still count byes as wins.
SUM(CASE WHEN dm.games < odm.games THEN 1 ELSE 0 END) AS losses,
SUM(CASE WHEN dm.games = odm.games THEN 1 ELSE 0 END) AS draws
FROM
deck_match AS dm
INNER JOIN
deck_match AS odm ON dm.match_id = odm.match_id AND dm.deck_id <> odm.deck_id
INNER JOIN
deck AS d ON d.id = dm.deck_id
GROUP BY
d.id
) AS dsum ON d.id = dsum.id
WHERE
d.archetype_id IS NOT NULL
GROUP BY
card,
d.archetype_id,
season.season_id,
ct.name
```
[]
(slow_query, 701.1, mysql)
Reported on decksite by mysql-perf
Location Hash: f2991d02069e5a1fa5707770fd42095b662f4c3c
Labels: decksite
Exceeded slow_query limit (661.1 > 60.0) in mysql: ```
CREATE TABLE IF NOT EXISTS _new_card_archetype_stats (
name VARCHAR(190) NOT NULL,
season_id INT NOT NULL,
archetype_id INT NOT NULL,
wins INT NOT NULL,
losses INT NOT NULL,
draws INT NOT NULL,
perfect_runs INT NOT NULL,
tournament_wins INT NOT NULL,
tournament_top8s INT NOT NULL,
deck_type ENUM('league', 'tournament', 'other') NOT NULL,
PRIMARY KEY (season_id, archetype_id, name, deck_type),
FOREIGN KEY (season_id) REFERENCES season (id) ON UPDATE CASCADE ON DELETE CASCADE,
FOREIGN KEY (archetype_id) REFERENCES archetype (id) ON UPDATE CASCADE ON DELETE CASCADE,
INDEX idx_archetype_id_name (archetype_id, name)
) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci AS
SELECT
card AS name,
season.season_id,
d.archetype_id,
SUM(CASE WHEN d.id IS NOT NULL THEN 1 ELSE 0 END) AS num_decks,
IFNULL(SUM(dsum.wins), 0) AS wins,
IFNULL(SUM(dsum.losses), 0) AS losses,
IFNULL(SUM(dsum.draws), 0) AS draws,
SUM(CASE WHEN dsum.wins >= 5 AND dsum.losses = 0 AND d.source_id IN (SELECT id FROM source WHERE name = 'League') THEN 1 ELSE 0 END) AS perfect_runs,
SUM(CASE WHEN dsum.finish = 1 THEN 1 ELSE 0 END) AS tournament_wins,
SUM(CASE WHEN dsum.finish <= 8 THEN 1 ELSE 0 END) AS tournament_top8s,
(CASE WHEN ct.name = 'League' THEN 'league' WHEN ct.name = 'Gatherling' THEN 'tournament' ELSE 'other' END) AS deck_type
FROM
deck AS d
INNER JOIN
-- Eliminate maindeck/sideboard double-counting with DISTINCT. See #5493.
(SELECT DISTINCT card, deck_id FROM deck_card) AS dc ON d.id = dc.deck_id
LEFT JOIN
competition AS c ON d.competition_id = c.id
LEFT JOIN
competition_series AS cs ON cs.id = c.competition_series_id
LEFT JOIN
competition_type AS ct ON ct.id = cs.competition_type_id
LEFT JOIN deck_cache AS season ON d.id = season.deck_id
LEFT JOIN
(
SELECT
d.id,
d.created_date,
d.finish,
SUM(CASE WHEN dm.games > IFNULL(odm.games, 0) THEN 1 ELSE 0 END) AS wins, -- IFNULL so we still count byes as wins.
SUM(CASE WHEN dm.games < odm.games THEN 1 ELSE 0 END) AS losses,
SUM(CASE WHEN dm.games = odm.games THEN 1 ELSE 0 END) AS draws
FROM
deck_match AS dm
INNER JOIN
deck_match AS odm ON dm.match_id = odm.match_id AND dm.deck_id <> odm.deck_id
INNER JOIN
deck AS d ON d.id = dm.deck_id
GROUP BY
d.id
) AS dsum ON d.id = dsum.id
WHERE
d.archetype_id IS NOT NULL
GROUP BY
card,
d.archetype_id,
season.season_id,
ct.name
```
[]
(slow_query, 661.1, mysql)
Reported on decksite by mysql-perf
Location Hash: f2991d02069e5a1fa5707770fd42095b662f4c3c
Labels: decksite
Exceeded slow_query limit (676.7 > 60.0) in mysql: ```
CREATE TABLE IF NOT EXISTS _new_card_archetype_stats (
name VARCHAR(190) NOT NULL,
season_id INT NOT NULL,
archetype_id INT NOT NULL,
wins INT NOT NULL,
losses INT NOT NULL,
draws INT NOT NULL,
perfect_runs INT NOT NULL,
tournament_wins INT NOT NULL,
tournament_top8s INT NOT NULL,
deck_type ENUM('league', 'tournament', 'other') NOT NULL,
PRIMARY KEY (season_id, archetype_id, name, deck_type),
FOREIGN KEY (season_id) REFERENCES season (id) ON UPDATE CASCADE ON DELETE CASCADE,
FOREIGN KEY (archetype_id) REFERENCES archetype (id) ON UPDATE CASCADE ON DELETE CASCADE,
INDEX idx_archetype_id_name (archetype_id, name)
) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci AS
SELECT
card AS name,
season.season_id,
d.archetype_id,
SUM(CASE WHEN d.id IS NOT NULL THEN 1 ELSE 0 END) AS num_decks,
IFNULL(SUM(dsum.wins), 0) AS wins,
IFNULL(SUM(dsum.losses), 0) AS losses,
IFNULL(SUM(dsum.draws), 0) AS draws,
SUM(CASE WHEN dsum.wins >= 5 AND dsum.losses = 0 AND d.source_id IN (SELECT id FROM source WHERE name = 'League') THEN 1 ELSE 0 END) AS perfect_runs,
SUM(CASE WHEN dsum.finish = 1 THEN 1 ELSE 0 END) AS tournament_wins,
SUM(CASE WHEN dsum.finish <= 8 THEN 1 ELSE 0 END) AS tournament_top8s,
(CASE WHEN ct.name = 'League' THEN 'league' WHEN ct.name = 'Gatherling' THEN 'tournament' ELSE 'other' END) AS deck_type
FROM
deck AS d
INNER JOIN
-- Eliminate maindeck/sideboard double-counting with DISTINCT. See #5493.
(SELECT DISTINCT card, deck_id FROM deck_card) AS dc ON d.id = dc.deck_id
LEFT JOIN
competition AS c ON d.competition_id = c.id
LEFT JOIN
competition_series AS cs ON cs.id = c.competition_series_id
LEFT JOIN
competition_type AS ct ON ct.id = cs.competition_type_id
LEFT JOIN deck_cache AS season ON d.id = season.deck_id
LEFT JOIN
(
SELECT
d.id,
d.created_date,
d.finish,
SUM(CASE WHEN dm.games > IFNULL(odm.games, 0) THEN 1 ELSE 0 END) AS wins, -- IFNULL so we still count byes as wins.
SUM(CASE WHEN dm.games < odm.games THEN 1 ELSE 0 END) AS losses,
SUM(CASE WHEN dm.games = odm.games THEN 1 ELSE 0 END) AS draws
FROM
deck_match AS dm
INNER JOIN
deck_match AS odm ON dm.match_id = odm.match_id AND dm.deck_id <> odm.deck_id
INNER JOIN
deck AS d ON d.id = dm.deck_id
GROUP BY
d.id
) AS dsum ON d.id = dsum.id
WHERE
d.archetype_id IS NOT NULL
GROUP BY
card,
d.archetype_id,
season.season_id,
ct.name
```
[]
(slow_query, 676.7, mysql)
Reported on decksite by mysql-perf
Location Hash: f2991d02069e5a1fa5707770fd42095b662f4c3c
Labels: decksite
Exceeded slow_query limit (666.3 > 60.0) in mysql: ```
CREATE TABLE IF NOT EXISTS _new_card_archetype_stats (
name VARCHAR(190) NOT NULL,
season_id INT NOT NULL,
archetype_id INT NOT NULL,
wins INT NOT NULL,
losses INT NOT NULL,
draws INT NOT NULL,
perfect_runs INT NOT NULL,
tournament_wins INT NOT NULL,
tournament_top8s INT NOT NULL,
deck_type ENUM('league', 'tournament', 'other') NOT NULL,
PRIMARY KEY (season_id, archetype_id, name, deck_type),
FOREIGN KEY (season_id) REFERENCES season (id) ON UPDATE CASCADE ON DELETE CASCADE,
FOREIGN KEY (archetype_id) REFERENCES archetype (id) ON UPDATE CASCADE ON DELETE CASCADE,
INDEX idx_archetype_id_name (archetype_id, name)
) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci AS
SELECT
card AS name,
season.season_id,
d.archetype_id,
SUM(CASE WHEN d.id IS NOT NULL THEN 1 ELSE 0 END) AS num_decks,
IFNULL(SUM(dsum.wins), 0) AS wins,
IFNULL(SUM(dsum.losses), 0) AS losses,
IFNULL(SUM(dsum.draws), 0) AS draws,
SUM(CASE WHEN dsum.wins >= 5 AND dsum.losses = 0 AND d.source_id IN (SELECT id FROM source WHERE name = 'League') THEN 1 ELSE 0 END) AS perfect_runs,
SUM(CASE WHEN dsum.finish = 1 THEN 1 ELSE 0 END) AS tournament_wins,
SUM(CASE WHEN dsum.finish <= 8 THEN 1 ELSE 0 END) AS tournament_top8s,
(CASE WHEN ct.name = 'League' THEN 'league' WHEN ct.name = 'Gatherling' THEN 'tournament' ELSE 'other' END) AS deck_type
FROM
deck AS d
INNER JOIN
-- Eliminate maindeck/sideboard double-counting with DISTINCT. See #5493.
(SELECT DISTINCT card, deck_id FROM deck_card) AS dc ON d.id = dc.deck_id
LEFT JOIN
competition AS c ON d.competition_id = c.id
LEFT JOIN
competition_series AS cs ON cs.id = c.competition_series_id
LEFT JOIN
competition_type AS ct ON ct.id = cs.competition_type_id
LEFT JOIN deck_cache AS season ON d.id = season.deck_id
LEFT JOIN
(
SELECT
d.id,
d.created_date,
d.finish,
SUM(CASE WHEN dm.games > IFNULL(odm.games, 0) THEN 1 ELSE 0 END) AS wins, -- IFNULL so we still count byes as wins.
SUM(CASE WHEN dm.games < odm.games THEN 1 ELSE 0 END) AS losses,
SUM(CASE WHEN dm.games = odm.games THEN 1 ELSE 0 END) AS draws
FROM
deck_match AS dm
INNER JOIN
deck_match AS odm ON dm.match_id = odm.match_id AND dm.deck_id <> odm.deck_id
INNER JOIN
deck AS d ON d.id = dm.deck_id
GROUP BY
d.id
) AS dsum ON d.id = dsum.id
WHERE
d.archetype_id IS NOT NULL
GROUP BY
card,
d.archetype_id,
season.season_id,
ct.name
```
[]
(slow_query, 666.3, mysql)
Reported on decksite by mysql-perf
Location Hash: f2991d02069e5a1fa5707770fd42095b662f4c3c
Labels: decksite
Exceeded slow_query limit (655.9 > 60.0) in mysql: ```
CREATE TABLE IF NOT EXISTS _new_card_archetype_stats (
name VARCHAR(190) NOT NULL,
season_id INT NOT NULL,
archetype_id INT NOT NULL,
wins INT NOT NULL,
losses INT NOT NULL,
draws INT NOT NULL,
perfect_runs INT NOT NULL,
tournament_wins INT NOT NULL,
tournament_top8s INT NOT NULL,
deck_type ENUM('league', 'tournament', 'other') NOT NULL,
PRIMARY KEY (season_id, archetype_id, name, deck_type),
FOREIGN KEY (season_id) REFERENCES season (id) ON UPDATE CASCADE ON DELETE CASCADE,
FOREIGN KEY (archetype_id) REFERENCES archetype (id) ON UPDATE CASCADE ON DELETE CASCADE,
INDEX idx_archetype_id_name (archetype_id, name)
) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci AS
SELECT
card AS name,
season.season_id,
d.archetype_id,
SUM(CASE WHEN d.id IS NOT NULL THEN 1 ELSE 0 END) AS num_decks,
IFNULL(SUM(dsum.wins), 0) AS wins,
IFNULL(SUM(dsum.losses), 0) AS losses,
IFNULL(SUM(dsum.draws), 0) AS draws,
SUM(CASE WHEN dsum.wins >= 5 AND dsum.losses = 0 AND d.source_id IN (SELECT id FROM source WHERE name = 'League') THEN 1 ELSE 0 END) AS perfect_runs,
SUM(CASE WHEN dsum.finish = 1 THEN 1 ELSE 0 END) AS tournament_wins,
SUM(CASE WHEN dsum.finish <= 8 THEN 1 ELSE 0 END) AS tournament_top8s,
(CASE WHEN ct.name = 'League' THEN 'league' WHEN ct.name = 'Gatherling' THEN 'tournament' ELSE 'other' END) AS deck_type
FROM
deck AS d
INNER JOIN
-- Eliminate maindeck/sideboard double-counting with DISTINCT. See #5493.
(SELECT DISTINCT card, deck_id FROM deck_card) AS dc ON d.id = dc.deck_id
LEFT JOIN
competition AS c ON d.competition_id = c.id
LEFT JOIN
competition_series AS cs ON cs.id = c.competition_series_id
LEFT JOIN
competition_type AS ct ON ct.id = cs.competition_type_id
LEFT JOIN deck_cache AS season ON d.id = season.deck_id
LEFT JOIN
(
SELECT
d.id,
d.created_date,
d.finish,
SUM(CASE WHEN dm.games > IFNULL(odm.games, 0) THEN 1 ELSE 0 END) AS wins, -- IFNULL so we still count byes as wins.
SUM(CASE WHEN dm.games < odm.games THEN 1 ELSE 0 END) AS losses,
SUM(CASE WHEN dm.games = odm.games THEN 1 ELSE 0 END) AS draws
FROM
deck_match AS dm
INNER JOIN
deck_match AS odm ON dm.match_id = odm.match_id AND dm.deck_id <> odm.deck_id
INNER JOIN
deck AS d ON d.id = dm.deck_id
GROUP BY
d.id
) AS dsum ON d.id = dsum.id
WHERE
d.archetype_id IS NOT NULL
GROUP BY
card,
d.archetype_id,
season.season_id,
ct.name
```
[]
(slow_query, 655.9, mysql)
Reported on decksite by mysql-perf
Location Hash: f2991d02069e5a1fa5707770fd42095b662f4c3c
Labels: decksite
Exceeded slow_query limit (640.5 > 60.0) in mysql: ```
CREATE TABLE IF NOT EXISTS _new_card_archetype_stats (
name VARCHAR(190) NOT NULL,
season_id INT NOT NULL,
archetype_id INT NOT NULL,
wins INT NOT NULL,
losses INT NOT NULL,
draws INT NOT NULL,
perfect_runs INT NOT NULL,
tournament_wins INT NOT NULL,
tournament_top8s INT NOT NULL,
deck_type ENUM('league', 'tournament', 'other') NOT NULL,
PRIMARY KEY (season_id, archetype_id, name, deck_type),
FOREIGN KEY (season_id) REFERENCES season (id) ON UPDATE CASCADE ON DELETE CASCADE,
FOREIGN KEY (archetype_id) REFERENCES archetype (id) ON UPDATE CASCADE ON DELETE CASCADE,
INDEX idx_archetype_id_name (archetype_id, name)
) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci AS
SELECT
card AS name,
season.season_id,
d.archetype_id,
SUM(CASE WHEN d.id IS NOT NULL THEN 1 ELSE 0 END) AS num_decks,
IFNULL(SUM(dsum.wins), 0) AS wins,
IFNULL(SUM(dsum.losses), 0) AS losses,
IFNULL(SUM(dsum.draws), 0) AS draws,
SUM(CASE WHEN dsum.wins >= 5 AND dsum.losses = 0 AND d.source_id IN (SELECT id FROM source WHERE name = 'League') THEN 1 ELSE 0 END) AS perfect_runs,
SUM(CASE WHEN dsum.finish = 1 THEN 1 ELSE 0 END) AS tournament_wins,
SUM(CASE WHEN dsum.finish <= 8 THEN 1 ELSE 0 END) AS tournament_top8s,
(CASE WHEN ct.name = 'League' THEN 'league' WHEN ct.name = 'Gatherling' THEN 'tournament' ELSE 'other' END) AS deck_type
FROM
deck AS d
INNER JOIN
-- Eliminate maindeck/sideboard double-counting with DISTINCT. See #5493.
(SELECT DISTINCT card, deck_id FROM deck_card) AS dc ON d.id = dc.deck_id
LEFT JOIN
competition AS c ON d.competition_id = c.id
LEFT JOIN
competition_series AS cs ON cs.id = c.competition_series_id
LEFT JOIN
competition_type AS ct ON ct.id = cs.competition_type_id
LEFT JOIN deck_cache AS season ON d.id = season.deck_id
LEFT JOIN
(
SELECT
d.id,
d.created_date,
d.finish,
SUM(CASE WHEN dm.games > IFNULL(odm.games, 0) THEN 1 ELSE 0 END) AS wins, -- IFNULL so we still count byes as wins.
SUM(CASE WHEN dm.games < odm.games THEN 1 ELSE 0 END) AS losses,
SUM(CASE WHEN dm.games = odm.games THEN 1 ELSE 0 END) AS draws
FROM
deck_match AS dm
INNER JOIN
deck_match AS odm ON dm.match_id = odm.match_id AND dm.deck_id <> odm.deck_id
INNER JOIN
deck AS d ON d.id = dm.deck_id
GROUP BY
d.id
) AS dsum ON d.id = dsum.id
WHERE
d.archetype_id IS NOT NULL
GROUP BY
card,
d.archetype_id,
season.season_id,
ct.name
```
[]
(slow_query, 640.5, mysql)
Reported on decksite by mysql-perf
Location Hash: f2991d02069e5a1fa5707770fd42095b662f4c3c
Labels: decksite
Exceeded slow_query limit (654.9 > 60.0) in mysql: ```
CREATE TABLE IF NOT EXISTS _new_card_archetype_stats (
name VARCHAR(190) NOT NULL,
season_id INT NOT NULL,
archetype_id INT NOT NULL,
wins INT NOT NULL,
losses INT NOT NULL,
draws INT NOT NULL,
perfect_runs INT NOT NULL,
tournament_wins INT NOT NULL,
tournament_top8s INT NOT NULL,
deck_type ENUM('league', 'tournament', 'other') NOT NULL,
PRIMARY KEY (season_id, archetype_id, name, deck_type),
FOREIGN KEY (season_id) REFERENCES season (id) ON UPDATE CASCADE ON DELETE CASCADE,
FOREIGN KEY (archetype_id) REFERENCES archetype (id) ON UPDATE CASCADE ON DELETE CASCADE,
INDEX idx_archetype_id_name (archetype_id, name)
) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci AS
SELECT
card AS name,
season.season_id,
d.archetype_id,
SUM(CASE WHEN d.id IS NOT NULL THEN 1 ELSE 0 END) AS num_decks,
IFNULL(SUM(dsum.wins), 0) AS wins,
IFNULL(SUM(dsum.losses), 0) AS losses,
IFNULL(SUM(dsum.draws), 0) AS draws,
SUM(CASE WHEN dsum.wins >= 5 AND dsum.losses = 0 AND d.source_id IN (SELECT id FROM source WHERE name = 'League') THEN 1 ELSE 0 END) AS perfect_runs,
SUM(CASE WHEN dsum.finish = 1 THEN 1 ELSE 0 END) AS tournament_wins,
SUM(CASE WHEN dsum.finish <= 8 THEN 1 ELSE 0 END) AS tournament_top8s,
(CASE WHEN ct.name = 'League' THEN 'league' WHEN ct.name = 'Gatherling' THEN 'tournament' ELSE 'other' END) AS deck_type
FROM
deck AS d
INNER JOIN
-- Eliminate maindeck/sideboard double-counting with DISTINCT. See #5493.
(SELECT DISTINCT card, deck_id FROM deck_card) AS dc ON d.id = dc.deck_id
LEFT JOIN
competition AS c ON d.competition_id = c.id
LEFT JOIN
competition_series AS cs ON cs.id = c.competition_series_id
LEFT JOIN
competition_type AS ct ON ct.id = cs.competition_type_id
LEFT JOIN deck_cache AS season ON d.id = season.deck_id
LEFT JOIN
(
SELECT
d.id,
d.created_date,
d.finish,
SUM(CASE WHEN dm.games > IFNULL(odm.games, 0) THEN 1 ELSE 0 END) AS wins, -- IFNULL so we still count byes as wins.
SUM(CASE WHEN dm.games < odm.games THEN 1 ELSE 0 END) AS losses,
SUM(CASE WHEN dm.games = odm.games THEN 1 ELSE 0 END) AS draws
FROM
deck_match AS dm
INNER JOIN
deck_match AS odm ON dm.match_id = odm.match_id AND dm.deck_id <> odm.deck_id
INNER JOIN
deck AS d ON d.id = dm.deck_id
GROUP BY
d.id
) AS dsum ON d.id = dsum.id
WHERE
d.archetype_id IS NOT NULL
GROUP BY
card,
d.archetype_id,
season.season_id,
ct.name
```
[]
(slow_query, 654.9, mysql)
Reported on decksite by mysql-perf
Location Hash: f2991d02069e5a1fa5707770fd42095b662f4c3c
Labels: decksite
Exceeded slow_query limit (652.2 > 60.0) in mysql: ```
CREATE TABLE IF NOT EXISTS _new_card_archetype_stats (
name VARCHAR(190) NOT NULL,
season_id INT NOT NULL,
archetype_id INT NOT NULL,
wins INT NOT NULL,
losses INT NOT NULL,
draws INT NOT NULL,
perfect_runs INT NOT NULL,
tournament_wins INT NOT NULL,
tournament_top8s INT NOT NULL,
deck_type ENUM('league', 'tournament', 'other') NOT NULL,
PRIMARY KEY (season_id, archetype_id, name, deck_type),
FOREIGN KEY (season_id) REFERENCES season (id) ON UPDATE CASCADE ON DELETE CASCADE,
FOREIGN KEY (archetype_id) REFERENCES archetype (id) ON UPDATE CASCADE ON DELETE CASCADE,
INDEX idx_archetype_id_name (archetype_id, name)
) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci AS
SELECT
card AS name,
season.season_id,
d.archetype_id,
SUM(CASE WHEN d.id IS NOT NULL THEN 1 ELSE 0 END) AS num_decks,
IFNULL(SUM(dsum.wins), 0) AS wins,
IFNULL(SUM(dsum.losses), 0) AS losses,
IFNULL(SUM(dsum.draws), 0) AS draws,
SUM(CASE WHEN dsum.wins >= 5 AND dsum.losses = 0 AND d.source_id IN (SELECT id FROM source WHERE name = 'League') THEN 1 ELSE 0 END) AS perfect_runs,
SUM(CASE WHEN dsum.finish = 1 THEN 1 ELSE 0 END) AS tournament_wins,
SUM(CASE WHEN dsum.finish <= 8 THEN 1 ELSE 0 END) AS tournament_top8s,
(CASE WHEN ct.name = 'League' THEN 'league' WHEN ct.name = 'Gatherling' THEN 'tournament' ELSE 'other' END) AS deck_type
FROM
deck AS d
INNER JOIN
-- Eliminate maindeck/sideboard double-counting with DISTINCT. See #5493.
(SELECT DISTINCT card, deck_id FROM deck_card) AS dc ON d.id = dc.deck_id
LEFT JOIN
competition AS c ON d.competition_id = c.id
LEFT JOIN
competition_series AS cs ON cs.id = c.competition_series_id
LEFT JOIN
competition_type AS ct ON ct.id = cs.competition_type_id
LEFT JOIN deck_cache AS season ON d.id = season.deck_id
LEFT JOIN
(
SELECT
d.id,
d.created_date,
d.finish,
SUM(CASE WHEN dm.games > IFNULL(odm.games, 0) THEN 1 ELSE 0 END) AS wins, -- IFNULL so we still count byes as wins.
SUM(CASE WHEN dm.games < odm.games THEN 1 ELSE 0 END) AS losses,
SUM(CASE WHEN dm.games = odm.games THEN 1 ELSE 0 END) AS draws
FROM
deck_match AS dm
INNER JOIN
deck_match AS odm ON dm.match_id = odm.match_id AND dm.deck_id <> odm.deck_id
INNER JOIN
deck AS d ON d.id = dm.deck_id
GROUP BY
d.id
) AS dsum ON d.id = dsum.id
WHERE
d.archetype_id IS NOT NULL
GROUP BY
card,
d.archetype_id,
season.season_id,
ct.name
```
[]
(slow_query, 652.2, mysql)
Reported on decksite by mysql-perf
Location Hash: f2991d02069e5a1fa5707770fd42095b662f4c3c
Labels: decksite
Exceeded slow_query limit (677.8 > 60.0) in mysql: ```
CREATE TABLE IF NOT EXISTS _new_card_archetype_stats (
name VARCHAR(190) NOT NULL,
season_id INT NOT NULL,
archetype_id INT NOT NULL,
wins INT NOT NULL,
losses INT NOT NULL,
draws INT NOT NULL,
perfect_runs INT NOT NULL,
tournament_wins INT NOT NULL,
tournament_top8s INT NOT NULL,
deck_type ENUM('league', 'tournament', 'other') NOT NULL,
PRIMARY KEY (season_id, archetype_id, name, deck_type),
FOREIGN KEY (season_id) REFERENCES season (id) ON UPDATE CASCADE ON DELETE CASCADE,
FOREIGN KEY (archetype_id) REFERENCES archetype (id) ON UPDATE CASCADE ON DELETE CASCADE,
INDEX idx_archetype_id_name (archetype_id, name)
) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci AS
SELECT
card AS name,
season.season_id,
d.archetype_id,
SUM(CASE WHEN d.id IS NOT NULL THEN 1 ELSE 0 END) AS num_decks,
IFNULL(SUM(dsum.wins), 0) AS wins,
IFNULL(SUM(dsum.losses), 0) AS losses,
IFNULL(SUM(dsum.draws), 0) AS draws,
SUM(CASE WHEN dsum.wins >= 5 AND dsum.losses = 0 AND d.source_id IN (SELECT id FROM source WHERE name = 'League') THEN 1 ELSE 0 END) AS perfect_runs,
SUM(CASE WHEN dsum.finish = 1 THEN 1 ELSE 0 END) AS tournament_wins,
SUM(CASE WHEN dsum.finish <= 8 THEN 1 ELSE 0 END) AS tournament_top8s,
(CASE WHEN ct.name = 'League' THEN 'league' WHEN ct.name = 'Gatherling' THEN 'tournament' ELSE 'other' END) AS deck_type
FROM
deck AS d
INNER JOIN
-- Eliminate maindeck/sideboard double-counting with DISTINCT. See #5493.
(SELECT DISTINCT card, deck_id FROM deck_card) AS dc ON d.id = dc.deck_id
LEFT JOIN
competition AS c ON d.competition_id = c.id
LEFT JOIN
competition_series AS cs ON cs.id = c.competition_series_id
LEFT JOIN
competition_type AS ct ON ct.id = cs.competition_type_id
LEFT JOIN deck_cache AS season ON d.id = season.deck_id
LEFT JOIN
(
SELECT
d.id,
d.created_date,
d.finish,
SUM(CASE WHEN dm.games > IFNULL(odm.games, 0) THEN 1 ELSE 0 END) AS wins, -- IFNULL so we still count byes as wins.
SUM(CASE WHEN dm.games < odm.games THEN 1 ELSE 0 END) AS losses,
SUM(CASE WHEN dm.games = odm.games THEN 1 ELSE 0 END) AS draws
FROM
deck_match AS dm
INNER JOIN
deck_match AS odm ON dm.match_id = odm.match_id AND dm.deck_id <> odm.deck_id
INNER JOIN
deck AS d ON d.id = dm.deck_id
GROUP BY
d.id
) AS dsum ON d.id = dsum.id
WHERE
d.archetype_id IS NOT NULL
GROUP BY
card,
d.archetype_id,
season.season_id,
ct.name
```
[]
(slow_query, 677.8, mysql)
Reported on decksite by mysql-perf
Location Hash: f2991d02069e5a1fa5707770fd42095b662f4c3c
Labels: decksite
Exceeded slow_query limit (716.8 > 60.0) in mysql: ```
CREATE TABLE IF NOT EXISTS _new_card_archetype_stats (
name VARCHAR(190) NOT NULL,
season_id INT NOT NULL,
archetype_id INT NOT NULL,
wins INT NOT NULL,
losses INT NOT NULL,
draws INT NOT NULL,
perfect_runs INT NOT NULL,
tournament_wins INT NOT NULL,
tournament_top8s INT NOT NULL,
deck_type ENUM('league', 'tournament', 'other') NOT NULL,
PRIMARY KEY (season_id, archetype_id, name, deck_type),
FOREIGN KEY (season_id) REFERENCES season (id) ON UPDATE CASCADE ON DELETE CASCADE,
FOREIGN KEY (archetype_id) REFERENCES archetype (id) ON UPDATE CASCADE ON DELETE CASCADE,
INDEX idx_archetype_id_name (archetype_id, name)
) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci AS
SELECT
card AS name,
season.season_id,
d.archetype_id,
SUM(CASE WHEN d.id IS NOT NULL THEN 1 ELSE 0 END) AS num_decks,
IFNULL(SUM(dsum.wins), 0) AS wins,
IFNULL(SUM(dsum.losses), 0) AS losses,
IFNULL(SUM(dsum.draws), 0) AS draws,
SUM(CASE WHEN dsum.wins >= 5 AND dsum.losses = 0 AND d.source_id IN (SELECT id FROM source WHERE name = 'League') THEN 1 ELSE 0 END) AS perfect_runs,
SUM(CASE WHEN dsum.finish = 1 THEN 1 ELSE 0 END) AS tournament_wins,
SUM(CASE WHEN dsum.finish <= 8 THEN 1 ELSE 0 END) AS tournament_top8s,
(CASE WHEN ct.name = 'League' THEN 'league' WHEN ct.name = 'Gatherling' THEN 'tournament' ELSE 'other' END) AS deck_type
FROM
deck AS d
INNER JOIN
-- Eliminate maindeck/sideboard double-counting with DISTINCT. See #5493.
(SELECT DISTINCT card, deck_id FROM deck_card) AS dc ON d.id = dc.deck_id
LEFT JOIN
competition AS c ON d.competition_id = c.id
LEFT JOIN
competition_series AS cs ON cs.id = c.competition_series_id
LEFT JOIN
competition_type AS ct ON ct.id = cs.competition_type_id
LEFT JOIN deck_cache AS season ON d.id = season.deck_id
LEFT JOIN
(
SELECT
d.id,
d.created_date,
d.finish,
SUM(CASE WHEN dm.games > IFNULL(odm.games, 0) THEN 1 ELSE 0 END) AS wins, -- IFNULL so we still count byes as wins.
SUM(CASE WHEN dm.games < odm.games THEN 1 ELSE 0 END) AS losses,
SUM(CASE WHEN dm.games = odm.games THEN 1 ELSE 0 END) AS draws
FROM
deck_match AS dm
INNER JOIN
deck_match AS odm ON dm.match_id = odm.match_id AND dm.deck_id <> odm.deck_id
INNER JOIN
deck AS d ON d.id = dm.deck_id
GROUP BY
d.id
) AS dsum ON d.id = dsum.id
WHERE
d.archetype_id IS NOT NULL
GROUP BY
card,
d.archetype_id,
season.season_id,
ct.name
```
[]
(slow_query, 716.8, mysql)
Reported on decksite by mysql-perf
Location Hash: f2991d02069e5a1fa5707770fd42095b662f4c3c
Labels: decksite
Exceeded slow_query limit (703.0 > 60.0) in mysql: ```
CREATE TABLE IF NOT EXISTS _new_card_archetype_stats (
name VARCHAR(190) NOT NULL,
season_id INT NOT NULL,
archetype_id INT NOT NULL,
wins INT NOT NULL,
losses INT NOT NULL,
draws INT NOT NULL,
perfect_runs INT NOT NULL,
tournament_wins INT NOT NULL,
tournament_top8s INT NOT NULL,
deck_type ENUM('league', 'tournament', 'other') NOT NULL,
PRIMARY KEY (season_id, archetype_id, name, deck_type),
FOREIGN KEY (season_id) REFERENCES season (id) ON UPDATE CASCADE ON DELETE CASCADE,
FOREIGN KEY (archetype_id) REFERENCES archetype (id) ON UPDATE CASCADE ON DELETE CASCADE,
INDEX idx_archetype_id_name (archetype_id, name)
) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci AS
SELECT
card AS name,
season.season_id,
d.archetype_id,
SUM(CASE WHEN d.id IS NOT NULL THEN 1 ELSE 0 END) AS num_decks,
IFNULL(SUM(dsum.wins), 0) AS wins,
IFNULL(SUM(dsum.losses), 0) AS losses,
IFNULL(SUM(dsum.draws), 0) AS draws,
SUM(CASE WHEN dsum.wins >= 5 AND dsum.losses = 0 AND d.source_id IN (SELECT id FROM source WHERE name = 'League') THEN 1 ELSE 0 END) AS perfect_runs,
SUM(CASE WHEN dsum.finish = 1 THEN 1 ELSE 0 END) AS tournament_wins,
SUM(CASE WHEN dsum.finish <= 8 THEN 1 ELSE 0 END) AS tournament_top8s,
(CASE WHEN ct.name = 'League' THEN 'league' WHEN ct.name = 'Gatherling' THEN 'tournament' ELSE 'other' END) AS deck_type
FROM
deck AS d
INNER JOIN
-- Eliminate maindeck/sideboard double-counting with DISTINCT. See #5493.
(SELECT DISTINCT card, deck_id FROM deck_card) AS dc ON d.id = dc.deck_id
LEFT JOIN
competition AS c ON d.competition_id = c.id
LEFT JOIN
competition_series AS cs ON cs.id = c.competition_series_id
LEFT JOIN
competition_type AS ct ON ct.id = cs.competition_type_id
LEFT JOIN deck_cache AS season ON d.id = season.deck_id
LEFT JOIN
(
SELECT
d.id,
d.created_date,
d.finish,
SUM(CASE WHEN dm.games > IFNULL(odm.games, 0) THEN 1 ELSE 0 END) AS wins, -- IFNULL so we still count byes as wins.
SUM(CASE WHEN dm.games < odm.games THEN 1 ELSE 0 END) AS losses,
SUM(CASE WHEN dm.games = odm.games THEN 1 ELSE 0 END) AS draws
FROM
deck_match AS dm
INNER JOIN
deck_match AS odm ON dm.match_id = odm.match_id AND dm.deck_id <> odm.deck_id
INNER JOIN
deck AS d ON d.id = dm.deck_id
GROUP BY
d.id
) AS dsum ON d.id = dsum.id
WHERE
d.archetype_id IS NOT NULL
GROUP BY
card,
d.archetype_id,
season.season_id,
ct.name
```
[]
(slow_query, 703.0, mysql)
Reported on decksite by mysql-perf
Location Hash: f2991d02069e5a1fa5707770fd42095b662f4c3c
Labels: decksite
[]
(slow_query, 759.5, mysql)Reported on decksite by mysql-perf
Location Hash: f2991d02069e5a1fa5707770fd42095b662f4c3c