Open vorpal-buildbot opened 2 months ago
Exceeded slow_query limit (65.7 > 60.0) in mysql: ```
CREATE TABLE IF NOT EXISTS _new_matchup_stats (
archetype_id INT NOT NULL,
opponent_archetype_id INT NOT NULL,
season_id INT NOT NULL,
wins INT NOT NULL,
losses INT NOT NULL,
draws INT NOT NULL,
deck_type ENUM('league', 'tournament', 'other') NOT NULL,
PRIMARY KEY (season_id, archetype_id, opponent_archetype_id, 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,
FOREIGN KEY (opponent_archetype_id) REFERENCES archetype (id) ON UPDATE CASCADE ON DELETE CASCADE
) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci AS
SELECT
a.id AS archetype_id,
oa.id AS opponent_archetype_id,
season.season_id,
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,
(CASE WHEN ct.name = 'League' THEN 'league' WHEN ct.name = 'Gatherling' THEN 'tournament' ELSE 'other' END) AS deck_type
FROM
archetype AS a
INNER JOIN
deck AS d ON d.archetype_id IN (SELECT descendant FROM archetype_closure WHERE ancestor = a.id)
INNER JOIN
deck_match AS dm ON d.id = dm.deck_id
INNER JOIN
deck_match AS odm ON dm.match_id = odm.match_id AND odm.deck_id <> d.id
INNER JOIN
deck AS od ON od.id = odm.deck_id
INNER JOIN
archetype AS oa ON od.archetype_id IN (SELECT descendant FROM archetype_closure WHERE ancestor = oa.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
GROUP BY
a.id,
oa.id,
season.season_id,
ct.name
```
[]
(slow_query, 65.7, mysql)
Reported on decksite by mysql-perf
Location Hash: 5c8a85b213df89d7a387ee22284c50e99ddb0591
Labels: decksite
Exceeded slow_query limit (68.9 > 60.0) in mysql: ```
CREATE TABLE IF NOT EXISTS _new_matchup_stats (
archetype_id INT NOT NULL,
opponent_archetype_id INT NOT NULL,
season_id INT NOT NULL,
wins INT NOT NULL,
losses INT NOT NULL,
draws INT NOT NULL,
deck_type ENUM('league', 'tournament', 'other') NOT NULL,
PRIMARY KEY (season_id, archetype_id, opponent_archetype_id, 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,
FOREIGN KEY (opponent_archetype_id) REFERENCES archetype (id) ON UPDATE CASCADE ON DELETE CASCADE
) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci AS
SELECT
a.id AS archetype_id,
oa.id AS opponent_archetype_id,
season.season_id,
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,
(CASE WHEN ct.name = 'League' THEN 'league' WHEN ct.name = 'Gatherling' THEN 'tournament' ELSE 'other' END) AS deck_type
FROM
archetype AS a
INNER JOIN
deck AS d ON d.archetype_id IN (SELECT descendant FROM archetype_closure WHERE ancestor = a.id)
INNER JOIN
deck_match AS dm ON d.id = dm.deck_id
INNER JOIN
deck_match AS odm ON dm.match_id = odm.match_id AND odm.deck_id <> d.id
INNER JOIN
deck AS od ON od.id = odm.deck_id
INNER JOIN
archetype AS oa ON od.archetype_id IN (SELECT descendant FROM archetype_closure WHERE ancestor = oa.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
GROUP BY
a.id,
oa.id,
season.season_id,
ct.name
```
[]
(slow_query, 68.9, mysql)
Reported on decksite by mysql-perf
Location Hash: 5c8a85b213df89d7a387ee22284c50e99ddb0591
Labels: decksite
Exceeded slow_query limit (65.0 > 60.0) in mysql: ```
CREATE TABLE IF NOT EXISTS _new_matchup_stats (
archetype_id INT NOT NULL,
opponent_archetype_id INT NOT NULL,
season_id INT NOT NULL,
wins INT NOT NULL,
losses INT NOT NULL,
draws INT NOT NULL,
deck_type ENUM('league', 'tournament', 'other') NOT NULL,
PRIMARY KEY (season_id, archetype_id, opponent_archetype_id, 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,
FOREIGN KEY (opponent_archetype_id) REFERENCES archetype (id) ON UPDATE CASCADE ON DELETE CASCADE
) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci AS
SELECT
a.id AS archetype_id,
oa.id AS opponent_archetype_id,
season.season_id,
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,
(CASE WHEN ct.name = 'League' THEN 'league' WHEN ct.name = 'Gatherling' THEN 'tournament' ELSE 'other' END) AS deck_type
FROM
archetype AS a
INNER JOIN
deck AS d ON d.archetype_id IN (SELECT descendant FROM archetype_closure WHERE ancestor = a.id)
INNER JOIN
deck_match AS dm ON d.id = dm.deck_id
INNER JOIN
deck_match AS odm ON dm.match_id = odm.match_id AND odm.deck_id <> d.id
INNER JOIN
deck AS od ON od.id = odm.deck_id
INNER JOIN
archetype AS oa ON od.archetype_id IN (SELECT descendant FROM archetype_closure WHERE ancestor = oa.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
GROUP BY
a.id,
oa.id,
season.season_id,
ct.name
```
[]
(slow_query, 65.0, mysql)
Reported on decksite by mysql-perf
Location Hash: 5c8a85b213df89d7a387ee22284c50e99ddb0591
Labels: decksite
Exceeded slow_query limit (79.8 > 60.0) in mysql: ```
CREATE TABLE IF NOT EXISTS _new_matchup_stats (
archetype_id INT NOT NULL,
opponent_archetype_id INT NOT NULL,
season_id INT NOT NULL,
wins INT NOT NULL,
losses INT NOT NULL,
draws INT NOT NULL,
deck_type ENUM('league', 'tournament', 'other') NOT NULL,
PRIMARY KEY (season_id, archetype_id, opponent_archetype_id, 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,
FOREIGN KEY (opponent_archetype_id) REFERENCES archetype (id) ON UPDATE CASCADE ON DELETE CASCADE
) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci AS
SELECT
a.id AS archetype_id,
oa.id AS opponent_archetype_id,
season.season_id,
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,
(CASE WHEN ct.name = 'League' THEN 'league' WHEN ct.name = 'Gatherling' THEN 'tournament' ELSE 'other' END) AS deck_type
FROM
archetype AS a
INNER JOIN
deck AS d ON d.archetype_id IN (SELECT descendant FROM archetype_closure WHERE ancestor = a.id)
INNER JOIN
deck_match AS dm ON d.id = dm.deck_id
INNER JOIN
deck_match AS odm ON dm.match_id = odm.match_id AND odm.deck_id <> d.id
INNER JOIN
deck AS od ON od.id = odm.deck_id
INNER JOIN
archetype AS oa ON od.archetype_id IN (SELECT descendant FROM archetype_closure WHERE ancestor = oa.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
GROUP BY
a.id,
oa.id,
season.season_id,
ct.name
```
[]
(slow_query, 79.8, mysql)
Reported on decksite by mysql-perf
Location Hash: 5c8a85b213df89d7a387ee22284c50e99ddb0591
Labels: decksite
Exceeded slow_query limit (75.3 > 60.0) in mysql: ```
CREATE TABLE IF NOT EXISTS _new_matchup_stats (
archetype_id INT NOT NULL,
opponent_archetype_id INT NOT NULL,
season_id INT NOT NULL,
wins INT NOT NULL,
losses INT NOT NULL,
draws INT NOT NULL,
deck_type ENUM('league', 'tournament', 'other') NOT NULL,
PRIMARY KEY (season_id, archetype_id, opponent_archetype_id, 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,
FOREIGN KEY (opponent_archetype_id) REFERENCES archetype (id) ON UPDATE CASCADE ON DELETE CASCADE
) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci AS
SELECT
a.id AS archetype_id,
oa.id AS opponent_archetype_id,
season.season_id,
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,
(CASE WHEN ct.name = 'League' THEN 'league' WHEN ct.name = 'Gatherling' THEN 'tournament' ELSE 'other' END) AS deck_type
FROM
archetype AS a
INNER JOIN
deck AS d ON d.archetype_id IN (SELECT descendant FROM archetype_closure WHERE ancestor = a.id)
INNER JOIN
deck_match AS dm ON d.id = dm.deck_id
INNER JOIN
deck_match AS odm ON dm.match_id = odm.match_id AND odm.deck_id <> d.id
INNER JOIN
deck AS od ON od.id = odm.deck_id
INNER JOIN
archetype AS oa ON od.archetype_id IN (SELECT descendant FROM archetype_closure WHERE ancestor = oa.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
GROUP BY
a.id,
oa.id,
season.season_id,
ct.name
```
[]
(slow_query, 75.3, mysql)
Reported on decksite by mysql-perf
Location Hash: 5c8a85b213df89d7a387ee22284c50e99ddb0591
Labels: decksite
Exceeded slow_query limit (77.7 > 60.0) in mysql: ```
CREATE TABLE IF NOT EXISTS _new_matchup_stats (
archetype_id INT NOT NULL,
opponent_archetype_id INT NOT NULL,
season_id INT NOT NULL,
wins INT NOT NULL,
losses INT NOT NULL,
draws INT NOT NULL,
deck_type ENUM('league', 'tournament', 'other') NOT NULL,
PRIMARY KEY (season_id, archetype_id, opponent_archetype_id, 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,
FOREIGN KEY (opponent_archetype_id) REFERENCES archetype (id) ON UPDATE CASCADE ON DELETE CASCADE
) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci AS
SELECT
a.id AS archetype_id,
oa.id AS opponent_archetype_id,
season.season_id,
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,
(CASE WHEN ct.name = 'League' THEN 'league' WHEN ct.name = 'Gatherling' THEN 'tournament' ELSE 'other' END) AS deck_type
FROM
archetype AS a
INNER JOIN
deck AS d ON d.archetype_id IN (SELECT descendant FROM archetype_closure WHERE ancestor = a.id)
INNER JOIN
deck_match AS dm ON d.id = dm.deck_id
INNER JOIN
deck_match AS odm ON dm.match_id = odm.match_id AND odm.deck_id <> d.id
INNER JOIN
deck AS od ON od.id = odm.deck_id
INNER JOIN
archetype AS oa ON od.archetype_id IN (SELECT descendant FROM archetype_closure WHERE ancestor = oa.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
GROUP BY
a.id,
oa.id,
season.season_id,
ct.name
```
[]
(slow_query, 77.7, mysql)
Reported on decksite by mysql-perf
Location Hash: 5c8a85b213df89d7a387ee22284c50e99ddb0591
Labels: decksite
Exceeded slow_query limit (70.7 > 60.0) in mysql: ```
CREATE TABLE IF NOT EXISTS _new_matchup_stats (
archetype_id INT NOT NULL,
opponent_archetype_id INT NOT NULL,
season_id INT NOT NULL,
wins INT NOT NULL,
losses INT NOT NULL,
draws INT NOT NULL,
deck_type ENUM('league', 'tournament', 'other') NOT NULL,
PRIMARY KEY (season_id, archetype_id, opponent_archetype_id, 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,
FOREIGN KEY (opponent_archetype_id) REFERENCES archetype (id) ON UPDATE CASCADE ON DELETE CASCADE
) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci AS
SELECT
a.id AS archetype_id,
oa.id AS opponent_archetype_id,
season.season_id,
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,
(CASE WHEN ct.name = 'League' THEN 'league' WHEN ct.name = 'Gatherling' THEN 'tournament' ELSE 'other' END) AS deck_type
FROM
archetype AS a
INNER JOIN
deck AS d ON d.archetype_id IN (SELECT descendant FROM archetype_closure WHERE ancestor = a.id)
INNER JOIN
deck_match AS dm ON d.id = dm.deck_id
INNER JOIN
deck_match AS odm ON dm.match_id = odm.match_id AND odm.deck_id <> d.id
INNER JOIN
deck AS od ON od.id = odm.deck_id
INNER JOIN
archetype AS oa ON od.archetype_id IN (SELECT descendant FROM archetype_closure WHERE ancestor = oa.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
GROUP BY
a.id,
oa.id,
season.season_id,
ct.name
```
[]
(slow_query, 70.7, mysql)
Reported on decksite by mysql-perf
Location Hash: 5c8a85b213df89d7a387ee22284c50e99ddb0591
Labels: decksite
Exceeded slow_query limit (64.1 > 60.0) in mysql: ```
CREATE TABLE IF NOT EXISTS _new_matchup_stats (
archetype_id INT NOT NULL,
opponent_archetype_id INT NOT NULL,
season_id INT NOT NULL,
wins INT NOT NULL,
losses INT NOT NULL,
draws INT NOT NULL,
deck_type ENUM('league', 'tournament', 'other') NOT NULL,
PRIMARY KEY (season_id, archetype_id, opponent_archetype_id, 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,
FOREIGN KEY (opponent_archetype_id) REFERENCES archetype (id) ON UPDATE CASCADE ON DELETE CASCADE
) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci AS
SELECT
a.id AS archetype_id,
oa.id AS opponent_archetype_id,
season.season_id,
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,
(CASE WHEN ct.name = 'League' THEN 'league' WHEN ct.name = 'Gatherling' THEN 'tournament' ELSE 'other' END) AS deck_type
FROM
archetype AS a
INNER JOIN
deck AS d ON d.archetype_id IN (SELECT descendant FROM archetype_closure WHERE ancestor = a.id)
INNER JOIN
deck_match AS dm ON d.id = dm.deck_id
INNER JOIN
deck_match AS odm ON dm.match_id = odm.match_id AND odm.deck_id <> d.id
INNER JOIN
deck AS od ON od.id = odm.deck_id
INNER JOIN
archetype AS oa ON od.archetype_id IN (SELECT descendant FROM archetype_closure WHERE ancestor = oa.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
GROUP BY
a.id,
oa.id,
season.season_id,
ct.name
```
[]
(slow_query, 64.1, mysql)
Reported on decksite by mysql-perf
Location Hash: 5c8a85b213df89d7a387ee22284c50e99ddb0591
Labels: decksite
Exceeded slow_query limit (67.2 > 60.0) in mysql: ```
CREATE TABLE IF NOT EXISTS _new_matchup_stats (
archetype_id INT NOT NULL,
opponent_archetype_id INT NOT NULL,
season_id INT NOT NULL,
wins INT NOT NULL,
losses INT NOT NULL,
draws INT NOT NULL,
deck_type ENUM('league', 'tournament', 'other') NOT NULL,
PRIMARY KEY (season_id, archetype_id, opponent_archetype_id, 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,
FOREIGN KEY (opponent_archetype_id) REFERENCES archetype (id) ON UPDATE CASCADE ON DELETE CASCADE
) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci AS
SELECT
a.id AS archetype_id,
oa.id AS opponent_archetype_id,
season.season_id,
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,
(CASE WHEN ct.name = 'League' THEN 'league' WHEN ct.name = 'Gatherling' THEN 'tournament' ELSE 'other' END) AS deck_type
FROM
archetype AS a
INNER JOIN
deck AS d ON d.archetype_id IN (SELECT descendant FROM archetype_closure WHERE ancestor = a.id)
INNER JOIN
deck_match AS dm ON d.id = dm.deck_id
INNER JOIN
deck_match AS odm ON dm.match_id = odm.match_id AND odm.deck_id <> d.id
INNER JOIN
deck AS od ON od.id = odm.deck_id
INNER JOIN
archetype AS oa ON od.archetype_id IN (SELECT descendant FROM archetype_closure WHERE ancestor = oa.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
GROUP BY
a.id,
oa.id,
season.season_id,
ct.name
```
[]
(slow_query, 67.2, mysql)
Reported on decksite by mysql-perf
Location Hash: 5c8a85b213df89d7a387ee22284c50e99ddb0591
Labels: decksite
Exceeded slow_query limit (69.2 > 60.0) in mysql: ```
CREATE TABLE IF NOT EXISTS _new_matchup_stats (
archetype_id INT NOT NULL,
opponent_archetype_id INT NOT NULL,
season_id INT NOT NULL,
wins INT NOT NULL,
losses INT NOT NULL,
draws INT NOT NULL,
deck_type ENUM('league', 'tournament', 'other') NOT NULL,
PRIMARY KEY (season_id, archetype_id, opponent_archetype_id, 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,
FOREIGN KEY (opponent_archetype_id) REFERENCES archetype (id) ON UPDATE CASCADE ON DELETE CASCADE
) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci AS
SELECT
a.id AS archetype_id,
oa.id AS opponent_archetype_id,
season.season_id,
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,
(CASE WHEN ct.name = 'League' THEN 'league' WHEN ct.name = 'Gatherling' THEN 'tournament' ELSE 'other' END) AS deck_type
FROM
archetype AS a
INNER JOIN
deck AS d ON d.archetype_id IN (SELECT descendant FROM archetype_closure WHERE ancestor = a.id)
INNER JOIN
deck_match AS dm ON d.id = dm.deck_id
INNER JOIN
deck_match AS odm ON dm.match_id = odm.match_id AND odm.deck_id <> d.id
INNER JOIN
deck AS od ON od.id = odm.deck_id
INNER JOIN
archetype AS oa ON od.archetype_id IN (SELECT descendant FROM archetype_closure WHERE ancestor = oa.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
GROUP BY
a.id,
oa.id,
season.season_id,
ct.name
```
[]
(slow_query, 69.2, mysql)
Reported on decksite by mysql-perf
Location Hash: 5c8a85b213df89d7a387ee22284c50e99ddb0591
Labels: decksite
Exceeded slow_query limit (67.3 > 60.0) in mysql: ```
CREATE TABLE IF NOT EXISTS _new_matchup_stats (
archetype_id INT NOT NULL,
opponent_archetype_id INT NOT NULL,
season_id INT NOT NULL,
wins INT NOT NULL,
losses INT NOT NULL,
draws INT NOT NULL,
deck_type ENUM('league', 'tournament', 'other') NOT NULL,
PRIMARY KEY (season_id, archetype_id, opponent_archetype_id, 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,
FOREIGN KEY (opponent_archetype_id) REFERENCES archetype (id) ON UPDATE CASCADE ON DELETE CASCADE
) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci AS
SELECT
a.id AS archetype_id,
oa.id AS opponent_archetype_id,
season.season_id,
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,
(CASE WHEN ct.name = 'League' THEN 'league' WHEN ct.name = 'Gatherling' THEN 'tournament' ELSE 'other' END) AS deck_type
FROM
archetype AS a
INNER JOIN
deck AS d ON d.archetype_id IN (SELECT descendant FROM archetype_closure WHERE ancestor = a.id)
INNER JOIN
deck_match AS dm ON d.id = dm.deck_id
INNER JOIN
deck_match AS odm ON dm.match_id = odm.match_id AND odm.deck_id <> d.id
INNER JOIN
deck AS od ON od.id = odm.deck_id
INNER JOIN
archetype AS oa ON od.archetype_id IN (SELECT descendant FROM archetype_closure WHERE ancestor = oa.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
GROUP BY
a.id,
oa.id,
season.season_id,
ct.name
```
[]
(slow_query, 67.3, mysql)
Reported on decksite by mysql-perf
Location Hash: 5c8a85b213df89d7a387ee22284c50e99ddb0591
Labels: decksite
Exceeded slow_query limit (68.5 > 60.0) in mysql: ```
CREATE TABLE IF NOT EXISTS _new_matchup_stats (
archetype_id INT NOT NULL,
opponent_archetype_id INT NOT NULL,
season_id INT NOT NULL,
wins INT NOT NULL,
losses INT NOT NULL,
draws INT NOT NULL,
deck_type ENUM('league', 'tournament', 'other') NOT NULL,
PRIMARY KEY (season_id, archetype_id, opponent_archetype_id, 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,
FOREIGN KEY (opponent_archetype_id) REFERENCES archetype (id) ON UPDATE CASCADE ON DELETE CASCADE
) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci AS
SELECT
a.id AS archetype_id,
oa.id AS opponent_archetype_id,
season.season_id,
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,
(CASE WHEN ct.name = 'League' THEN 'league' WHEN ct.name = 'Gatherling' THEN 'tournament' ELSE 'other' END) AS deck_type
FROM
archetype AS a
INNER JOIN
deck AS d ON d.archetype_id IN (SELECT descendant FROM archetype_closure WHERE ancestor = a.id)
INNER JOIN
deck_match AS dm ON d.id = dm.deck_id
INNER JOIN
deck_match AS odm ON dm.match_id = odm.match_id AND odm.deck_id <> d.id
INNER JOIN
deck AS od ON od.id = odm.deck_id
INNER JOIN
archetype AS oa ON od.archetype_id IN (SELECT descendant FROM archetype_closure WHERE ancestor = oa.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
GROUP BY
a.id,
oa.id,
season.season_id,
ct.name
```
[]
(slow_query, 68.5, mysql)
Reported on decksite by mysql-perf
Location Hash: 5c8a85b213df89d7a387ee22284c50e99ddb0591
Labels: decksite
Exceeded slow_query limit (67.7 > 60.0) in mysql: ```
CREATE TABLE IF NOT EXISTS _new_matchup_stats (
archetype_id INT NOT NULL,
opponent_archetype_id INT NOT NULL,
season_id INT NOT NULL,
wins INT NOT NULL,
losses INT NOT NULL,
draws INT NOT NULL,
deck_type ENUM('league', 'tournament', 'other') NOT NULL,
PRIMARY KEY (season_id, archetype_id, opponent_archetype_id, 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,
FOREIGN KEY (opponent_archetype_id) REFERENCES archetype (id) ON UPDATE CASCADE ON DELETE CASCADE
) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci AS
SELECT
a.id AS archetype_id,
oa.id AS opponent_archetype_id,
season.season_id,
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,
(CASE WHEN ct.name = 'League' THEN 'league' WHEN ct.name = 'Gatherling' THEN 'tournament' ELSE 'other' END) AS deck_type
FROM
archetype AS a
INNER JOIN
deck AS d ON d.archetype_id IN (SELECT descendant FROM archetype_closure WHERE ancestor = a.id)
INNER JOIN
deck_match AS dm ON d.id = dm.deck_id
INNER JOIN
deck_match AS odm ON dm.match_id = odm.match_id AND odm.deck_id <> d.id
INNER JOIN
deck AS od ON od.id = odm.deck_id
INNER JOIN
archetype AS oa ON od.archetype_id IN (SELECT descendant FROM archetype_closure WHERE ancestor = oa.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
GROUP BY
a.id,
oa.id,
season.season_id,
ct.name
```
[]
(slow_query, 67.7, mysql)
Reported on decksite by mysql-perf
Location Hash: 5c8a85b213df89d7a387ee22284c50e99ddb0591
Labels: decksite
Exceeded slow_query limit (70.2 > 60.0) in mysql: ```
CREATE TABLE IF NOT EXISTS _new_matchup_stats (
archetype_id INT NOT NULL,
opponent_archetype_id INT NOT NULL,
season_id INT NOT NULL,
wins INT NOT NULL,
losses INT NOT NULL,
draws INT NOT NULL,
deck_type ENUM('league', 'tournament', 'other') NOT NULL,
PRIMARY KEY (season_id, archetype_id, opponent_archetype_id, 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,
FOREIGN KEY (opponent_archetype_id) REFERENCES archetype (id) ON UPDATE CASCADE ON DELETE CASCADE
) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci AS
SELECT
a.id AS archetype_id,
oa.id AS opponent_archetype_id,
season.season_id,
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,
(CASE WHEN ct.name = 'League' THEN 'league' WHEN ct.name = 'Gatherling' THEN 'tournament' ELSE 'other' END) AS deck_type
FROM
archetype AS a
INNER JOIN
deck AS d ON d.archetype_id IN (SELECT descendant FROM archetype_closure WHERE ancestor = a.id)
INNER JOIN
deck_match AS dm ON d.id = dm.deck_id
INNER JOIN
deck_match AS odm ON dm.match_id = odm.match_id AND odm.deck_id <> d.id
INNER JOIN
deck AS od ON od.id = odm.deck_id
INNER JOIN
archetype AS oa ON od.archetype_id IN (SELECT descendant FROM archetype_closure WHERE ancestor = oa.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
GROUP BY
a.id,
oa.id,
season.season_id,
ct.name
```
[]
(slow_query, 70.2, mysql)
Reported on decksite by mysql-perf
Location Hash: 5c8a85b213df89d7a387ee22284c50e99ddb0591
Labels: decksite
Exceeded slow_query limit (72.2 > 60.0) in mysql: ```
CREATE TABLE IF NOT EXISTS _new_matchup_stats (
archetype_id INT NOT NULL,
opponent_archetype_id INT NOT NULL,
season_id INT NOT NULL,
wins INT NOT NULL,
losses INT NOT NULL,
draws INT NOT NULL,
deck_type ENUM('league', 'tournament', 'other') NOT NULL,
PRIMARY KEY (season_id, archetype_id, opponent_archetype_id, 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,
FOREIGN KEY (opponent_archetype_id) REFERENCES archetype (id) ON UPDATE CASCADE ON DELETE CASCADE
) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci AS
SELECT
a.id AS archetype_id,
oa.id AS opponent_archetype_id,
season.season_id,
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,
(CASE WHEN ct.name = 'League' THEN 'league' WHEN ct.name = 'Gatherling' THEN 'tournament' ELSE 'other' END) AS deck_type
FROM
archetype AS a
INNER JOIN
deck AS d ON d.archetype_id IN (SELECT descendant FROM archetype_closure WHERE ancestor = a.id)
INNER JOIN
deck_match AS dm ON d.id = dm.deck_id
INNER JOIN
deck_match AS odm ON dm.match_id = odm.match_id AND odm.deck_id <> d.id
INNER JOIN
deck AS od ON od.id = odm.deck_id
INNER JOIN
archetype AS oa ON od.archetype_id IN (SELECT descendant FROM archetype_closure WHERE ancestor = oa.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
GROUP BY
a.id,
oa.id,
season.season_id,
ct.name
```
[]
(slow_query, 72.2, mysql)
Reported on decksite by mysql-perf
Location Hash: 5c8a85b213df89d7a387ee22284c50e99ddb0591
Labels: decksite
Exceeded slow_query limit (67.2 > 60.0) in mysql: ```
CREATE TABLE IF NOT EXISTS _new_matchup_stats (
archetype_id INT NOT NULL,
opponent_archetype_id INT NOT NULL,
season_id INT NOT NULL,
wins INT NOT NULL,
losses INT NOT NULL,
draws INT NOT NULL,
deck_type ENUM('league', 'tournament', 'other') NOT NULL,
PRIMARY KEY (season_id, archetype_id, opponent_archetype_id, 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,
FOREIGN KEY (opponent_archetype_id) REFERENCES archetype (id) ON UPDATE CASCADE ON DELETE CASCADE
) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci AS
SELECT
a.id AS archetype_id,
oa.id AS opponent_archetype_id,
season.season_id,
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,
(CASE WHEN ct.name = 'League' THEN 'league' WHEN ct.name = 'Gatherling' THEN 'tournament' ELSE 'other' END) AS deck_type
FROM
archetype AS a
INNER JOIN
deck AS d ON d.archetype_id IN (SELECT descendant FROM archetype_closure WHERE ancestor = a.id)
INNER JOIN
deck_match AS dm ON d.id = dm.deck_id
INNER JOIN
deck_match AS odm ON dm.match_id = odm.match_id AND odm.deck_id <> d.id
INNER JOIN
deck AS od ON od.id = odm.deck_id
INNER JOIN
archetype AS oa ON od.archetype_id IN (SELECT descendant FROM archetype_closure WHERE ancestor = oa.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
GROUP BY
a.id,
oa.id,
season.season_id,
ct.name
```
[]
(slow_query, 67.2, mysql)
Reported on decksite by mysql-perf
Location Hash: 5c8a85b213df89d7a387ee22284c50e99ddb0591
Labels: decksite
Exceeded slow_query limit (67.2 > 60.0) in mysql: ```
CREATE TABLE IF NOT EXISTS _new_matchup_stats (
archetype_id INT NOT NULL,
opponent_archetype_id INT NOT NULL,
season_id INT NOT NULL,
wins INT NOT NULL,
losses INT NOT NULL,
draws INT NOT NULL,
deck_type ENUM('league', 'tournament', 'other') NOT NULL,
PRIMARY KEY (season_id, archetype_id, opponent_archetype_id, 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,
FOREIGN KEY (opponent_archetype_id) REFERENCES archetype (id) ON UPDATE CASCADE ON DELETE CASCADE
) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci AS
SELECT
a.id AS archetype_id,
oa.id AS opponent_archetype_id,
season.season_id,
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,
(CASE WHEN ct.name = 'League' THEN 'league' WHEN ct.name = 'Gatherling' THEN 'tournament' ELSE 'other' END) AS deck_type
FROM
archetype AS a
INNER JOIN
deck AS d ON d.archetype_id IN (SELECT descendant FROM archetype_closure WHERE ancestor = a.id)
INNER JOIN
deck_match AS dm ON d.id = dm.deck_id
INNER JOIN
deck_match AS odm ON dm.match_id = odm.match_id AND odm.deck_id <> d.id
INNER JOIN
deck AS od ON od.id = odm.deck_id
INNER JOIN
archetype AS oa ON od.archetype_id IN (SELECT descendant FROM archetype_closure WHERE ancestor = oa.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
GROUP BY
a.id,
oa.id,
season.season_id,
ct.name
```
[]
(slow_query, 67.2, mysql)
Reported on decksite by mysql-perf
Location Hash: 5c8a85b213df89d7a387ee22284c50e99ddb0591
Labels: decksite
Exceeded slow_query limit (69.1 > 60.0) in mysql: ```
CREATE TABLE IF NOT EXISTS _new_matchup_stats (
archetype_id INT NOT NULL,
opponent_archetype_id INT NOT NULL,
season_id INT NOT NULL,
wins INT NOT NULL,
losses INT NOT NULL,
draws INT NOT NULL,
deck_type ENUM('league', 'tournament', 'other') NOT NULL,
PRIMARY KEY (season_id, archetype_id, opponent_archetype_id, 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,
FOREIGN KEY (opponent_archetype_id) REFERENCES archetype (id) ON UPDATE CASCADE ON DELETE CASCADE
) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci AS
SELECT
a.id AS archetype_id,
oa.id AS opponent_archetype_id,
season.season_id,
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,
(CASE WHEN ct.name = 'League' THEN 'league' WHEN ct.name = 'Gatherling' THEN 'tournament' ELSE 'other' END) AS deck_type
FROM
archetype AS a
INNER JOIN
deck AS d ON d.archetype_id IN (SELECT descendant FROM archetype_closure WHERE ancestor = a.id)
INNER JOIN
deck_match AS dm ON d.id = dm.deck_id
INNER JOIN
deck_match AS odm ON dm.match_id = odm.match_id AND odm.deck_id <> d.id
INNER JOIN
deck AS od ON od.id = odm.deck_id
INNER JOIN
archetype AS oa ON od.archetype_id IN (SELECT descendant FROM archetype_closure WHERE ancestor = oa.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
GROUP BY
a.id,
oa.id,
season.season_id,
ct.name
```
[]
(slow_query, 69.1, mysql)
Reported on decksite by mysql-perf
Location Hash: 5c8a85b213df89d7a387ee22284c50e99ddb0591
Labels: decksite
Exceeded slow_query limit (67.2 > 60.0) in mysql: ```
CREATE TABLE IF NOT EXISTS _new_matchup_stats (
archetype_id INT NOT NULL,
opponent_archetype_id INT NOT NULL,
season_id INT NOT NULL,
wins INT NOT NULL,
losses INT NOT NULL,
draws INT NOT NULL,
deck_type ENUM('league', 'tournament', 'other') NOT NULL,
PRIMARY KEY (season_id, archetype_id, opponent_archetype_id, 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,
FOREIGN KEY (opponent_archetype_id) REFERENCES archetype (id) ON UPDATE CASCADE ON DELETE CASCADE
) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci AS
SELECT
a.id AS archetype_id,
oa.id AS opponent_archetype_id,
season.season_id,
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,
(CASE WHEN ct.name = 'League' THEN 'league' WHEN ct.name = 'Gatherling' THEN 'tournament' ELSE 'other' END) AS deck_type
FROM
archetype AS a
INNER JOIN
deck AS d ON d.archetype_id IN (SELECT descendant FROM archetype_closure WHERE ancestor = a.id)
INNER JOIN
deck_match AS dm ON d.id = dm.deck_id
INNER JOIN
deck_match AS odm ON dm.match_id = odm.match_id AND odm.deck_id <> d.id
INNER JOIN
deck AS od ON od.id = odm.deck_id
INNER JOIN
archetype AS oa ON od.archetype_id IN (SELECT descendant FROM archetype_closure WHERE ancestor = oa.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
GROUP BY
a.id,
oa.id,
season.season_id,
ct.name
```
[]
(slow_query, 67.2, mysql)
Reported on decksite by mysql-perf
Location Hash: 5c8a85b213df89d7a387ee22284c50e99ddb0591
Labels: decksite
Exceeded slow_query limit (71.5 > 60.0) in mysql: ```
CREATE TABLE IF NOT EXISTS _new_matchup_stats (
archetype_id INT NOT NULL,
opponent_archetype_id INT NOT NULL,
season_id INT NOT NULL,
wins INT NOT NULL,
losses INT NOT NULL,
draws INT NOT NULL,
deck_type ENUM('league', 'tournament', 'other') NOT NULL,
PRIMARY KEY (season_id, archetype_id, opponent_archetype_id, 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,
FOREIGN KEY (opponent_archetype_id) REFERENCES archetype (id) ON UPDATE CASCADE ON DELETE CASCADE
) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci AS
SELECT
a.id AS archetype_id,
oa.id AS opponent_archetype_id,
season.season_id,
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,
(CASE WHEN ct.name = 'League' THEN 'league' WHEN ct.name = 'Gatherling' THEN 'tournament' ELSE 'other' END) AS deck_type
FROM
archetype AS a
INNER JOIN
deck AS d ON d.archetype_id IN (SELECT descendant FROM archetype_closure WHERE ancestor = a.id)
INNER JOIN
deck_match AS dm ON d.id = dm.deck_id
INNER JOIN
deck_match AS odm ON dm.match_id = odm.match_id AND odm.deck_id <> d.id
INNER JOIN
deck AS od ON od.id = odm.deck_id
INNER JOIN
archetype AS oa ON od.archetype_id IN (SELECT descendant FROM archetype_closure WHERE ancestor = oa.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
GROUP BY
a.id,
oa.id,
season.season_id,
ct.name
```
[]
(slow_query, 71.5, mysql)
Reported on decksite by mysql-perf
Location Hash: 5c8a85b213df89d7a387ee22284c50e99ddb0591
Labels: decksite
Exceeded slow_query limit (71.3 > 60.0) in mysql: ```
CREATE TABLE IF NOT EXISTS _new_matchup_stats (
archetype_id INT NOT NULL,
opponent_archetype_id INT NOT NULL,
season_id INT NOT NULL,
wins INT NOT NULL,
losses INT NOT NULL,
draws INT NOT NULL,
deck_type ENUM('league', 'tournament', 'other') NOT NULL,
PRIMARY KEY (season_id, archetype_id, opponent_archetype_id, 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,
FOREIGN KEY (opponent_archetype_id) REFERENCES archetype (id) ON UPDATE CASCADE ON DELETE CASCADE
) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci AS
SELECT
a.id AS archetype_id,
oa.id AS opponent_archetype_id,
season.season_id,
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,
(CASE WHEN ct.name = 'League' THEN 'league' WHEN ct.name = 'Gatherling' THEN 'tournament' ELSE 'other' END) AS deck_type
FROM
archetype AS a
INNER JOIN
deck AS d ON d.archetype_id IN (SELECT descendant FROM archetype_closure WHERE ancestor = a.id)
INNER JOIN
deck_match AS dm ON d.id = dm.deck_id
INNER JOIN
deck_match AS odm ON dm.match_id = odm.match_id AND odm.deck_id <> d.id
INNER JOIN
deck AS od ON od.id = odm.deck_id
INNER JOIN
archetype AS oa ON od.archetype_id IN (SELECT descendant FROM archetype_closure WHERE ancestor = oa.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
GROUP BY
a.id,
oa.id,
season.season_id,
ct.name
```
[]
(slow_query, 71.3, mysql)
Reported on decksite by mysql-perf
Location Hash: 5c8a85b213df89d7a387ee22284c50e99ddb0591
Labels: decksite
Exceeded slow_query limit (67.8 > 60.0) in mysql: ```
CREATE TABLE IF NOT EXISTS _new_matchup_stats (
archetype_id INT NOT NULL,
opponent_archetype_id INT NOT NULL,
season_id INT NOT NULL,
wins INT NOT NULL,
losses INT NOT NULL,
draws INT NOT NULL,
deck_type ENUM('league', 'tournament', 'other') NOT NULL,
PRIMARY KEY (season_id, archetype_id, opponent_archetype_id, 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,
FOREIGN KEY (opponent_archetype_id) REFERENCES archetype (id) ON UPDATE CASCADE ON DELETE CASCADE
) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci AS
SELECT
a.id AS archetype_id,
oa.id AS opponent_archetype_id,
season.season_id,
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,
(CASE WHEN ct.name = 'League' THEN 'league' WHEN ct.name = 'Gatherling' THEN 'tournament' ELSE 'other' END) AS deck_type
FROM
archetype AS a
INNER JOIN
deck AS d ON d.archetype_id IN (SELECT descendant FROM archetype_closure WHERE ancestor = a.id)
INNER JOIN
deck_match AS dm ON d.id = dm.deck_id
INNER JOIN
deck_match AS odm ON dm.match_id = odm.match_id AND odm.deck_id <> d.id
INNER JOIN
deck AS od ON od.id = odm.deck_id
INNER JOIN
archetype AS oa ON od.archetype_id IN (SELECT descendant FROM archetype_closure WHERE ancestor = oa.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
GROUP BY
a.id,
oa.id,
season.season_id,
ct.name
```
[]
(slow_query, 67.8, mysql)
Reported on decksite by mysql-perf
Location Hash: 5c8a85b213df89d7a387ee22284c50e99ddb0591
Labels: decksite
Exceeded slow_query limit (68.7 > 60.0) in mysql: ```
CREATE TABLE IF NOT EXISTS _new_matchup_stats (
archetype_id INT NOT NULL,
opponent_archetype_id INT NOT NULL,
season_id INT NOT NULL,
wins INT NOT NULL,
losses INT NOT NULL,
draws INT NOT NULL,
deck_type ENUM('league', 'tournament', 'other') NOT NULL,
PRIMARY KEY (season_id, archetype_id, opponent_archetype_id, 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,
FOREIGN KEY (opponent_archetype_id) REFERENCES archetype (id) ON UPDATE CASCADE ON DELETE CASCADE
) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci AS
SELECT
a.id AS archetype_id,
oa.id AS opponent_archetype_id,
season.season_id,
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,
(CASE WHEN ct.name = 'League' THEN 'league' WHEN ct.name = 'Gatherling' THEN 'tournament' ELSE 'other' END) AS deck_type
FROM
archetype AS a
INNER JOIN
deck AS d ON d.archetype_id IN (SELECT descendant FROM archetype_closure WHERE ancestor = a.id)
INNER JOIN
deck_match AS dm ON d.id = dm.deck_id
INNER JOIN
deck_match AS odm ON dm.match_id = odm.match_id AND odm.deck_id <> d.id
INNER JOIN
deck AS od ON od.id = odm.deck_id
INNER JOIN
archetype AS oa ON od.archetype_id IN (SELECT descendant FROM archetype_closure WHERE ancestor = oa.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
GROUP BY
a.id,
oa.id,
season.season_id,
ct.name
```
[]
(slow_query, 68.7, mysql)
Reported on decksite by mysql-perf
Location Hash: 5c8a85b213df89d7a387ee22284c50e99ddb0591
Labels: decksite
Exceeded slow_query limit (72.6 > 60.0) in mysql: ```
CREATE TABLE IF NOT EXISTS _new_matchup_stats (
archetype_id INT NOT NULL,
opponent_archetype_id INT NOT NULL,
season_id INT NOT NULL,
wins INT NOT NULL,
losses INT NOT NULL,
draws INT NOT NULL,
deck_type ENUM('league', 'tournament', 'other') NOT NULL,
PRIMARY KEY (season_id, archetype_id, opponent_archetype_id, 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,
FOREIGN KEY (opponent_archetype_id) REFERENCES archetype (id) ON UPDATE CASCADE ON DELETE CASCADE
) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci AS
SELECT
a.id AS archetype_id,
oa.id AS opponent_archetype_id,
season.season_id,
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,
(CASE WHEN ct.name = 'League' THEN 'league' WHEN ct.name = 'Gatherling' THEN 'tournament' ELSE 'other' END) AS deck_type
FROM
archetype AS a
INNER JOIN
deck AS d ON d.archetype_id IN (SELECT descendant FROM archetype_closure WHERE ancestor = a.id)
INNER JOIN
deck_match AS dm ON d.id = dm.deck_id
INNER JOIN
deck_match AS odm ON dm.match_id = odm.match_id AND odm.deck_id <> d.id
INNER JOIN
deck AS od ON od.id = odm.deck_id
INNER JOIN
archetype AS oa ON od.archetype_id IN (SELECT descendant FROM archetype_closure WHERE ancestor = oa.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
GROUP BY
a.id,
oa.id,
season.season_id,
ct.name
```
[]
(slow_query, 72.6, mysql)
Reported on decksite by mysql-perf
Location Hash: 5c8a85b213df89d7a387ee22284c50e99ddb0591
Labels: decksite
Exceeded slow_query limit (66.0 > 60.0) in mysql: ```
CREATE TABLE IF NOT EXISTS _new_matchup_stats (
archetype_id INT NOT NULL,
opponent_archetype_id INT NOT NULL,
season_id INT NOT NULL,
wins INT NOT NULL,
losses INT NOT NULL,
draws INT NOT NULL,
deck_type ENUM('league', 'tournament', 'other') NOT NULL,
PRIMARY KEY (season_id, archetype_id, opponent_archetype_id, 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,
FOREIGN KEY (opponent_archetype_id) REFERENCES archetype (id) ON UPDATE CASCADE ON DELETE CASCADE
) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci AS
SELECT
a.id AS archetype_id,
oa.id AS opponent_archetype_id,
season.season_id,
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,
(CASE WHEN ct.name = 'League' THEN 'league' WHEN ct.name = 'Gatherling' THEN 'tournament' ELSE 'other' END) AS deck_type
FROM
archetype AS a
INNER JOIN
deck AS d ON d.archetype_id IN (SELECT descendant FROM archetype_closure WHERE ancestor = a.id)
INNER JOIN
deck_match AS dm ON d.id = dm.deck_id
INNER JOIN
deck_match AS odm ON dm.match_id = odm.match_id AND odm.deck_id <> d.id
INNER JOIN
deck AS od ON od.id = odm.deck_id
INNER JOIN
archetype AS oa ON od.archetype_id IN (SELECT descendant FROM archetype_closure WHERE ancestor = oa.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
GROUP BY
a.id,
oa.id,
season.season_id,
ct.name
```
[]
(slow_query, 66.0, mysql)
Reported on decksite by mysql-perf
Location Hash: 5c8a85b213df89d7a387ee22284c50e99ddb0591
Labels: decksite
Exceeded slow_query limit (68.1 > 60.0) in mysql: ```
CREATE TABLE IF NOT EXISTS _new_matchup_stats (
archetype_id INT NOT NULL,
opponent_archetype_id INT NOT NULL,
season_id INT NOT NULL,
wins INT NOT NULL,
losses INT NOT NULL,
draws INT NOT NULL,
deck_type ENUM('league', 'tournament', 'other') NOT NULL,
PRIMARY KEY (season_id, archetype_id, opponent_archetype_id, 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,
FOREIGN KEY (opponent_archetype_id) REFERENCES archetype (id) ON UPDATE CASCADE ON DELETE CASCADE
) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci AS
SELECT
a.id AS archetype_id,
oa.id AS opponent_archetype_id,
season.season_id,
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,
(CASE WHEN ct.name = 'League' THEN 'league' WHEN ct.name = 'Gatherling' THEN 'tournament' ELSE 'other' END) AS deck_type
FROM
archetype AS a
INNER JOIN
deck AS d ON d.archetype_id IN (SELECT descendant FROM archetype_closure WHERE ancestor = a.id)
INNER JOIN
deck_match AS dm ON d.id = dm.deck_id
INNER JOIN
deck_match AS odm ON dm.match_id = odm.match_id AND odm.deck_id <> d.id
INNER JOIN
deck AS od ON od.id = odm.deck_id
INNER JOIN
archetype AS oa ON od.archetype_id IN (SELECT descendant FROM archetype_closure WHERE ancestor = oa.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
GROUP BY
a.id,
oa.id,
season.season_id,
ct.name
```
[]
(slow_query, 68.1, mysql)
Reported on decksite by mysql-perf
Location Hash: 5c8a85b213df89d7a387ee22284c50e99ddb0591
Labels: decksite
Exceeded slow_query limit (67.5 > 60.0) in mysql: ```
CREATE TABLE IF NOT EXISTS _new_matchup_stats (
archetype_id INT NOT NULL,
opponent_archetype_id INT NOT NULL,
season_id INT NOT NULL,
wins INT NOT NULL,
losses INT NOT NULL,
draws INT NOT NULL,
deck_type ENUM('league', 'tournament', 'other') NOT NULL,
PRIMARY KEY (season_id, archetype_id, opponent_archetype_id, 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,
FOREIGN KEY (opponent_archetype_id) REFERENCES archetype (id) ON UPDATE CASCADE ON DELETE CASCADE
) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci AS
SELECT
a.id AS archetype_id,
oa.id AS opponent_archetype_id,
season.season_id,
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,
(CASE WHEN ct.name = 'League' THEN 'league' WHEN ct.name = 'Gatherling' THEN 'tournament' ELSE 'other' END) AS deck_type
FROM
archetype AS a
INNER JOIN
deck AS d ON d.archetype_id IN (SELECT descendant FROM archetype_closure WHERE ancestor = a.id)
INNER JOIN
deck_match AS dm ON d.id = dm.deck_id
INNER JOIN
deck_match AS odm ON dm.match_id = odm.match_id AND odm.deck_id <> d.id
INNER JOIN
deck AS od ON od.id = odm.deck_id
INNER JOIN
archetype AS oa ON od.archetype_id IN (SELECT descendant FROM archetype_closure WHERE ancestor = oa.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
GROUP BY
a.id,
oa.id,
season.season_id,
ct.name
```
[]
(slow_query, 67.5, mysql)
Reported on decksite by mysql-perf
Location Hash: 5c8a85b213df89d7a387ee22284c50e99ddb0591
Labels: decksite
Exceeded slow_query limit (67.0 > 60.0) in mysql: ```
CREATE TABLE IF NOT EXISTS _new_matchup_stats (
archetype_id INT NOT NULL,
opponent_archetype_id INT NOT NULL,
season_id INT NOT NULL,
wins INT NOT NULL,
losses INT NOT NULL,
draws INT NOT NULL,
deck_type ENUM('league', 'tournament', 'other') NOT NULL,
PRIMARY KEY (season_id, archetype_id, opponent_archetype_id, 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,
FOREIGN KEY (opponent_archetype_id) REFERENCES archetype (id) ON UPDATE CASCADE ON DELETE CASCADE
) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci AS
SELECT
a.id AS archetype_id,
oa.id AS opponent_archetype_id,
season.season_id,
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,
(CASE WHEN ct.name = 'League' THEN 'league' WHEN ct.name = 'Gatherling' THEN 'tournament' ELSE 'other' END) AS deck_type
FROM
archetype AS a
INNER JOIN
deck AS d ON d.archetype_id IN (SELECT descendant FROM archetype_closure WHERE ancestor = a.id)
INNER JOIN
deck_match AS dm ON d.id = dm.deck_id
INNER JOIN
deck_match AS odm ON dm.match_id = odm.match_id AND odm.deck_id <> d.id
INNER JOIN
deck AS od ON od.id = odm.deck_id
INNER JOIN
archetype AS oa ON od.archetype_id IN (SELECT descendant FROM archetype_closure WHERE ancestor = oa.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
GROUP BY
a.id,
oa.id,
season.season_id,
ct.name
```
[]
(slow_query, 67.0, mysql)
Reported on decksite by mysql-perf
Location Hash: 5c8a85b213df89d7a387ee22284c50e99ddb0591
Labels: decksite
Exceeded slow_query limit (70.3 > 60.0) in mysql: ```
CREATE TABLE IF NOT EXISTS _new_matchup_stats (
archetype_id INT NOT NULL,
opponent_archetype_id INT NOT NULL,
season_id INT NOT NULL,
wins INT NOT NULL,
losses INT NOT NULL,
draws INT NOT NULL,
deck_type ENUM('league', 'tournament', 'other') NOT NULL,
PRIMARY KEY (season_id, archetype_id, opponent_archetype_id, 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,
FOREIGN KEY (opponent_archetype_id) REFERENCES archetype (id) ON UPDATE CASCADE ON DELETE CASCADE
) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci AS
SELECT
a.id AS archetype_id,
oa.id AS opponent_archetype_id,
season.season_id,
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,
(CASE WHEN ct.name = 'League' THEN 'league' WHEN ct.name = 'Gatherling' THEN 'tournament' ELSE 'other' END) AS deck_type
FROM
archetype AS a
INNER JOIN
deck AS d ON d.archetype_id IN (SELECT descendant FROM archetype_closure WHERE ancestor = a.id)
INNER JOIN
deck_match AS dm ON d.id = dm.deck_id
INNER JOIN
deck_match AS odm ON dm.match_id = odm.match_id AND odm.deck_id <> d.id
INNER JOIN
deck AS od ON od.id = odm.deck_id
INNER JOIN
archetype AS oa ON od.archetype_id IN (SELECT descendant FROM archetype_closure WHERE ancestor = oa.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
GROUP BY
a.id,
oa.id,
season.season_id,
ct.name
```
[]
(slow_query, 70.3, mysql)
Reported on decksite by mysql-perf
Location Hash: 5c8a85b213df89d7a387ee22284c50e99ddb0591
Labels: decksite
Exceeded slow_query limit (72.0 > 60.0) in mysql: ```
CREATE TABLE IF NOT EXISTS _new_matchup_stats (
archetype_id INT NOT NULL,
opponent_archetype_id INT NOT NULL,
season_id INT NOT NULL,
wins INT NOT NULL,
losses INT NOT NULL,
draws INT NOT NULL,
deck_type ENUM('league', 'tournament', 'other') NOT NULL,
PRIMARY KEY (season_id, archetype_id, opponent_archetype_id, 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,
FOREIGN KEY (opponent_archetype_id) REFERENCES archetype (id) ON UPDATE CASCADE ON DELETE CASCADE
) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci AS
SELECT
a.id AS archetype_id,
oa.id AS opponent_archetype_id,
season.season_id,
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,
(CASE WHEN ct.name = 'League' THEN 'league' WHEN ct.name = 'Gatherling' THEN 'tournament' ELSE 'other' END) AS deck_type
FROM
archetype AS a
INNER JOIN
deck AS d ON d.archetype_id IN (SELECT descendant FROM archetype_closure WHERE ancestor = a.id)
INNER JOIN
deck_match AS dm ON d.id = dm.deck_id
INNER JOIN
deck_match AS odm ON dm.match_id = odm.match_id AND odm.deck_id <> d.id
INNER JOIN
deck AS od ON od.id = odm.deck_id
INNER JOIN
archetype AS oa ON od.archetype_id IN (SELECT descendant FROM archetype_closure WHERE ancestor = oa.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
GROUP BY
a.id,
oa.id,
season.season_id,
ct.name
```
[]
(slow_query, 72.0, mysql)
Reported on decksite by mysql-perf
Location Hash: 5c8a85b213df89d7a387ee22284c50e99ddb0591
Labels: decksite
Exceeded slow_query limit (66.3 > 60.0) in mysql: ```
CREATE TABLE IF NOT EXISTS _new_matchup_stats (
archetype_id INT NOT NULL,
opponent_archetype_id INT NOT NULL,
season_id INT NOT NULL,
wins INT NOT NULL,
losses INT NOT NULL,
draws INT NOT NULL,
deck_type ENUM('league', 'tournament', 'other') NOT NULL,
PRIMARY KEY (season_id, archetype_id, opponent_archetype_id, 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,
FOREIGN KEY (opponent_archetype_id) REFERENCES archetype (id) ON UPDATE CASCADE ON DELETE CASCADE
) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci AS
SELECT
a.id AS archetype_id,
oa.id AS opponent_archetype_id,
season.season_id,
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,
(CASE WHEN ct.name = 'League' THEN 'league' WHEN ct.name = 'Gatherling' THEN 'tournament' ELSE 'other' END) AS deck_type
FROM
archetype AS a
INNER JOIN
deck AS d ON d.archetype_id IN (SELECT descendant FROM archetype_closure WHERE ancestor = a.id)
INNER JOIN
deck_match AS dm ON d.id = dm.deck_id
INNER JOIN
deck_match AS odm ON dm.match_id = odm.match_id AND odm.deck_id <> d.id
INNER JOIN
deck AS od ON od.id = odm.deck_id
INNER JOIN
archetype AS oa ON od.archetype_id IN (SELECT descendant FROM archetype_closure WHERE ancestor = oa.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
GROUP BY
a.id,
oa.id,
season.season_id,
ct.name
```
[]
(slow_query, 66.3, mysql)
Reported on decksite by mysql-perf
Location Hash: 5c8a85b213df89d7a387ee22284c50e99ddb0591
Labels: decksite
Exceeded slow_query limit (72.7 > 60.0) in mysql: ```
CREATE TABLE IF NOT EXISTS _new_matchup_stats (
archetype_id INT NOT NULL,
opponent_archetype_id INT NOT NULL,
season_id INT NOT NULL,
wins INT NOT NULL,
losses INT NOT NULL,
draws INT NOT NULL,
deck_type ENUM('league', 'tournament', 'other') NOT NULL,
PRIMARY KEY (season_id, archetype_id, opponent_archetype_id, 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,
FOREIGN KEY (opponent_archetype_id) REFERENCES archetype (id) ON UPDATE CASCADE ON DELETE CASCADE
) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci AS
SELECT
a.id AS archetype_id,
oa.id AS opponent_archetype_id,
season.season_id,
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,
(CASE WHEN ct.name = 'League' THEN 'league' WHEN ct.name = 'Gatherling' THEN 'tournament' ELSE 'other' END) AS deck_type
FROM
archetype AS a
INNER JOIN
deck AS d ON d.archetype_id IN (SELECT descendant FROM archetype_closure WHERE ancestor = a.id)
INNER JOIN
deck_match AS dm ON d.id = dm.deck_id
INNER JOIN
deck_match AS odm ON dm.match_id = odm.match_id AND odm.deck_id <> d.id
INNER JOIN
deck AS od ON od.id = odm.deck_id
INNER JOIN
archetype AS oa ON od.archetype_id IN (SELECT descendant FROM archetype_closure WHERE ancestor = oa.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
GROUP BY
a.id,
oa.id,
season.season_id,
ct.name
```
[]
(slow_query, 72.7, mysql)
Reported on decksite by mysql-perf
Location Hash: 5c8a85b213df89d7a387ee22284c50e99ddb0591
Labels: decksite
Exceeded slow_query limit (71.4 > 60.0) in mysql: ```
CREATE TABLE IF NOT EXISTS _new_matchup_stats (
archetype_id INT NOT NULL,
opponent_archetype_id INT NOT NULL,
season_id INT NOT NULL,
wins INT NOT NULL,
losses INT NOT NULL,
draws INT NOT NULL,
deck_type ENUM('league', 'tournament', 'other') NOT NULL,
PRIMARY KEY (season_id, archetype_id, opponent_archetype_id, 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,
FOREIGN KEY (opponent_archetype_id) REFERENCES archetype (id) ON UPDATE CASCADE ON DELETE CASCADE
) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci AS
SELECT
a.id AS archetype_id,
oa.id AS opponent_archetype_id,
season.season_id,
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,
(CASE WHEN ct.name = 'League' THEN 'league' WHEN ct.name = 'Gatherling' THEN 'tournament' ELSE 'other' END) AS deck_type
FROM
archetype AS a
INNER JOIN
deck AS d ON d.archetype_id IN (SELECT descendant FROM archetype_closure WHERE ancestor = a.id)
INNER JOIN
deck_match AS dm ON d.id = dm.deck_id
INNER JOIN
deck_match AS odm ON dm.match_id = odm.match_id AND odm.deck_id <> d.id
INNER JOIN
deck AS od ON od.id = odm.deck_id
INNER JOIN
archetype AS oa ON od.archetype_id IN (SELECT descendant FROM archetype_closure WHERE ancestor = oa.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
GROUP BY
a.id,
oa.id,
season.season_id,
ct.name
```
[]
(slow_query, 71.4, mysql)
Reported on decksite by mysql-perf
Location Hash: 5c8a85b213df89d7a387ee22284c50e99ddb0591
Labels: decksite
Exceeded slow_query limit (71.0 > 60.0) in mysql: ```
CREATE TABLE IF NOT EXISTS _new_matchup_stats (
archetype_id INT NOT NULL,
opponent_archetype_id INT NOT NULL,
season_id INT NOT NULL,
wins INT NOT NULL,
losses INT NOT NULL,
draws INT NOT NULL,
deck_type ENUM('league', 'tournament', 'other') NOT NULL,
PRIMARY KEY (season_id, archetype_id, opponent_archetype_id, 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,
FOREIGN KEY (opponent_archetype_id) REFERENCES archetype (id) ON UPDATE CASCADE ON DELETE CASCADE
) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci AS
SELECT
a.id AS archetype_id,
oa.id AS opponent_archetype_id,
season.season_id,
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,
(CASE WHEN ct.name = 'League' THEN 'league' WHEN ct.name = 'Gatherling' THEN 'tournament' ELSE 'other' END) AS deck_type
FROM
archetype AS a
INNER JOIN
deck AS d ON d.archetype_id IN (SELECT descendant FROM archetype_closure WHERE ancestor = a.id)
INNER JOIN
deck_match AS dm ON d.id = dm.deck_id
INNER JOIN
deck_match AS odm ON dm.match_id = odm.match_id AND odm.deck_id <> d.id
INNER JOIN
deck AS od ON od.id = odm.deck_id
INNER JOIN
archetype AS oa ON od.archetype_id IN (SELECT descendant FROM archetype_closure WHERE ancestor = oa.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
GROUP BY
a.id,
oa.id,
season.season_id,
ct.name
```
[]
(slow_query, 71.0, mysql)
Reported on decksite by mysql-perf
Location Hash: 5c8a85b213df89d7a387ee22284c50e99ddb0591
Labels: decksite
Exceeded slow_query limit (74.2 > 60.0) in mysql: ```
CREATE TABLE IF NOT EXISTS _new_matchup_stats (
archetype_id INT NOT NULL,
opponent_archetype_id INT NOT NULL,
season_id INT NOT NULL,
wins INT NOT NULL,
losses INT NOT NULL,
draws INT NOT NULL,
deck_type ENUM('league', 'tournament', 'other') NOT NULL,
PRIMARY KEY (season_id, archetype_id, opponent_archetype_id, 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,
FOREIGN KEY (opponent_archetype_id) REFERENCES archetype (id) ON UPDATE CASCADE ON DELETE CASCADE
) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci AS
SELECT
a.id AS archetype_id,
oa.id AS opponent_archetype_id,
season.season_id,
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,
(CASE WHEN ct.name = 'League' THEN 'league' WHEN ct.name = 'Gatherling' THEN 'tournament' ELSE 'other' END) AS deck_type
FROM
archetype AS a
INNER JOIN
deck AS d ON d.archetype_id IN (SELECT descendant FROM archetype_closure WHERE ancestor = a.id)
INNER JOIN
deck_match AS dm ON d.id = dm.deck_id
INNER JOIN
deck_match AS odm ON dm.match_id = odm.match_id AND odm.deck_id <> d.id
INNER JOIN
deck AS od ON od.id = odm.deck_id
INNER JOIN
archetype AS oa ON od.archetype_id IN (SELECT descendant FROM archetype_closure WHERE ancestor = oa.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
GROUP BY
a.id,
oa.id,
season.season_id,
ct.name
```
[]
(slow_query, 74.2, mysql)
Reported on decksite by mysql-perf
Location Hash: 5c8a85b213df89d7a387ee22284c50e99ddb0591
Labels: decksite
Exceeded slow_query limit (75.7 > 60.0) in mysql: ```
CREATE TABLE IF NOT EXISTS _new_matchup_stats (
archetype_id INT NOT NULL,
opponent_archetype_id INT NOT NULL,
season_id INT NOT NULL,
wins INT NOT NULL,
losses INT NOT NULL,
draws INT NOT NULL,
deck_type ENUM('league', 'tournament', 'other') NOT NULL,
PRIMARY KEY (season_id, archetype_id, opponent_archetype_id, 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,
FOREIGN KEY (opponent_archetype_id) REFERENCES archetype (id) ON UPDATE CASCADE ON DELETE CASCADE
) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci AS
SELECT
a.id AS archetype_id,
oa.id AS opponent_archetype_id,
season.season_id,
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,
(CASE WHEN ct.name = 'League' THEN 'league' WHEN ct.name = 'Gatherling' THEN 'tournament' ELSE 'other' END) AS deck_type
FROM
archetype AS a
INNER JOIN
deck AS d ON d.archetype_id IN (SELECT descendant FROM archetype_closure WHERE ancestor = a.id)
INNER JOIN
deck_match AS dm ON d.id = dm.deck_id
INNER JOIN
deck_match AS odm ON dm.match_id = odm.match_id AND odm.deck_id <> d.id
INNER JOIN
deck AS od ON od.id = odm.deck_id
INNER JOIN
archetype AS oa ON od.archetype_id IN (SELECT descendant FROM archetype_closure WHERE ancestor = oa.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
GROUP BY
a.id,
oa.id,
season.season_id,
ct.name
```
[]
(slow_query, 75.7, mysql)
Reported on decksite by mysql-perf
Location Hash: 5c8a85b213df89d7a387ee22284c50e99ddb0591
Labels: decksite
Exceeded slow_query limit (68.1 > 60.0) in mysql: ```
CREATE TABLE IF NOT EXISTS _new_matchup_stats (
archetype_id INT NOT NULL,
opponent_archetype_id INT NOT NULL,
season_id INT NOT NULL,
wins INT NOT NULL,
losses INT NOT NULL,
draws INT NOT NULL,
deck_type ENUM('league', 'tournament', 'other') NOT NULL,
PRIMARY KEY (season_id, archetype_id, opponent_archetype_id, 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,
FOREIGN KEY (opponent_archetype_id) REFERENCES archetype (id) ON UPDATE CASCADE ON DELETE CASCADE
) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci AS
SELECT
a.id AS archetype_id,
oa.id AS opponent_archetype_id,
season.season_id,
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,
(CASE WHEN ct.name = 'League' THEN 'league' WHEN ct.name = 'Gatherling' THEN 'tournament' ELSE 'other' END) AS deck_type
FROM
archetype AS a
INNER JOIN
deck AS d ON d.archetype_id IN (SELECT descendant FROM archetype_closure WHERE ancestor = a.id)
INNER JOIN
deck_match AS dm ON d.id = dm.deck_id
INNER JOIN
deck_match AS odm ON dm.match_id = odm.match_id AND odm.deck_id <> d.id
INNER JOIN
deck AS od ON od.id = odm.deck_id
INNER JOIN
archetype AS oa ON od.archetype_id IN (SELECT descendant FROM archetype_closure WHERE ancestor = oa.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
GROUP BY
a.id,
oa.id,
season.season_id,
ct.name
```
[]
(slow_query, 68.1, mysql)
Reported on decksite by mysql-perf
Location Hash: 5c8a85b213df89d7a387ee22284c50e99ddb0591
Labels: decksite
Exceeded slow_query limit (68.9 > 60.0) in mysql: ```
CREATE TABLE IF NOT EXISTS _new_matchup_stats (
archetype_id INT NOT NULL,
opponent_archetype_id INT NOT NULL,
season_id INT NOT NULL,
wins INT NOT NULL,
losses INT NOT NULL,
draws INT NOT NULL,
deck_type ENUM('league', 'tournament', 'other') NOT NULL,
PRIMARY KEY (season_id, archetype_id, opponent_archetype_id, 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,
FOREIGN KEY (opponent_archetype_id) REFERENCES archetype (id) ON UPDATE CASCADE ON DELETE CASCADE
) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci AS
SELECT
a.id AS archetype_id,
oa.id AS opponent_archetype_id,
season.season_id,
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,
(CASE WHEN ct.name = 'League' THEN 'league' WHEN ct.name = 'Gatherling' THEN 'tournament' ELSE 'other' END) AS deck_type
FROM
archetype AS a
INNER JOIN
deck AS d ON d.archetype_id IN (SELECT descendant FROM archetype_closure WHERE ancestor = a.id)
INNER JOIN
deck_match AS dm ON d.id = dm.deck_id
INNER JOIN
deck_match AS odm ON dm.match_id = odm.match_id AND odm.deck_id <> d.id
INNER JOIN
deck AS od ON od.id = odm.deck_id
INNER JOIN
archetype AS oa ON od.archetype_id IN (SELECT descendant FROM archetype_closure WHERE ancestor = oa.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
GROUP BY
a.id,
oa.id,
season.season_id,
ct.name
```
[]
(slow_query, 68.9, mysql)
Reported on decksite by mysql-perf
Location Hash: 5c8a85b213df89d7a387ee22284c50e99ddb0591
Labels: decksite
Exceeded slow_query limit (72.0 > 60.0) in mysql: ```
CREATE TABLE IF NOT EXISTS _new_matchup_stats (
archetype_id INT NOT NULL,
opponent_archetype_id INT NOT NULL,
season_id INT NOT NULL,
wins INT NOT NULL,
losses INT NOT NULL,
draws INT NOT NULL,
deck_type ENUM('league', 'tournament', 'other') NOT NULL,
PRIMARY KEY (season_id, archetype_id, opponent_archetype_id, 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,
FOREIGN KEY (opponent_archetype_id) REFERENCES archetype (id) ON UPDATE CASCADE ON DELETE CASCADE
) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci AS
SELECT
a.id AS archetype_id,
oa.id AS opponent_archetype_id,
season.season_id,
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,
(CASE WHEN ct.name = 'League' THEN 'league' WHEN ct.name = 'Gatherling' THEN 'tournament' ELSE 'other' END) AS deck_type
FROM
archetype AS a
INNER JOIN
deck AS d ON d.archetype_id IN (SELECT descendant FROM archetype_closure WHERE ancestor = a.id)
INNER JOIN
deck_match AS dm ON d.id = dm.deck_id
INNER JOIN
deck_match AS odm ON dm.match_id = odm.match_id AND odm.deck_id <> d.id
INNER JOIN
deck AS od ON od.id = odm.deck_id
INNER JOIN
archetype AS oa ON od.archetype_id IN (SELECT descendant FROM archetype_closure WHERE ancestor = oa.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
GROUP BY
a.id,
oa.id,
season.season_id,
ct.name
```
[]
(slow_query, 72.0, mysql)
Reported on decksite by mysql-perf
Location Hash: 5c8a85b213df89d7a387ee22284c50e99ddb0591
Labels: decksite
Exceeded slow_query limit (68.3 > 60.0) in mysql: ```
CREATE TABLE IF NOT EXISTS _new_matchup_stats (
archetype_id INT NOT NULL,
opponent_archetype_id INT NOT NULL,
season_id INT NOT NULL,
wins INT NOT NULL,
losses INT NOT NULL,
draws INT NOT NULL,
deck_type ENUM('league', 'tournament', 'other') NOT NULL,
PRIMARY KEY (season_id, archetype_id, opponent_archetype_id, 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,
FOREIGN KEY (opponent_archetype_id) REFERENCES archetype (id) ON UPDATE CASCADE ON DELETE CASCADE
) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci AS
SELECT
a.id AS archetype_id,
oa.id AS opponent_archetype_id,
season.season_id,
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,
(CASE WHEN ct.name = 'League' THEN 'league' WHEN ct.name = 'Gatherling' THEN 'tournament' ELSE 'other' END) AS deck_type
FROM
archetype AS a
INNER JOIN
deck AS d ON d.archetype_id IN (SELECT descendant FROM archetype_closure WHERE ancestor = a.id)
INNER JOIN
deck_match AS dm ON d.id = dm.deck_id
INNER JOIN
deck_match AS odm ON dm.match_id = odm.match_id AND odm.deck_id <> d.id
INNER JOIN
deck AS od ON od.id = odm.deck_id
INNER JOIN
archetype AS oa ON od.archetype_id IN (SELECT descendant FROM archetype_closure WHERE ancestor = oa.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
GROUP BY
a.id,
oa.id,
season.season_id,
ct.name
```
[]
(slow_query, 68.3, mysql)
Reported on decksite by mysql-perf
Location Hash: 5c8a85b213df89d7a387ee22284c50e99ddb0591
Labels: decksite
Exceeded slow_query limit (75.2 > 60.0) in mysql: ```
CREATE TABLE IF NOT EXISTS _new_matchup_stats (
archetype_id INT NOT NULL,
opponent_archetype_id INT NOT NULL,
season_id INT NOT NULL,
wins INT NOT NULL,
losses INT NOT NULL,
draws INT NOT NULL,
deck_type ENUM('league', 'tournament', 'other') NOT NULL,
PRIMARY KEY (season_id, archetype_id, opponent_archetype_id, 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,
FOREIGN KEY (opponent_archetype_id) REFERENCES archetype (id) ON UPDATE CASCADE ON DELETE CASCADE
) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci AS
SELECT
a.id AS archetype_id,
oa.id AS opponent_archetype_id,
season.season_id,
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,
(CASE WHEN ct.name = 'League' THEN 'league' WHEN ct.name = 'Gatherling' THEN 'tournament' ELSE 'other' END) AS deck_type
FROM
archetype AS a
INNER JOIN
deck AS d ON d.archetype_id IN (SELECT descendant FROM archetype_closure WHERE ancestor = a.id)
INNER JOIN
deck_match AS dm ON d.id = dm.deck_id
INNER JOIN
deck_match AS odm ON dm.match_id = odm.match_id AND odm.deck_id <> d.id
INNER JOIN
deck AS od ON od.id = odm.deck_id
INNER JOIN
archetype AS oa ON od.archetype_id IN (SELECT descendant FROM archetype_closure WHERE ancestor = oa.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
GROUP BY
a.id,
oa.id,
season.season_id,
ct.name
```
[]
(slow_query, 75.2, mysql)
Reported on decksite by mysql-perf
Location Hash: 5c8a85b213df89d7a387ee22284c50e99ddb0591
Labels: decksite
Exceeded slow_query limit (77.7 > 60.0) in mysql: ```
CREATE TABLE IF NOT EXISTS _new_matchup_stats (
archetype_id INT NOT NULL,
opponent_archetype_id INT NOT NULL,
season_id INT NOT NULL,
wins INT NOT NULL,
losses INT NOT NULL,
draws INT NOT NULL,
deck_type ENUM('league', 'tournament', 'other') NOT NULL,
PRIMARY KEY (season_id, archetype_id, opponent_archetype_id, 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,
FOREIGN KEY (opponent_archetype_id) REFERENCES archetype (id) ON UPDATE CASCADE ON DELETE CASCADE
) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci AS
SELECT
a.id AS archetype_id,
oa.id AS opponent_archetype_id,
season.season_id,
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,
(CASE WHEN ct.name = 'League' THEN 'league' WHEN ct.name = 'Gatherling' THEN 'tournament' ELSE 'other' END) AS deck_type
FROM
archetype AS a
INNER JOIN
deck AS d ON d.archetype_id IN (SELECT descendant FROM archetype_closure WHERE ancestor = a.id)
INNER JOIN
deck_match AS dm ON d.id = dm.deck_id
INNER JOIN
deck_match AS odm ON dm.match_id = odm.match_id AND odm.deck_id <> d.id
INNER JOIN
deck AS od ON od.id = odm.deck_id
INNER JOIN
archetype AS oa ON od.archetype_id IN (SELECT descendant FROM archetype_closure WHERE ancestor = oa.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
GROUP BY
a.id,
oa.id,
season.season_id,
ct.name
```
[]
(slow_query, 77.7, mysql)
Reported on decksite by mysql-perf
Location Hash: 5c8a85b213df89d7a387ee22284c50e99ddb0591
Labels: decksite
Exceeded slow_query limit (87.0 > 60.0) in mysql: ```
CREATE TABLE IF NOT EXISTS _new_matchup_stats (
archetype_id INT NOT NULL,
opponent_archetype_id INT NOT NULL,
season_id INT NOT NULL,
wins INT NOT NULL,
losses INT NOT NULL,
draws INT NOT NULL,
deck_type ENUM('league', 'tournament', 'other') NOT NULL,
PRIMARY KEY (season_id, archetype_id, opponent_archetype_id, 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,
FOREIGN KEY (opponent_archetype_id) REFERENCES archetype (id) ON UPDATE CASCADE ON DELETE CASCADE
) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci AS
SELECT
a.id AS archetype_id,
oa.id AS opponent_archetype_id,
season.season_id,
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,
(CASE WHEN ct.name = 'League' THEN 'league' WHEN ct.name = 'Gatherling' THEN 'tournament' ELSE 'other' END) AS deck_type
FROM
archetype AS a
INNER JOIN
deck AS d ON d.archetype_id IN (SELECT descendant FROM archetype_closure WHERE ancestor = a.id)
INNER JOIN
deck_match AS dm ON d.id = dm.deck_id
INNER JOIN
deck_match AS odm ON dm.match_id = odm.match_id AND odm.deck_id <> d.id
INNER JOIN
deck AS od ON od.id = odm.deck_id
INNER JOIN
archetype AS oa ON od.archetype_id IN (SELECT descendant FROM archetype_closure WHERE ancestor = oa.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
GROUP BY
a.id,
oa.id,
season.season_id,
ct.name
```
[]
(slow_query, 87.0, mysql)
Reported on decksite by mysql-perf
Location Hash: 5c8a85b213df89d7a387ee22284c50e99ddb0591
Labels: decksite
Exceeded slow_query limit (87.1 > 60.0) in mysql: ```
CREATE TABLE IF NOT EXISTS _new_matchup_stats (
archetype_id INT NOT NULL,
opponent_archetype_id INT NOT NULL,
season_id INT NOT NULL,
wins INT NOT NULL,
losses INT NOT NULL,
draws INT NOT NULL,
deck_type ENUM('league', 'tournament', 'other') NOT NULL,
PRIMARY KEY (season_id, archetype_id, opponent_archetype_id, 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,
FOREIGN KEY (opponent_archetype_id) REFERENCES archetype (id) ON UPDATE CASCADE ON DELETE CASCADE
) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci AS
SELECT
a.id AS archetype_id,
oa.id AS opponent_archetype_id,
season.season_id,
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,
(CASE WHEN ct.name = 'League' THEN 'league' WHEN ct.name = 'Gatherling' THEN 'tournament' ELSE 'other' END) AS deck_type
FROM
archetype AS a
INNER JOIN
deck AS d ON d.archetype_id IN (SELECT descendant FROM archetype_closure WHERE ancestor = a.id)
INNER JOIN
deck_match AS dm ON d.id = dm.deck_id
INNER JOIN
deck_match AS odm ON dm.match_id = odm.match_id AND odm.deck_id <> d.id
INNER JOIN
deck AS od ON od.id = odm.deck_id
INNER JOIN
archetype AS oa ON od.archetype_id IN (SELECT descendant FROM archetype_closure WHERE ancestor = oa.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
GROUP BY
a.id,
oa.id,
season.season_id,
ct.name
```
[]
(slow_query, 87.1, mysql)
Reported on decksite by mysql-perf
Location Hash: 5c8a85b213df89d7a387ee22284c50e99ddb0591
Labels: decksite
Exceeded slow_query limit (82.4 > 60.0) in mysql: ```
CREATE TABLE IF NOT EXISTS _new_matchup_stats (
archetype_id INT NOT NULL,
opponent_archetype_id INT NOT NULL,
season_id INT NOT NULL,
wins INT NOT NULL,
losses INT NOT NULL,
draws INT NOT NULL,
deck_type ENUM('league', 'tournament', 'other') NOT NULL,
PRIMARY KEY (season_id, archetype_id, opponent_archetype_id, 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,
FOREIGN KEY (opponent_archetype_id) REFERENCES archetype (id) ON UPDATE CASCADE ON DELETE CASCADE
) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci AS
SELECT
a.id AS archetype_id,
oa.id AS opponent_archetype_id,
season.season_id,
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,
(CASE WHEN ct.name = 'League' THEN 'league' WHEN ct.name = 'Gatherling' THEN 'tournament' ELSE 'other' END) AS deck_type
FROM
archetype AS a
INNER JOIN
deck AS d ON d.archetype_id IN (SELECT descendant FROM archetype_closure WHERE ancestor = a.id)
INNER JOIN
deck_match AS dm ON d.id = dm.deck_id
INNER JOIN
deck_match AS odm ON dm.match_id = odm.match_id AND odm.deck_id <> d.id
INNER JOIN
deck AS od ON od.id = odm.deck_id
INNER JOIN
archetype AS oa ON od.archetype_id IN (SELECT descendant FROM archetype_closure WHERE ancestor = oa.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
GROUP BY
a.id,
oa.id,
season.season_id,
ct.name
```
[]
(slow_query, 82.4, mysql)
Reported on decksite by mysql-perf
Location Hash: 5c8a85b213df89d7a387ee22284c50e99ddb0591
Labels: decksite
Exceeded slow_query limit (91.7 > 60.0) in mysql: ```
CREATE TABLE IF NOT EXISTS _new_matchup_stats (
archetype_id INT NOT NULL,
opponent_archetype_id INT NOT NULL,
season_id INT NOT NULL,
wins INT NOT NULL,
losses INT NOT NULL,
draws INT NOT NULL,
deck_type ENUM('league', 'tournament', 'other') NOT NULL,
PRIMARY KEY (season_id, archetype_id, opponent_archetype_id, 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,
FOREIGN KEY (opponent_archetype_id) REFERENCES archetype (id) ON UPDATE CASCADE ON DELETE CASCADE
) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci AS
SELECT
a.id AS archetype_id,
oa.id AS opponent_archetype_id,
season.season_id,
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,
(CASE WHEN ct.name = 'League' THEN 'league' WHEN ct.name = 'Gatherling' THEN 'tournament' ELSE 'other' END) AS deck_type
FROM
archetype AS a
INNER JOIN
deck AS d ON d.archetype_id IN (SELECT descendant FROM archetype_closure WHERE ancestor = a.id)
INNER JOIN
deck_match AS dm ON d.id = dm.deck_id
INNER JOIN
deck_match AS odm ON dm.match_id = odm.match_id AND odm.deck_id <> d.id
INNER JOIN
deck AS od ON od.id = odm.deck_id
INNER JOIN
archetype AS oa ON od.archetype_id IN (SELECT descendant FROM archetype_closure WHERE ancestor = oa.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
GROUP BY
a.id,
oa.id,
season.season_id,
ct.name
```
[]
(slow_query, 91.7, mysql)
Reported on decksite by mysql-perf
Location Hash: 5c8a85b213df89d7a387ee22284c50e99ddb0591
Labels: decksite
Exceeded slow_query limit (97.8 > 60.0) in mysql: ```
CREATE TABLE IF NOT EXISTS _new_matchup_stats (
archetype_id INT NOT NULL,
opponent_archetype_id INT NOT NULL,
season_id INT NOT NULL,
wins INT NOT NULL,
losses INT NOT NULL,
draws INT NOT NULL,
deck_type ENUM('league', 'tournament', 'other') NOT NULL,
PRIMARY KEY (season_id, archetype_id, opponent_archetype_id, 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,
FOREIGN KEY (opponent_archetype_id) REFERENCES archetype (id) ON UPDATE CASCADE ON DELETE CASCADE
) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci AS
SELECT
a.id AS archetype_id,
oa.id AS opponent_archetype_id,
season.season_id,
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,
(CASE WHEN ct.name = 'League' THEN 'league' WHEN ct.name = 'Gatherling' THEN 'tournament' ELSE 'other' END) AS deck_type
FROM
archetype AS a
INNER JOIN
deck AS d ON d.archetype_id IN (SELECT descendant FROM archetype_closure WHERE ancestor = a.id)
INNER JOIN
deck_match AS dm ON d.id = dm.deck_id
INNER JOIN
deck_match AS odm ON dm.match_id = odm.match_id AND odm.deck_id <> d.id
INNER JOIN
deck AS od ON od.id = odm.deck_id
INNER JOIN
archetype AS oa ON od.archetype_id IN (SELECT descendant FROM archetype_closure WHERE ancestor = oa.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
GROUP BY
a.id,
oa.id,
season.season_id,
ct.name
```
[]
(slow_query, 97.8, mysql)
Reported on decksite by mysql-perf
Location Hash: 5c8a85b213df89d7a387ee22284c50e99ddb0591
Labels: decksite
Exceeded slow_query limit (76.0 > 60.0) in mysql: ```
CREATE TABLE IF NOT EXISTS _new_matchup_stats (
archetype_id INT NOT NULL,
opponent_archetype_id INT NOT NULL,
season_id INT NOT NULL,
wins INT NOT NULL,
losses INT NOT NULL,
draws INT NOT NULL,
deck_type ENUM('league', 'tournament', 'other') NOT NULL,
PRIMARY KEY (season_id, archetype_id, opponent_archetype_id, 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,
FOREIGN KEY (opponent_archetype_id) REFERENCES archetype (id) ON UPDATE CASCADE ON DELETE CASCADE
) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci AS
SELECT
a.id AS archetype_id,
oa.id AS opponent_archetype_id,
season.season_id,
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,
(CASE WHEN ct.name = 'League' THEN 'league' WHEN ct.name = 'Gatherling' THEN 'tournament' ELSE 'other' END) AS deck_type
FROM
archetype AS a
INNER JOIN
deck AS d ON d.archetype_id IN (SELECT descendant FROM archetype_closure WHERE ancestor = a.id)
INNER JOIN
deck_match AS dm ON d.id = dm.deck_id
INNER JOIN
deck_match AS odm ON dm.match_id = odm.match_id AND odm.deck_id <> d.id
INNER JOIN
deck AS od ON od.id = odm.deck_id
INNER JOIN
archetype AS oa ON od.archetype_id IN (SELECT descendant FROM archetype_closure WHERE ancestor = oa.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
GROUP BY
a.id,
oa.id,
season.season_id,
ct.name
```
[]
(slow_query, 76.0, mysql)
Reported on decksite by mysql-perf
Location Hash: 5c8a85b213df89d7a387ee22284c50e99ddb0591
Labels: decksite
Exceeded slow_query limit (76.2 > 60.0) in mysql: ```
CREATE TABLE IF NOT EXISTS _new_matchup_stats (
archetype_id INT NOT NULL,
opponent_archetype_id INT NOT NULL,
season_id INT NOT NULL,
wins INT NOT NULL,
losses INT NOT NULL,
draws INT NOT NULL,
deck_type ENUM('league', 'tournament', 'other') NOT NULL,
PRIMARY KEY (season_id, archetype_id, opponent_archetype_id, 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,
FOREIGN KEY (opponent_archetype_id) REFERENCES archetype (id) ON UPDATE CASCADE ON DELETE CASCADE
) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci AS
SELECT
a.id AS archetype_id,
oa.id AS opponent_archetype_id,
season.season_id,
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,
(CASE WHEN ct.name = 'League' THEN 'league' WHEN ct.name = 'Gatherling' THEN 'tournament' ELSE 'other' END) AS deck_type
FROM
archetype AS a
INNER JOIN
deck AS d ON d.archetype_id IN (SELECT descendant FROM archetype_closure WHERE ancestor = a.id)
INNER JOIN
deck_match AS dm ON d.id = dm.deck_id
INNER JOIN
deck_match AS odm ON dm.match_id = odm.match_id AND odm.deck_id <> d.id
INNER JOIN
deck AS od ON od.id = odm.deck_id
INNER JOIN
archetype AS oa ON od.archetype_id IN (SELECT descendant FROM archetype_closure WHERE ancestor = oa.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
GROUP BY
a.id,
oa.id,
season.season_id,
ct.name
```
[]
(slow_query, 76.2, mysql)
Reported on decksite by mysql-perf
Location Hash: 5c8a85b213df89d7a387ee22284c50e99ddb0591
Labels: decksite
Exceeded slow_query limit (67.1 > 60.0) in mysql: ```
CREATE TABLE IF NOT EXISTS _new_matchup_stats (
archetype_id INT NOT NULL,
opponent_archetype_id INT NOT NULL,
season_id INT NOT NULL,
wins INT NOT NULL,
losses INT NOT NULL,
draws INT NOT NULL,
deck_type ENUM('league', 'tournament', 'other') NOT NULL,
PRIMARY KEY (season_id, archetype_id, opponent_archetype_id, 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,
FOREIGN KEY (opponent_archetype_id) REFERENCES archetype (id) ON UPDATE CASCADE ON DELETE CASCADE
) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci AS
SELECT
a.id AS archetype_id,
oa.id AS opponent_archetype_id,
season.season_id,
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,
(CASE WHEN ct.name = 'League' THEN 'league' WHEN ct.name = 'Gatherling' THEN 'tournament' ELSE 'other' END) AS deck_type
FROM
archetype AS a
INNER JOIN
deck AS d ON d.archetype_id IN (SELECT descendant FROM archetype_closure WHERE ancestor = a.id)
INNER JOIN
deck_match AS dm ON d.id = dm.deck_id
INNER JOIN
deck_match AS odm ON dm.match_id = odm.match_id AND odm.deck_id <> d.id
INNER JOIN
deck AS od ON od.id = odm.deck_id
INNER JOIN
archetype AS oa ON od.archetype_id IN (SELECT descendant FROM archetype_closure WHERE ancestor = oa.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
GROUP BY
a.id,
oa.id,
season.season_id,
ct.name
```
[]
(slow_query, 67.1, mysql)
Reported on decksite by mysql-perf
Location Hash: 5c8a85b213df89d7a387ee22284c50e99ddb0591
Labels: decksite
[]
(slow_query, 61.3, mysql)Reported on decksite by mysql-perf
Location Hash: 5c8a85b213df89d7a387ee22284c50e99ddb0591