Exceeded slow_query limit (1145.1 > 60.0) in mysql: ```

PennyDreadfulMTG / perf-reports

2 stars 2 forks source link

Exceeded slow_query limit (1145.1 > 60.0) in mysql: ``` #59595

Open vorpal-buildbot opened 2 years ago

vorpal-buildbot commented 2 years ago

    CREATE TABLE IF NOT EXISTS _new_card_stats (
        name VARCHAR(190) NOT NULL,
        season_id INT NOT NULL,
        num_decks INT NOT NULL,
        wins INT NOT NULL,
        losses INT NOT NULL,
        draws INT NOT NULL,
        perfect_runs INT NOT NULL,
        tournament_wins INT NOT NULL,
        tournament_top8s INT NOT NULL,
        deck_type ENUM('league', 'tournament', 'other') NOT NULL,
        PRIMARY KEY (season_id, name, deck_type),
        FOREIGN KEY (season_id) REFERENCES season (id) ON UPDATE CASCADE ON DELETE CASCADE
    ) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci AS
    SELECT
        card AS name,
        season.season_id,
        SUM(CASE WHEN d.id IS NOT NULL THEN 1 ELSE 0 END) AS num_decks,
        IFNULL(SUM(dsum.wins), 0) AS wins,
        IFNULL(SUM(dsum.losses), 0) AS losses,
        IFNULL(SUM(dsum.draws), 0) AS draws,
        SUM(CASE WHEN dsum.wins >= 5 AND dsum.losses = 0 AND d.source_id IN (SELECT id FROM source WHERE name = 'League') THEN 1 ELSE 0 END) AS perfect_runs,
        SUM(CASE WHEN dsum.finish = 1 THEN 1 ELSE 0 END) AS tournament_wins,
        SUM(CASE WHEN dsum.finish <= 8 THEN 1 ELSE 0 END) AS tournament_top8s,
        (CASE WHEN ct.name = 'League' THEN 'league' WHEN ct.name = 'Gatherling' THEN 'tournament' ELSE 'other' END) AS deck_type
    FROM
        deck AS d
    INNER JOIN
        -- Eiliminate maindeck/sideboard double-counting with DISTINCT. See #5493.
        (SELECT DISTINCT card, deck_id FROM deck_card) AS dc ON d.id = dc.deck_id

    LEFT JOIN
        competition AS c ON d.competition_id = c.id
    LEFT JOIN
        competition_series AS cs ON cs.id = c.competition_series_id
    LEFT JOIN
        competition_type AS ct ON ct.id = cs.competition_type_id

    LEFT JOIN deck_cache AS season ON d.id = season.deck_id

    LEFT JOIN
        (
            SELECT
                d.id,
                d.created_date,
                d.finish,
                SUM(CASE WHEN dm.games > IFNULL(odm.games, 0) THEN 1 ELSE 0 END) AS wins, -- IFNULL so we still count byes as wins.
                SUM(CASE WHEN dm.games < odm.games THEN 1 ELSE 0 END) AS losses,
                SUM(CASE WHEN dm.games = odm.games THEN 1 ELSE 0 END) AS draws
            FROM
                deck_match AS dm
            INNER JOIN
                deck_match AS odm ON dm.match_id = odm.match_id AND dm.deck_id <> odm.deck_id
            INNER JOIN
                deck AS d ON d.id = dm.deck_id
            GROUP BY
                d.id
        ) AS dsum ON d.id = dsum.id

    GROUP BY
        card,
        season.season_id,
        ct.name
```

[] (slow_query, 1145.1, mysql)

Reported on decksite by mysql-perf

Location Hash: 7c1b78e351d987fc4af4134cd398b330cb417fbe

vorpal-buildbot commented 2 years ago

Exceeded slow_query limit (1287.2 > 60.0) in mysql: ```

    CREATE TABLE IF NOT EXISTS _new_card_stats (
        name VARCHAR(190) NOT NULL,
        season_id INT NOT NULL,
        num_decks INT NOT NULL,
        wins INT NOT NULL,
        losses INT NOT NULL,
        draws INT NOT NULL,
        perfect_runs INT NOT NULL,
        tournament_wins INT NOT NULL,
        tournament_top8s INT NOT NULL,
        deck_type ENUM('league', 'tournament', 'other') NOT NULL,
        PRIMARY KEY (season_id, name, deck_type),
        FOREIGN KEY (season_id) REFERENCES season (id) ON UPDATE CASCADE ON DELETE CASCADE
    ) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci AS
    SELECT
        card AS name,
        season.season_id,
        SUM(CASE WHEN d.id IS NOT NULL THEN 1 ELSE 0 END) AS num_decks,
        IFNULL(SUM(dsum.wins), 0) AS wins,
        IFNULL(SUM(dsum.losses), 0) AS losses,
        IFNULL(SUM(dsum.draws), 0) AS draws,
        SUM(CASE WHEN dsum.wins >= 5 AND dsum.losses = 0 AND d.source_id IN (SELECT id FROM source WHERE name = 'League') THEN 1 ELSE 0 END) AS perfect_runs,
        SUM(CASE WHEN dsum.finish = 1 THEN 1 ELSE 0 END) AS tournament_wins,
        SUM(CASE WHEN dsum.finish <= 8 THEN 1 ELSE 0 END) AS tournament_top8s,
        (CASE WHEN ct.name = 'League' THEN 'league' WHEN ct.name = 'Gatherling' THEN 'tournament' ELSE 'other' END) AS deck_type
    FROM
        deck AS d
    INNER JOIN
        -- Eiliminate maindeck/sideboard double-counting with DISTINCT. See #5493.
        (SELECT DISTINCT card, deck_id FROM deck_card) AS dc ON d.id = dc.deck_id

    LEFT JOIN
        competition AS c ON d.competition_id = c.id
    LEFT JOIN
        competition_series AS cs ON cs.id = c.competition_series_id
    LEFT JOIN
        competition_type AS ct ON ct.id = cs.competition_type_id

    LEFT JOIN deck_cache AS season ON d.id = season.deck_id

    LEFT JOIN
        (
            SELECT
                d.id,
                d.created_date,
                d.finish,
                SUM(CASE WHEN dm.games > IFNULL(odm.games, 0) THEN 1 ELSE 0 END) AS wins, -- IFNULL so we still count byes as wins.
                SUM(CASE WHEN dm.games < odm.games THEN 1 ELSE 0 END) AS losses,
                SUM(CASE WHEN dm.games = odm.games THEN 1 ELSE 0 END) AS draws
            FROM
                deck_match AS dm
            INNER JOIN
                deck_match AS odm ON dm.match_id = odm.match_id AND dm.deck_id <> odm.deck_id
            INNER JOIN
                deck AS d ON d.id = dm.deck_id
            GROUP BY
                d.id
        ) AS dsum ON d.id = dsum.id

    GROUP BY
        card,
        season.season_id,
        ct.name
```

[] (slow_query, 1287.2, mysql)

Reported on decksite by mysql-perf

Location Hash: 7c1b78e351d987fc4af4134cd398b330cb417fbe

Labels: decksite

vorpal-buildbot commented 2 years ago

Exceeded slow_query limit (1264.1 > 60.0) in mysql: ```

    CREATE TABLE IF NOT EXISTS _new_card_stats (
        name VARCHAR(190) NOT NULL,
        season_id INT NOT NULL,
        num_decks INT NOT NULL,
        wins INT NOT NULL,
        losses INT NOT NULL,
        draws INT NOT NULL,
        perfect_runs INT NOT NULL,
        tournament_wins INT NOT NULL,
        tournament_top8s INT NOT NULL,
        deck_type ENUM('league', 'tournament', 'other') NOT NULL,
        PRIMARY KEY (season_id, name, deck_type),
        FOREIGN KEY (season_id) REFERENCES season (id) ON UPDATE CASCADE ON DELETE CASCADE
    ) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci AS
    SELECT
        card AS name,
        season.season_id,
        SUM(CASE WHEN d.id IS NOT NULL THEN 1 ELSE 0 END) AS num_decks,
        IFNULL(SUM(dsum.wins), 0) AS wins,
        IFNULL(SUM(dsum.losses), 0) AS losses,
        IFNULL(SUM(dsum.draws), 0) AS draws,
        SUM(CASE WHEN dsum.wins >= 5 AND dsum.losses = 0 AND d.source_id IN (SELECT id FROM source WHERE name = 'League') THEN 1 ELSE 0 END) AS perfect_runs,
        SUM(CASE WHEN dsum.finish = 1 THEN 1 ELSE 0 END) AS tournament_wins,
        SUM(CASE WHEN dsum.finish <= 8 THEN 1 ELSE 0 END) AS tournament_top8s,
        (CASE WHEN ct.name = 'League' THEN 'league' WHEN ct.name = 'Gatherling' THEN 'tournament' ELSE 'other' END) AS deck_type
    FROM
        deck AS d
    INNER JOIN
        -- Eiliminate maindeck/sideboard double-counting with DISTINCT. See #5493.
        (SELECT DISTINCT card, deck_id FROM deck_card) AS dc ON d.id = dc.deck_id

    LEFT JOIN
        competition AS c ON d.competition_id = c.id
    LEFT JOIN
        competition_series AS cs ON cs.id = c.competition_series_id
    LEFT JOIN
        competition_type AS ct ON ct.id = cs.competition_type_id

    LEFT JOIN deck_cache AS season ON d.id = season.deck_id

    LEFT JOIN
        (
            SELECT
                d.id,
                d.created_date,
                d.finish,
                SUM(CASE WHEN dm.games > IFNULL(odm.games, 0) THEN 1 ELSE 0 END) AS wins, -- IFNULL so we still count byes as wins.
                SUM(CASE WHEN dm.games < odm.games THEN 1 ELSE 0 END) AS losses,
                SUM(CASE WHEN dm.games = odm.games THEN 1 ELSE 0 END) AS draws
            FROM
                deck_match AS dm
            INNER JOIN
                deck_match AS odm ON dm.match_id = odm.match_id AND dm.deck_id <> odm.deck_id
            INNER JOIN
                deck AS d ON d.id = dm.deck_id
            GROUP BY
                d.id
        ) AS dsum ON d.id = dsum.id

    GROUP BY
        card,
        season.season_id,
        ct.name
```

[] (slow_query, 1264.1, mysql)

Reported on decksite by mysql-perf

Location Hash: 7c1b78e351d987fc4af4134cd398b330cb417fbe

Labels: decksite

vorpal-buildbot commented 2 years ago

Exceeded slow_query limit (1131.9 > 60.0) in mysql: ```

    CREATE TABLE IF NOT EXISTS _new_card_stats (
        name VARCHAR(190) NOT NULL,
        season_id INT NOT NULL,
        num_decks INT NOT NULL,
        wins INT NOT NULL,
        losses INT NOT NULL,
        draws INT NOT NULL,
        perfect_runs INT NOT NULL,
        tournament_wins INT NOT NULL,
        tournament_top8s INT NOT NULL,
        deck_type ENUM('league', 'tournament', 'other') NOT NULL,
        PRIMARY KEY (season_id, name, deck_type),
        FOREIGN KEY (season_id) REFERENCES season (id) ON UPDATE CASCADE ON DELETE CASCADE
    ) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci AS
    SELECT
        card AS name,
        season.season_id,
        SUM(CASE WHEN d.id IS NOT NULL THEN 1 ELSE 0 END) AS num_decks,
        IFNULL(SUM(dsum.wins), 0) AS wins,
        IFNULL(SUM(dsum.losses), 0) AS losses,
        IFNULL(SUM(dsum.draws), 0) AS draws,
        SUM(CASE WHEN dsum.wins >= 5 AND dsum.losses = 0 AND d.source_id IN (SELECT id FROM source WHERE name = 'League') THEN 1 ELSE 0 END) AS perfect_runs,
        SUM(CASE WHEN dsum.finish = 1 THEN 1 ELSE 0 END) AS tournament_wins,
        SUM(CASE WHEN dsum.finish <= 8 THEN 1 ELSE 0 END) AS tournament_top8s,
        (CASE WHEN ct.name = 'League' THEN 'league' WHEN ct.name = 'Gatherling' THEN 'tournament' ELSE 'other' END) AS deck_type
    FROM
        deck AS d
    INNER JOIN
        -- Eiliminate maindeck/sideboard double-counting with DISTINCT. See #5493.
        (SELECT DISTINCT card, deck_id FROM deck_card) AS dc ON d.id = dc.deck_id

    LEFT JOIN
        competition AS c ON d.competition_id = c.id
    LEFT JOIN
        competition_series AS cs ON cs.id = c.competition_series_id
    LEFT JOIN
        competition_type AS ct ON ct.id = cs.competition_type_id

    LEFT JOIN deck_cache AS season ON d.id = season.deck_id

    LEFT JOIN
        (
            SELECT
                d.id,
                d.created_date,
                d.finish,
                SUM(CASE WHEN dm.games > IFNULL(odm.games, 0) THEN 1 ELSE 0 END) AS wins, -- IFNULL so we still count byes as wins.
                SUM(CASE WHEN dm.games < odm.games THEN 1 ELSE 0 END) AS losses,
                SUM(CASE WHEN dm.games = odm.games THEN 1 ELSE 0 END) AS draws
            FROM
                deck_match AS dm
            INNER JOIN
                deck_match AS odm ON dm.match_id = odm.match_id AND dm.deck_id <> odm.deck_id
            INNER JOIN
                deck AS d ON d.id = dm.deck_id
            GROUP BY
                d.id
        ) AS dsum ON d.id = dsum.id

    GROUP BY
        card,
        season.season_id,
        ct.name
```

[] (slow_query, 1131.9, mysql)

Reported on decksite by mysql-perf

Location Hash: 7c1b78e351d987fc4af4134cd398b330cb417fbe

Labels: decksite

vorpal-buildbot commented 2 years ago

Exceeded slow_query limit (905.1 > 60.0) in mysql: ```

    CREATE TABLE IF NOT EXISTS _new_card_stats (
        name VARCHAR(190) NOT NULL,
        season_id INT NOT NULL,
        num_decks INT NOT NULL,
        wins INT NOT NULL,
        losses INT NOT NULL,
        draws INT NOT NULL,
        perfect_runs INT NOT NULL,
        tournament_wins INT NOT NULL,
        tournament_top8s INT NOT NULL,
        deck_type ENUM('league', 'tournament', 'other') NOT NULL,
        PRIMARY KEY (season_id, name, deck_type),
        FOREIGN KEY (season_id) REFERENCES season (id) ON UPDATE CASCADE ON DELETE CASCADE
    ) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci AS
    SELECT
        card AS name,
        season.season_id,
        SUM(CASE WHEN d.id IS NOT NULL THEN 1 ELSE 0 END) AS num_decks,
        IFNULL(SUM(dsum.wins), 0) AS wins,
        IFNULL(SUM(dsum.losses), 0) AS losses,
        IFNULL(SUM(dsum.draws), 0) AS draws,
        SUM(CASE WHEN dsum.wins >= 5 AND dsum.losses = 0 AND d.source_id IN (SELECT id FROM source WHERE name = 'League') THEN 1 ELSE 0 END) AS perfect_runs,
        SUM(CASE WHEN dsum.finish = 1 THEN 1 ELSE 0 END) AS tournament_wins,
        SUM(CASE WHEN dsum.finish <= 8 THEN 1 ELSE 0 END) AS tournament_top8s,
        (CASE WHEN ct.name = 'League' THEN 'league' WHEN ct.name = 'Gatherling' THEN 'tournament' ELSE 'other' END) AS deck_type
    FROM
        deck AS d
    INNER JOIN
        -- Eiliminate maindeck/sideboard double-counting with DISTINCT. See #5493.
        (SELECT DISTINCT card, deck_id FROM deck_card) AS dc ON d.id = dc.deck_id

    LEFT JOIN
        competition AS c ON d.competition_id = c.id
    LEFT JOIN
        competition_series AS cs ON cs.id = c.competition_series_id
    LEFT JOIN
        competition_type AS ct ON ct.id = cs.competition_type_id

    LEFT JOIN deck_cache AS season ON d.id = season.deck_id

    LEFT JOIN
        (
            SELECT
                d.id,
                d.created_date,
                d.finish,
                SUM(CASE WHEN dm.games > IFNULL(odm.games, 0) THEN 1 ELSE 0 END) AS wins, -- IFNULL so we still count byes as wins.
                SUM(CASE WHEN dm.games < odm.games THEN 1 ELSE 0 END) AS losses,
                SUM(CASE WHEN dm.games = odm.games THEN 1 ELSE 0 END) AS draws
            FROM
                deck_match AS dm
            INNER JOIN
                deck_match AS odm ON dm.match_id = odm.match_id AND dm.deck_id <> odm.deck_id
            INNER JOIN
                deck AS d ON d.id = dm.deck_id
            GROUP BY
                d.id
        ) AS dsum ON d.id = dsum.id

    GROUP BY
        card,
        season.season_id,
        ct.name
```

[] (slow_query, 905.1, mysql)

Reported on decksite by mysql-perf

Location Hash: 7c1b78e351d987fc4af4134cd398b330cb417fbe

Labels: decksite

vorpal-buildbot commented 2 years ago

Exceeded slow_query limit (1055.1 > 60.0) in mysql: ```

    CREATE TABLE IF NOT EXISTS _new_card_stats (
        name VARCHAR(190) NOT NULL,
        season_id INT NOT NULL,
        num_decks INT NOT NULL,
        wins INT NOT NULL,
        losses INT NOT NULL,
        draws INT NOT NULL,
        perfect_runs INT NOT NULL,
        tournament_wins INT NOT NULL,
        tournament_top8s INT NOT NULL,
        deck_type ENUM('league', 'tournament', 'other') NOT NULL,
        PRIMARY KEY (season_id, name, deck_type),
        FOREIGN KEY (season_id) REFERENCES season (id) ON UPDATE CASCADE ON DELETE CASCADE
    ) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci AS
    SELECT
        card AS name,
        season.season_id,
        SUM(CASE WHEN d.id IS NOT NULL THEN 1 ELSE 0 END) AS num_decks,
        IFNULL(SUM(dsum.wins), 0) AS wins,
        IFNULL(SUM(dsum.losses), 0) AS losses,
        IFNULL(SUM(dsum.draws), 0) AS draws,
        SUM(CASE WHEN dsum.wins >= 5 AND dsum.losses = 0 AND d.source_id IN (SELECT id FROM source WHERE name = 'League') THEN 1 ELSE 0 END) AS perfect_runs,
        SUM(CASE WHEN dsum.finish = 1 THEN 1 ELSE 0 END) AS tournament_wins,
        SUM(CASE WHEN dsum.finish <= 8 THEN 1 ELSE 0 END) AS tournament_top8s,
        (CASE WHEN ct.name = 'League' THEN 'league' WHEN ct.name = 'Gatherling' THEN 'tournament' ELSE 'other' END) AS deck_type
    FROM
        deck AS d
    INNER JOIN
        -- Eiliminate maindeck/sideboard double-counting with DISTINCT. See #5493.
        (SELECT DISTINCT card, deck_id FROM deck_card) AS dc ON d.id = dc.deck_id

    LEFT JOIN
        competition AS c ON d.competition_id = c.id
    LEFT JOIN
        competition_series AS cs ON cs.id = c.competition_series_id
    LEFT JOIN
        competition_type AS ct ON ct.id = cs.competition_type_id

    LEFT JOIN deck_cache AS season ON d.id = season.deck_id

    LEFT JOIN
        (
            SELECT
                d.id,
                d.created_date,
                d.finish,
                SUM(CASE WHEN dm.games > IFNULL(odm.games, 0) THEN 1 ELSE 0 END) AS wins, -- IFNULL so we still count byes as wins.
                SUM(CASE WHEN dm.games < odm.games THEN 1 ELSE 0 END) AS losses,
                SUM(CASE WHEN dm.games = odm.games THEN 1 ELSE 0 END) AS draws
            FROM
                deck_match AS dm
            INNER JOIN
                deck_match AS odm ON dm.match_id = odm.match_id AND dm.deck_id <> odm.deck_id
            INNER JOIN
                deck AS d ON d.id = dm.deck_id
            GROUP BY
                d.id
        ) AS dsum ON d.id = dsum.id

    GROUP BY
        card,
        season.season_id,
        ct.name
```

[] (slow_query, 1055.1, mysql)

Reported on decksite by mysql-perf

Location Hash: 7c1b78e351d987fc4af4134cd398b330cb417fbe

Labels: decksite

vorpal-buildbot commented 2 years ago

Exceeded slow_query limit (1007.1 > 60.0) in mysql: ```

    CREATE TABLE IF NOT EXISTS _new_card_stats (
        name VARCHAR(190) NOT NULL,
        season_id INT NOT NULL,
        num_decks INT NOT NULL,
        wins INT NOT NULL,
        losses INT NOT NULL,
        draws INT NOT NULL,
        perfect_runs INT NOT NULL,
        tournament_wins INT NOT NULL,
        tournament_top8s INT NOT NULL,
        deck_type ENUM('league', 'tournament', 'other') NOT NULL,
        PRIMARY KEY (season_id, name, deck_type),
        FOREIGN KEY (season_id) REFERENCES season (id) ON UPDATE CASCADE ON DELETE CASCADE
    ) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci AS
    SELECT
        card AS name,
        season.season_id,
        SUM(CASE WHEN d.id IS NOT NULL THEN 1 ELSE 0 END) AS num_decks,
        IFNULL(SUM(dsum.wins), 0) AS wins,
        IFNULL(SUM(dsum.losses), 0) AS losses,
        IFNULL(SUM(dsum.draws), 0) AS draws,
        SUM(CASE WHEN dsum.wins >= 5 AND dsum.losses = 0 AND d.source_id IN (SELECT id FROM source WHERE name = 'League') THEN 1 ELSE 0 END) AS perfect_runs,
        SUM(CASE WHEN dsum.finish = 1 THEN 1 ELSE 0 END) AS tournament_wins,
        SUM(CASE WHEN dsum.finish <= 8 THEN 1 ELSE 0 END) AS tournament_top8s,
        (CASE WHEN ct.name = 'League' THEN 'league' WHEN ct.name = 'Gatherling' THEN 'tournament' ELSE 'other' END) AS deck_type
    FROM
        deck AS d
    INNER JOIN
        -- Eiliminate maindeck/sideboard double-counting with DISTINCT. See #5493.
        (SELECT DISTINCT card, deck_id FROM deck_card) AS dc ON d.id = dc.deck_id

    LEFT JOIN
        competition AS c ON d.competition_id = c.id
    LEFT JOIN
        competition_series AS cs ON cs.id = c.competition_series_id
    LEFT JOIN
        competition_type AS ct ON ct.id = cs.competition_type_id

    LEFT JOIN deck_cache AS season ON d.id = season.deck_id

    LEFT JOIN
        (
            SELECT
                d.id,
                d.created_date,
                d.finish,
                SUM(CASE WHEN dm.games > IFNULL(odm.games, 0) THEN 1 ELSE 0 END) AS wins, -- IFNULL so we still count byes as wins.
                SUM(CASE WHEN dm.games < odm.games THEN 1 ELSE 0 END) AS losses,
                SUM(CASE WHEN dm.games = odm.games THEN 1 ELSE 0 END) AS draws
            FROM
                deck_match AS dm
            INNER JOIN
                deck_match AS odm ON dm.match_id = odm.match_id AND dm.deck_id <> odm.deck_id
            INNER JOIN
                deck AS d ON d.id = dm.deck_id
            GROUP BY
                d.id
        ) AS dsum ON d.id = dsum.id

    GROUP BY
        card,
        season.season_id,
        ct.name
```

[] (slow_query, 1007.1, mysql)

Reported on decksite by mysql-perf

Location Hash: 7c1b78e351d987fc4af4134cd398b330cb417fbe

Labels: decksite

vorpal-buildbot commented 2 years ago

Exceeded slow_query limit (915.7 > 60.0) in mysql: ```

    CREATE TABLE IF NOT EXISTS _new_card_stats (
        name VARCHAR(190) NOT NULL,
        season_id INT NOT NULL,
        num_decks INT NOT NULL,
        wins INT NOT NULL,
        losses INT NOT NULL,
        draws INT NOT NULL,
        perfect_runs INT NOT NULL,
        tournament_wins INT NOT NULL,
        tournament_top8s INT NOT NULL,
        deck_type ENUM('league', 'tournament', 'other') NOT NULL,
        PRIMARY KEY (season_id, name, deck_type),
        FOREIGN KEY (season_id) REFERENCES season (id) ON UPDATE CASCADE ON DELETE CASCADE
    ) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci AS
    SELECT
        card AS name,
        season.season_id,
        SUM(CASE WHEN d.id IS NOT NULL THEN 1 ELSE 0 END) AS num_decks,
        IFNULL(SUM(dsum.wins), 0) AS wins,
        IFNULL(SUM(dsum.losses), 0) AS losses,
        IFNULL(SUM(dsum.draws), 0) AS draws,
        SUM(CASE WHEN dsum.wins >= 5 AND dsum.losses = 0 AND d.source_id IN (SELECT id FROM source WHERE name = 'League') THEN 1 ELSE 0 END) AS perfect_runs,
        SUM(CASE WHEN dsum.finish = 1 THEN 1 ELSE 0 END) AS tournament_wins,
        SUM(CASE WHEN dsum.finish <= 8 THEN 1 ELSE 0 END) AS tournament_top8s,
        (CASE WHEN ct.name = 'League' THEN 'league' WHEN ct.name = 'Gatherling' THEN 'tournament' ELSE 'other' END) AS deck_type
    FROM
        deck AS d
    INNER JOIN
        -- Eiliminate maindeck/sideboard double-counting with DISTINCT. See #5493.
        (SELECT DISTINCT card, deck_id FROM deck_card) AS dc ON d.id = dc.deck_id

    LEFT JOIN
        competition AS c ON d.competition_id = c.id
    LEFT JOIN
        competition_series AS cs ON cs.id = c.competition_series_id
    LEFT JOIN
        competition_type AS ct ON ct.id = cs.competition_type_id

    LEFT JOIN deck_cache AS season ON d.id = season.deck_id

    LEFT JOIN
        (
            SELECT
                d.id,
                d.created_date,
                d.finish,
                SUM(CASE WHEN dm.games > IFNULL(odm.games, 0) THEN 1 ELSE 0 END) AS wins, -- IFNULL so we still count byes as wins.
                SUM(CASE WHEN dm.games < odm.games THEN 1 ELSE 0 END) AS losses,
                SUM(CASE WHEN dm.games = odm.games THEN 1 ELSE 0 END) AS draws
            FROM
                deck_match AS dm
            INNER JOIN
                deck_match AS odm ON dm.match_id = odm.match_id AND dm.deck_id <> odm.deck_id
            INNER JOIN
                deck AS d ON d.id = dm.deck_id
            GROUP BY
                d.id
        ) AS dsum ON d.id = dsum.id

    GROUP BY
        card,
        season.season_id,
        ct.name
```

[] (slow_query, 915.7, mysql)

Reported on decksite by mysql-perf

Location Hash: 7c1b78e351d987fc4af4134cd398b330cb417fbe

Labels: decksite

vorpal-buildbot commented 2 years ago

Exceeded slow_query limit (895.9 > 60.0) in mysql: ```

    CREATE TABLE IF NOT EXISTS _new_card_stats (
        name VARCHAR(190) NOT NULL,
        season_id INT NOT NULL,
        num_decks INT NOT NULL,
        wins INT NOT NULL,
        losses INT NOT NULL,
        draws INT NOT NULL,
        perfect_runs INT NOT NULL,
        tournament_wins INT NOT NULL,
        tournament_top8s INT NOT NULL,
        deck_type ENUM('league', 'tournament', 'other') NOT NULL,
        PRIMARY KEY (season_id, name, deck_type),
        FOREIGN KEY (season_id) REFERENCES season (id) ON UPDATE CASCADE ON DELETE CASCADE
    ) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci AS
    SELECT
        card AS name,
        season.season_id,
        SUM(CASE WHEN d.id IS NOT NULL THEN 1 ELSE 0 END) AS num_decks,
        IFNULL(SUM(dsum.wins), 0) AS wins,
        IFNULL(SUM(dsum.losses), 0) AS losses,
        IFNULL(SUM(dsum.draws), 0) AS draws,
        SUM(CASE WHEN dsum.wins >= 5 AND dsum.losses = 0 AND d.source_id IN (SELECT id FROM source WHERE name = 'League') THEN 1 ELSE 0 END) AS perfect_runs,
        SUM(CASE WHEN dsum.finish = 1 THEN 1 ELSE 0 END) AS tournament_wins,
        SUM(CASE WHEN dsum.finish <= 8 THEN 1 ELSE 0 END) AS tournament_top8s,
        (CASE WHEN ct.name = 'League' THEN 'league' WHEN ct.name = 'Gatherling' THEN 'tournament' ELSE 'other' END) AS deck_type
    FROM
        deck AS d
    INNER JOIN
        -- Eiliminate maindeck/sideboard double-counting with DISTINCT. See #5493.
        (SELECT DISTINCT card, deck_id FROM deck_card) AS dc ON d.id = dc.deck_id

    LEFT JOIN
        competition AS c ON d.competition_id = c.id
    LEFT JOIN
        competition_series AS cs ON cs.id = c.competition_series_id
    LEFT JOIN
        competition_type AS ct ON ct.id = cs.competition_type_id

    LEFT JOIN deck_cache AS season ON d.id = season.deck_id

    LEFT JOIN
        (
            SELECT
                d.id,
                d.created_date,
                d.finish,
                SUM(CASE WHEN dm.games > IFNULL(odm.games, 0) THEN 1 ELSE 0 END) AS wins, -- IFNULL so we still count byes as wins.
                SUM(CASE WHEN dm.games < odm.games THEN 1 ELSE 0 END) AS losses,
                SUM(CASE WHEN dm.games = odm.games THEN 1 ELSE 0 END) AS draws
            FROM
                deck_match AS dm
            INNER JOIN
                deck_match AS odm ON dm.match_id = odm.match_id AND dm.deck_id <> odm.deck_id
            INNER JOIN
                deck AS d ON d.id = dm.deck_id
            GROUP BY
                d.id
        ) AS dsum ON d.id = dsum.id

    GROUP BY
        card,
        season.season_id,
        ct.name
```

[] (slow_query, 895.9, mysql)

Reported on decksite by mysql-perf

Location Hash: 7c1b78e351d987fc4af4134cd398b330cb417fbe

Labels: decksite

vorpal-buildbot commented 2 years ago

Exceeded slow_query limit (810.1 > 60.0) in mysql: ```

    CREATE TABLE IF NOT EXISTS _new_card_stats (
        name VARCHAR(190) NOT NULL,
        season_id INT NOT NULL,
        num_decks INT NOT NULL,
        wins INT NOT NULL,
        losses INT NOT NULL,
        draws INT NOT NULL,
        perfect_runs INT NOT NULL,
        tournament_wins INT NOT NULL,
        tournament_top8s INT NOT NULL,
        deck_type ENUM('league', 'tournament', 'other') NOT NULL,
        PRIMARY KEY (season_id, name, deck_type),
        FOREIGN KEY (season_id) REFERENCES season (id) ON UPDATE CASCADE ON DELETE CASCADE
    ) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci AS
    SELECT
        card AS name,
        season.season_id,
        SUM(CASE WHEN d.id IS NOT NULL THEN 1 ELSE 0 END) AS num_decks,
        IFNULL(SUM(dsum.wins), 0) AS wins,
        IFNULL(SUM(dsum.losses), 0) AS losses,
        IFNULL(SUM(dsum.draws), 0) AS draws,
        SUM(CASE WHEN dsum.wins >= 5 AND dsum.losses = 0 AND d.source_id IN (SELECT id FROM source WHERE name = 'League') THEN 1 ELSE 0 END) AS perfect_runs,
        SUM(CASE WHEN dsum.finish = 1 THEN 1 ELSE 0 END) AS tournament_wins,
        SUM(CASE WHEN dsum.finish <= 8 THEN 1 ELSE 0 END) AS tournament_top8s,
        (CASE WHEN ct.name = 'League' THEN 'league' WHEN ct.name = 'Gatherling' THEN 'tournament' ELSE 'other' END) AS deck_type
    FROM
        deck AS d
    INNER JOIN
        -- Eiliminate maindeck/sideboard double-counting with DISTINCT. See #5493.
        (SELECT DISTINCT card, deck_id FROM deck_card) AS dc ON d.id = dc.deck_id

    LEFT JOIN
        competition AS c ON d.competition_id = c.id
    LEFT JOIN
        competition_series AS cs ON cs.id = c.competition_series_id
    LEFT JOIN
        competition_type AS ct ON ct.id = cs.competition_type_id

    LEFT JOIN deck_cache AS season ON d.id = season.deck_id

    LEFT JOIN
        (
            SELECT
                d.id,
                d.created_date,
                d.finish,
                SUM(CASE WHEN dm.games > IFNULL(odm.games, 0) THEN 1 ELSE 0 END) AS wins, -- IFNULL so we still count byes as wins.
                SUM(CASE WHEN dm.games < odm.games THEN 1 ELSE 0 END) AS losses,
                SUM(CASE WHEN dm.games = odm.games THEN 1 ELSE 0 END) AS draws
            FROM
                deck_match AS dm
            INNER JOIN
                deck_match AS odm ON dm.match_id = odm.match_id AND dm.deck_id <> odm.deck_id
            INNER JOIN
                deck AS d ON d.id = dm.deck_id
            GROUP BY
                d.id
        ) AS dsum ON d.id = dsum.id

    GROUP BY
        card,
        season.season_id,
        ct.name
```

[] (slow_query, 810.1, mysql)

Reported on decksite by mysql-perf

Location Hash: 7c1b78e351d987fc4af4134cd398b330cb417fbe

Labels: decksite

vorpal-buildbot commented 2 years ago

Exceeded slow_query limit (814.1 > 60.0) in mysql: ```

    CREATE TABLE IF NOT EXISTS _new_card_stats (
        name VARCHAR(190) NOT NULL,
        season_id INT NOT NULL,
        num_decks INT NOT NULL,
        wins INT NOT NULL,
        losses INT NOT NULL,
        draws INT NOT NULL,
        perfect_runs INT NOT NULL,
        tournament_wins INT NOT NULL,
        tournament_top8s INT NOT NULL,
        deck_type ENUM('league', 'tournament', 'other') NOT NULL,
        PRIMARY KEY (season_id, name, deck_type),
        FOREIGN KEY (season_id) REFERENCES season (id) ON UPDATE CASCADE ON DELETE CASCADE
    ) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci AS
    SELECT
        card AS name,
        season.season_id,
        SUM(CASE WHEN d.id IS NOT NULL THEN 1 ELSE 0 END) AS num_decks,
        IFNULL(SUM(dsum.wins), 0) AS wins,
        IFNULL(SUM(dsum.losses), 0) AS losses,
        IFNULL(SUM(dsum.draws), 0) AS draws,
        SUM(CASE WHEN dsum.wins >= 5 AND dsum.losses = 0 AND d.source_id IN (SELECT id FROM source WHERE name = 'League') THEN 1 ELSE 0 END) AS perfect_runs,
        SUM(CASE WHEN dsum.finish = 1 THEN 1 ELSE 0 END) AS tournament_wins,
        SUM(CASE WHEN dsum.finish <= 8 THEN 1 ELSE 0 END) AS tournament_top8s,
        (CASE WHEN ct.name = 'League' THEN 'league' WHEN ct.name = 'Gatherling' THEN 'tournament' ELSE 'other' END) AS deck_type
    FROM
        deck AS d
    INNER JOIN
        -- Eiliminate maindeck/sideboard double-counting with DISTINCT. See #5493.
        (SELECT DISTINCT card, deck_id FROM deck_card) AS dc ON d.id = dc.deck_id

    LEFT JOIN
        competition AS c ON d.competition_id = c.id
    LEFT JOIN
        competition_series AS cs ON cs.id = c.competition_series_id
    LEFT JOIN
        competition_type AS ct ON ct.id = cs.competition_type_id

    LEFT JOIN deck_cache AS season ON d.id = season.deck_id

    LEFT JOIN
        (
            SELECT
                d.id,
                d.created_date,
                d.finish,
                SUM(CASE WHEN dm.games > IFNULL(odm.games, 0) THEN 1 ELSE 0 END) AS wins, -- IFNULL so we still count byes as wins.
                SUM(CASE WHEN dm.games < odm.games THEN 1 ELSE 0 END) AS losses,
                SUM(CASE WHEN dm.games = odm.games THEN 1 ELSE 0 END) AS draws
            FROM
                deck_match AS dm
            INNER JOIN
                deck_match AS odm ON dm.match_id = odm.match_id AND dm.deck_id <> odm.deck_id
            INNER JOIN
                deck AS d ON d.id = dm.deck_id
            GROUP BY
                d.id
        ) AS dsum ON d.id = dsum.id

    GROUP BY
        card,
        season.season_id,
        ct.name
```

[] (slow_query, 814.1, mysql)

Reported on decksite by mysql-perf

Location Hash: 7c1b78e351d987fc4af4134cd398b330cb417fbe

Labels: decksite

vorpal-buildbot commented 2 years ago

Exceeded slow_query limit (957.5 > 60.0) in mysql: ```

    CREATE TABLE IF NOT EXISTS _new_card_stats (
        name VARCHAR(190) NOT NULL,
        season_id INT NOT NULL,
        num_decks INT NOT NULL,
        wins INT NOT NULL,
        losses INT NOT NULL,
        draws INT NOT NULL,
        perfect_runs INT NOT NULL,
        tournament_wins INT NOT NULL,
        tournament_top8s INT NOT NULL,
        deck_type ENUM('league', 'tournament', 'other') NOT NULL,
        PRIMARY KEY (season_id, name, deck_type),
        FOREIGN KEY (season_id) REFERENCES season (id) ON UPDATE CASCADE ON DELETE CASCADE
    ) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci AS
    SELECT
        card AS name,
        season.season_id,
        SUM(CASE WHEN d.id IS NOT NULL THEN 1 ELSE 0 END) AS num_decks,
        IFNULL(SUM(dsum.wins), 0) AS wins,
        IFNULL(SUM(dsum.losses), 0) AS losses,
        IFNULL(SUM(dsum.draws), 0) AS draws,
        SUM(CASE WHEN dsum.wins >= 5 AND dsum.losses = 0 AND d.source_id IN (SELECT id FROM source WHERE name = 'League') THEN 1 ELSE 0 END) AS perfect_runs,
        SUM(CASE WHEN dsum.finish = 1 THEN 1 ELSE 0 END) AS tournament_wins,
        SUM(CASE WHEN dsum.finish <= 8 THEN 1 ELSE 0 END) AS tournament_top8s,
        (CASE WHEN ct.name = 'League' THEN 'league' WHEN ct.name = 'Gatherling' THEN 'tournament' ELSE 'other' END) AS deck_type
    FROM
        deck AS d
    INNER JOIN
        -- Eiliminate maindeck/sideboard double-counting with DISTINCT. See #5493.
        (SELECT DISTINCT card, deck_id FROM deck_card) AS dc ON d.id = dc.deck_id

    LEFT JOIN
        competition AS c ON d.competition_id = c.id
    LEFT JOIN
        competition_series AS cs ON cs.id = c.competition_series_id
    LEFT JOIN
        competition_type AS ct ON ct.id = cs.competition_type_id

    LEFT JOIN deck_cache AS season ON d.id = season.deck_id

    LEFT JOIN
        (
            SELECT
                d.id,
                d.created_date,
                d.finish,
                SUM(CASE WHEN dm.games > IFNULL(odm.games, 0) THEN 1 ELSE 0 END) AS wins, -- IFNULL so we still count byes as wins.
                SUM(CASE WHEN dm.games < odm.games THEN 1 ELSE 0 END) AS losses,
                SUM(CASE WHEN dm.games = odm.games THEN 1 ELSE 0 END) AS draws
            FROM
                deck_match AS dm
            INNER JOIN
                deck_match AS odm ON dm.match_id = odm.match_id AND dm.deck_id <> odm.deck_id
            INNER JOIN
                deck AS d ON d.id = dm.deck_id
            GROUP BY
                d.id
        ) AS dsum ON d.id = dsum.id

    GROUP BY
        card,
        season.season_id,
        ct.name
```

[] (slow_query, 957.5, mysql)

Reported on decksite by mysql-perf

Location Hash: 7c1b78e351d987fc4af4134cd398b330cb417fbe

Labels: decksite

vorpal-buildbot commented 2 years ago

Exceeded slow_query limit (1036.6 > 60.0) in mysql: ```

    CREATE TABLE IF NOT EXISTS _new_card_stats (
        name VARCHAR(190) NOT NULL,
        season_id INT NOT NULL,
        num_decks INT NOT NULL,
        wins INT NOT NULL,
        losses INT NOT NULL,
        draws INT NOT NULL,
        perfect_runs INT NOT NULL,
        tournament_wins INT NOT NULL,
        tournament_top8s INT NOT NULL,
        deck_type ENUM('league', 'tournament', 'other') NOT NULL,
        PRIMARY KEY (season_id, name, deck_type),
        FOREIGN KEY (season_id) REFERENCES season (id) ON UPDATE CASCADE ON DELETE CASCADE
    ) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci AS
    SELECT
        card AS name,
        season.season_id,
        SUM(CASE WHEN d.id IS NOT NULL THEN 1 ELSE 0 END) AS num_decks,
        IFNULL(SUM(dsum.wins), 0) AS wins,
        IFNULL(SUM(dsum.losses), 0) AS losses,
        IFNULL(SUM(dsum.draws), 0) AS draws,
        SUM(CASE WHEN dsum.wins >= 5 AND dsum.losses = 0 AND d.source_id IN (SELECT id FROM source WHERE name = 'League') THEN 1 ELSE 0 END) AS perfect_runs,
        SUM(CASE WHEN dsum.finish = 1 THEN 1 ELSE 0 END) AS tournament_wins,
        SUM(CASE WHEN dsum.finish <= 8 THEN 1 ELSE 0 END) AS tournament_top8s,
        (CASE WHEN ct.name = 'League' THEN 'league' WHEN ct.name = 'Gatherling' THEN 'tournament' ELSE 'other' END) AS deck_type
    FROM
        deck AS d
    INNER JOIN
        -- Eiliminate maindeck/sideboard double-counting with DISTINCT. See #5493.
        (SELECT DISTINCT card, deck_id FROM deck_card) AS dc ON d.id = dc.deck_id

    LEFT JOIN
        competition AS c ON d.competition_id = c.id
    LEFT JOIN
        competition_series AS cs ON cs.id = c.competition_series_id
    LEFT JOIN
        competition_type AS ct ON ct.id = cs.competition_type_id

    LEFT JOIN deck_cache AS season ON d.id = season.deck_id

    LEFT JOIN
        (
            SELECT
                d.id,
                d.created_date,
                d.finish,
                SUM(CASE WHEN dm.games > IFNULL(odm.games, 0) THEN 1 ELSE 0 END) AS wins, -- IFNULL so we still count byes as wins.
                SUM(CASE WHEN dm.games < odm.games THEN 1 ELSE 0 END) AS losses,
                SUM(CASE WHEN dm.games = odm.games THEN 1 ELSE 0 END) AS draws
            FROM
                deck_match AS dm
            INNER JOIN
                deck_match AS odm ON dm.match_id = odm.match_id AND dm.deck_id <> odm.deck_id
            INNER JOIN
                deck AS d ON d.id = dm.deck_id
            GROUP BY
                d.id
        ) AS dsum ON d.id = dsum.id

    GROUP BY
        card,
        season.season_id,
        ct.name
```

[] (slow_query, 1036.6, mysql)

Reported on decksite by mysql-perf

Location Hash: 7c1b78e351d987fc4af4134cd398b330cb417fbe

Labels: decksite

vorpal-buildbot commented 2 years ago

Exceeded slow_query limit (1190.3 > 60.0) in mysql: ```

    CREATE TABLE IF NOT EXISTS _new_card_stats (
        name VARCHAR(190) NOT NULL,
        season_id INT NOT NULL,
        num_decks INT NOT NULL,
        wins INT NOT NULL,
        losses INT NOT NULL,
        draws INT NOT NULL,
        perfect_runs INT NOT NULL,
        tournament_wins INT NOT NULL,
        tournament_top8s INT NOT NULL,
        deck_type ENUM('league', 'tournament', 'other') NOT NULL,
        PRIMARY KEY (season_id, name, deck_type),
        FOREIGN KEY (season_id) REFERENCES season (id) ON UPDATE CASCADE ON DELETE CASCADE
    ) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci AS
    SELECT
        card AS name,
        season.season_id,
        SUM(CASE WHEN d.id IS NOT NULL THEN 1 ELSE 0 END) AS num_decks,
        IFNULL(SUM(dsum.wins), 0) AS wins,
        IFNULL(SUM(dsum.losses), 0) AS losses,
        IFNULL(SUM(dsum.draws), 0) AS draws,
        SUM(CASE WHEN dsum.wins >= 5 AND dsum.losses = 0 AND d.source_id IN (SELECT id FROM source WHERE name = 'League') THEN 1 ELSE 0 END) AS perfect_runs,
        SUM(CASE WHEN dsum.finish = 1 THEN 1 ELSE 0 END) AS tournament_wins,
        SUM(CASE WHEN dsum.finish <= 8 THEN 1 ELSE 0 END) AS tournament_top8s,
        (CASE WHEN ct.name = 'League' THEN 'league' WHEN ct.name = 'Gatherling' THEN 'tournament' ELSE 'other' END) AS deck_type
    FROM
        deck AS d
    INNER JOIN
        -- Eiliminate maindeck/sideboard double-counting with DISTINCT. See #5493.
        (SELECT DISTINCT card, deck_id FROM deck_card) AS dc ON d.id = dc.deck_id

    LEFT JOIN
        competition AS c ON d.competition_id = c.id
    LEFT JOIN
        competition_series AS cs ON cs.id = c.competition_series_id
    LEFT JOIN
        competition_type AS ct ON ct.id = cs.competition_type_id

    LEFT JOIN deck_cache AS season ON d.id = season.deck_id

    LEFT JOIN
        (
            SELECT
                d.id,
                d.created_date,
                d.finish,
                SUM(CASE WHEN dm.games > IFNULL(odm.games, 0) THEN 1 ELSE 0 END) AS wins, -- IFNULL so we still count byes as wins.
                SUM(CASE WHEN dm.games < odm.games THEN 1 ELSE 0 END) AS losses,
                SUM(CASE WHEN dm.games = odm.games THEN 1 ELSE 0 END) AS draws
            FROM
                deck_match AS dm
            INNER JOIN
                deck_match AS odm ON dm.match_id = odm.match_id AND dm.deck_id <> odm.deck_id
            INNER JOIN
                deck AS d ON d.id = dm.deck_id
            GROUP BY
                d.id
        ) AS dsum ON d.id = dsum.id

    GROUP BY
        card,
        season.season_id,
        ct.name
```

[] (slow_query, 1190.3, mysql)

Reported on decksite by mysql-perf

Location Hash: 7c1b78e351d987fc4af4134cd398b330cb417fbe

Labels: decksite

vorpal-buildbot commented 2 years ago

Exceeded slow_query limit (959.6 > 60.0) in mysql: ```

    CREATE TABLE IF NOT EXISTS _new_card_stats (
        name VARCHAR(190) NOT NULL,
        season_id INT NOT NULL,
        num_decks INT NOT NULL,
        wins INT NOT NULL,
        losses INT NOT NULL,
        draws INT NOT NULL,
        perfect_runs INT NOT NULL,
        tournament_wins INT NOT NULL,
        tournament_top8s INT NOT NULL,
        deck_type ENUM('league', 'tournament', 'other') NOT NULL,
        PRIMARY KEY (season_id, name, deck_type),
        FOREIGN KEY (season_id) REFERENCES season (id) ON UPDATE CASCADE ON DELETE CASCADE
    ) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci AS
    SELECT
        card AS name,
        season.season_id,
        SUM(CASE WHEN d.id IS NOT NULL THEN 1 ELSE 0 END) AS num_decks,
        IFNULL(SUM(dsum.wins), 0) AS wins,
        IFNULL(SUM(dsum.losses), 0) AS losses,
        IFNULL(SUM(dsum.draws), 0) AS draws,
        SUM(CASE WHEN dsum.wins >= 5 AND dsum.losses = 0 AND d.source_id IN (SELECT id FROM source WHERE name = 'League') THEN 1 ELSE 0 END) AS perfect_runs,
        SUM(CASE WHEN dsum.finish = 1 THEN 1 ELSE 0 END) AS tournament_wins,
        SUM(CASE WHEN dsum.finish <= 8 THEN 1 ELSE 0 END) AS tournament_top8s,
        (CASE WHEN ct.name = 'League' THEN 'league' WHEN ct.name = 'Gatherling' THEN 'tournament' ELSE 'other' END) AS deck_type
    FROM
        deck AS d
    INNER JOIN
        -- Eiliminate maindeck/sideboard double-counting with DISTINCT. See #5493.
        (SELECT DISTINCT card, deck_id FROM deck_card) AS dc ON d.id = dc.deck_id

    LEFT JOIN
        competition AS c ON d.competition_id = c.id
    LEFT JOIN
        competition_series AS cs ON cs.id = c.competition_series_id
    LEFT JOIN
        competition_type AS ct ON ct.id = cs.competition_type_id

    LEFT JOIN deck_cache AS season ON d.id = season.deck_id

    LEFT JOIN
        (
            SELECT
                d.id,
                d.created_date,
                d.finish,
                SUM(CASE WHEN dm.games > IFNULL(odm.games, 0) THEN 1 ELSE 0 END) AS wins, -- IFNULL so we still count byes as wins.
                SUM(CASE WHEN dm.games < odm.games THEN 1 ELSE 0 END) AS losses,
                SUM(CASE WHEN dm.games = odm.games THEN 1 ELSE 0 END) AS draws
            FROM
                deck_match AS dm
            INNER JOIN
                deck_match AS odm ON dm.match_id = odm.match_id AND dm.deck_id <> odm.deck_id
            INNER JOIN
                deck AS d ON d.id = dm.deck_id
            GROUP BY
                d.id
        ) AS dsum ON d.id = dsum.id

    GROUP BY
        card,
        season.season_id,
        ct.name
```

[] (slow_query, 959.6, mysql)

Reported on decksite by mysql-perf

Location Hash: 7c1b78e351d987fc4af4134cd398b330cb417fbe

Labels: decksite

vorpal-buildbot commented 2 years ago

Exceeded slow_query limit (1101.9 > 60.0) in mysql: ```

    CREATE TABLE IF NOT EXISTS _new_card_stats (
        name VARCHAR(190) NOT NULL,
        season_id INT NOT NULL,
        num_decks INT NOT NULL,
        wins INT NOT NULL,
        losses INT NOT NULL,
        draws INT NOT NULL,
        perfect_runs INT NOT NULL,
        tournament_wins INT NOT NULL,
        tournament_top8s INT NOT NULL,
        deck_type ENUM('league', 'tournament', 'other') NOT NULL,
        PRIMARY KEY (season_id, name, deck_type),
        FOREIGN KEY (season_id) REFERENCES season (id) ON UPDATE CASCADE ON DELETE CASCADE
    ) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci AS
    SELECT
        card AS name,
        season.season_id,
        SUM(CASE WHEN d.id IS NOT NULL THEN 1 ELSE 0 END) AS num_decks,
        IFNULL(SUM(dsum.wins), 0) AS wins,
        IFNULL(SUM(dsum.losses), 0) AS losses,
        IFNULL(SUM(dsum.draws), 0) AS draws,
        SUM(CASE WHEN dsum.wins >= 5 AND dsum.losses = 0 AND d.source_id IN (SELECT id FROM source WHERE name = 'League') THEN 1 ELSE 0 END) AS perfect_runs,
        SUM(CASE WHEN dsum.finish = 1 THEN 1 ELSE 0 END) AS tournament_wins,
        SUM(CASE WHEN dsum.finish <= 8 THEN 1 ELSE 0 END) AS tournament_top8s,
        (CASE WHEN ct.name = 'League' THEN 'league' WHEN ct.name = 'Gatherling' THEN 'tournament' ELSE 'other' END) AS deck_type
    FROM
        deck AS d
    INNER JOIN
        -- Eiliminate maindeck/sideboard double-counting with DISTINCT. See #5493.
        (SELECT DISTINCT card, deck_id FROM deck_card) AS dc ON d.id = dc.deck_id

    LEFT JOIN
        competition AS c ON d.competition_id = c.id
    LEFT JOIN
        competition_series AS cs ON cs.id = c.competition_series_id
    LEFT JOIN
        competition_type AS ct ON ct.id = cs.competition_type_id

    LEFT JOIN deck_cache AS season ON d.id = season.deck_id

    LEFT JOIN
        (
            SELECT
                d.id,
                d.created_date,
                d.finish,
                SUM(CASE WHEN dm.games > IFNULL(odm.games, 0) THEN 1 ELSE 0 END) AS wins, -- IFNULL so we still count byes as wins.
                SUM(CASE WHEN dm.games < odm.games THEN 1 ELSE 0 END) AS losses,
                SUM(CASE WHEN dm.games = odm.games THEN 1 ELSE 0 END) AS draws
            FROM
                deck_match AS dm
            INNER JOIN
                deck_match AS odm ON dm.match_id = odm.match_id AND dm.deck_id <> odm.deck_id
            INNER JOIN
                deck AS d ON d.id = dm.deck_id
            GROUP BY
                d.id
        ) AS dsum ON d.id = dsum.id

    GROUP BY
        card,
        season.season_id,
        ct.name
```

[] (slow_query, 1101.9, mysql)

Reported on decksite by mysql-perf

Location Hash: 7c1b78e351d987fc4af4134cd398b330cb417fbe

Labels: decksite

vorpal-buildbot commented 2 years ago

Exceeded slow_query limit (1302.5 > 60.0) in mysql: ```

    CREATE TABLE IF NOT EXISTS _new_card_stats (
        name VARCHAR(190) NOT NULL,
        season_id INT NOT NULL,
        num_decks INT NOT NULL,
        wins INT NOT NULL,
        losses INT NOT NULL,
        draws INT NOT NULL,
        perfect_runs INT NOT NULL,
        tournament_wins INT NOT NULL,
        tournament_top8s INT NOT NULL,
        deck_type ENUM('league', 'tournament', 'other') NOT NULL,
        PRIMARY KEY (season_id, name, deck_type),
        FOREIGN KEY (season_id) REFERENCES season (id) ON UPDATE CASCADE ON DELETE CASCADE
    ) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci AS
    SELECT
        card AS name,
        season.season_id,
        SUM(CASE WHEN d.id IS NOT NULL THEN 1 ELSE 0 END) AS num_decks,
        IFNULL(SUM(dsum.wins), 0) AS wins,
        IFNULL(SUM(dsum.losses), 0) AS losses,
        IFNULL(SUM(dsum.draws), 0) AS draws,
        SUM(CASE WHEN dsum.wins >= 5 AND dsum.losses = 0 AND d.source_id IN (SELECT id FROM source WHERE name = 'League') THEN 1 ELSE 0 END) AS perfect_runs,
        SUM(CASE WHEN dsum.finish = 1 THEN 1 ELSE 0 END) AS tournament_wins,
        SUM(CASE WHEN dsum.finish <= 8 THEN 1 ELSE 0 END) AS tournament_top8s,
        (CASE WHEN ct.name = 'League' THEN 'league' WHEN ct.name = 'Gatherling' THEN 'tournament' ELSE 'other' END) AS deck_type
    FROM
        deck AS d
    INNER JOIN
        -- Eiliminate maindeck/sideboard double-counting with DISTINCT. See #5493.
        (SELECT DISTINCT card, deck_id FROM deck_card) AS dc ON d.id = dc.deck_id

    LEFT JOIN
        competition AS c ON d.competition_id = c.id
    LEFT JOIN
        competition_series AS cs ON cs.id = c.competition_series_id
    LEFT JOIN
        competition_type AS ct ON ct.id = cs.competition_type_id

    LEFT JOIN deck_cache AS season ON d.id = season.deck_id

    LEFT JOIN
        (
            SELECT
                d.id,
                d.created_date,
                d.finish,
                SUM(CASE WHEN dm.games > IFNULL(odm.games, 0) THEN 1 ELSE 0 END) AS wins, -- IFNULL so we still count byes as wins.
                SUM(CASE WHEN dm.games < odm.games THEN 1 ELSE 0 END) AS losses,
                SUM(CASE WHEN dm.games = odm.games THEN 1 ELSE 0 END) AS draws
            FROM
                deck_match AS dm
            INNER JOIN
                deck_match AS odm ON dm.match_id = odm.match_id AND dm.deck_id <> odm.deck_id
            INNER JOIN
                deck AS d ON d.id = dm.deck_id
            GROUP BY
                d.id
        ) AS dsum ON d.id = dsum.id

    GROUP BY
        card,
        season.season_id,
        ct.name
```

[] (slow_query, 1302.5, mysql)

Reported on decksite by mysql-perf

Location Hash: 7c1b78e351d987fc4af4134cd398b330cb417fbe

Labels: decksite

vorpal-buildbot commented 2 years ago

Exceeded slow_query limit (1407.7 > 60.0) in mysql: ```

    CREATE TABLE IF NOT EXISTS _new_card_stats (
        name VARCHAR(190) NOT NULL,
        season_id INT NOT NULL,
        num_decks INT NOT NULL,
        wins INT NOT NULL,
        losses INT NOT NULL,
        draws INT NOT NULL,
        perfect_runs INT NOT NULL,
        tournament_wins INT NOT NULL,
        tournament_top8s INT NOT NULL,
        deck_type ENUM('league', 'tournament', 'other') NOT NULL,
        PRIMARY KEY (season_id, name, deck_type),
        FOREIGN KEY (season_id) REFERENCES season (id) ON UPDATE CASCADE ON DELETE CASCADE
    ) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci AS
    SELECT
        card AS name,
        season.season_id,
        SUM(CASE WHEN d.id IS NOT NULL THEN 1 ELSE 0 END) AS num_decks,
        IFNULL(SUM(dsum.wins), 0) AS wins,
        IFNULL(SUM(dsum.losses), 0) AS losses,
        IFNULL(SUM(dsum.draws), 0) AS draws,
        SUM(CASE WHEN dsum.wins >= 5 AND dsum.losses = 0 AND d.source_id IN (SELECT id FROM source WHERE name = 'League') THEN 1 ELSE 0 END) AS perfect_runs,
        SUM(CASE WHEN dsum.finish = 1 THEN 1 ELSE 0 END) AS tournament_wins,
        SUM(CASE WHEN dsum.finish <= 8 THEN 1 ELSE 0 END) AS tournament_top8s,
        (CASE WHEN ct.name = 'League' THEN 'league' WHEN ct.name = 'Gatherling' THEN 'tournament' ELSE 'other' END) AS deck_type
    FROM
        deck AS d
    INNER JOIN
        -- Eiliminate maindeck/sideboard double-counting with DISTINCT. See #5493.
        (SELECT DISTINCT card, deck_id FROM deck_card) AS dc ON d.id = dc.deck_id

    LEFT JOIN
        competition AS c ON d.competition_id = c.id
    LEFT JOIN
        competition_series AS cs ON cs.id = c.competition_series_id
    LEFT JOIN
        competition_type AS ct ON ct.id = cs.competition_type_id

    LEFT JOIN deck_cache AS season ON d.id = season.deck_id

    LEFT JOIN
        (
            SELECT
                d.id,
                d.created_date,
                d.finish,
                SUM(CASE WHEN dm.games > IFNULL(odm.games, 0) THEN 1 ELSE 0 END) AS wins, -- IFNULL so we still count byes as wins.
                SUM(CASE WHEN dm.games < odm.games THEN 1 ELSE 0 END) AS losses,
                SUM(CASE WHEN dm.games = odm.games THEN 1 ELSE 0 END) AS draws
            FROM
                deck_match AS dm
            INNER JOIN
                deck_match AS odm ON dm.match_id = odm.match_id AND dm.deck_id <> odm.deck_id
            INNER JOIN
                deck AS d ON d.id = dm.deck_id
            GROUP BY
                d.id
        ) AS dsum ON d.id = dsum.id

    GROUP BY
        card,
        season.season_id,
        ct.name
```

[] (slow_query, 1407.7, mysql)

Reported on decksite by mysql-perf

Location Hash: 7c1b78e351d987fc4af4134cd398b330cb417fbe

Labels: decksite

vorpal-buildbot commented 2 years ago

Exceeded slow_query limit (925.4 > 60.0) in mysql: ```

    CREATE TABLE IF NOT EXISTS _new_card_stats (
        name VARCHAR(190) NOT NULL,
        season_id INT NOT NULL,
        num_decks INT NOT NULL,
        wins INT NOT NULL,
        losses INT NOT NULL,
        draws INT NOT NULL,
        perfect_runs INT NOT NULL,
        tournament_wins INT NOT NULL,
        tournament_top8s INT NOT NULL,
        deck_type ENUM('league', 'tournament', 'other') NOT NULL,
        PRIMARY KEY (season_id, name, deck_type),
        FOREIGN KEY (season_id) REFERENCES season (id) ON UPDATE CASCADE ON DELETE CASCADE
    ) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci AS
    SELECT
        card AS name,
        season.season_id,
        SUM(CASE WHEN d.id IS NOT NULL THEN 1 ELSE 0 END) AS num_decks,
        IFNULL(SUM(dsum.wins), 0) AS wins,
        IFNULL(SUM(dsum.losses), 0) AS losses,
        IFNULL(SUM(dsum.draws), 0) AS draws,
        SUM(CASE WHEN dsum.wins >= 5 AND dsum.losses = 0 AND d.source_id IN (SELECT id FROM source WHERE name = 'League') THEN 1 ELSE 0 END) AS perfect_runs,
        SUM(CASE WHEN dsum.finish = 1 THEN 1 ELSE 0 END) AS tournament_wins,
        SUM(CASE WHEN dsum.finish <= 8 THEN 1 ELSE 0 END) AS tournament_top8s,
        (CASE WHEN ct.name = 'League' THEN 'league' WHEN ct.name = 'Gatherling' THEN 'tournament' ELSE 'other' END) AS deck_type
    FROM
        deck AS d
    INNER JOIN
        -- Eiliminate maindeck/sideboard double-counting with DISTINCT. See #5493.
        (SELECT DISTINCT card, deck_id FROM deck_card) AS dc ON d.id = dc.deck_id

    LEFT JOIN
        competition AS c ON d.competition_id = c.id
    LEFT JOIN
        competition_series AS cs ON cs.id = c.competition_series_id
    LEFT JOIN
        competition_type AS ct ON ct.id = cs.competition_type_id

    LEFT JOIN deck_cache AS season ON d.id = season.deck_id

    LEFT JOIN
        (
            SELECT
                d.id,
                d.created_date,
                d.finish,
                SUM(CASE WHEN dm.games > IFNULL(odm.games, 0) THEN 1 ELSE 0 END) AS wins, -- IFNULL so we still count byes as wins.
                SUM(CASE WHEN dm.games < odm.games THEN 1 ELSE 0 END) AS losses,
                SUM(CASE WHEN dm.games = odm.games THEN 1 ELSE 0 END) AS draws
            FROM
                deck_match AS dm
            INNER JOIN
                deck_match AS odm ON dm.match_id = odm.match_id AND dm.deck_id <> odm.deck_id
            INNER JOIN
                deck AS d ON d.id = dm.deck_id
            GROUP BY
                d.id
        ) AS dsum ON d.id = dsum.id

    GROUP BY
        card,
        season.season_id,
        ct.name
```

[] (slow_query, 925.4, mysql)

Reported on decksite by mysql-perf

Location Hash: 7c1b78e351d987fc4af4134cd398b330cb417fbe

Labels: decksite

vorpal-buildbot commented 2 years ago

Exceeded slow_query limit (1078.4 > 60.0) in mysql: ```

    CREATE TABLE IF NOT EXISTS _new_card_stats (
        name VARCHAR(190) NOT NULL,
        season_id INT NOT NULL,
        num_decks INT NOT NULL,
        wins INT NOT NULL,
        losses INT NOT NULL,
        draws INT NOT NULL,
        perfect_runs INT NOT NULL,
        tournament_wins INT NOT NULL,
        tournament_top8s INT NOT NULL,
        deck_type ENUM('league', 'tournament', 'other') NOT NULL,
        PRIMARY KEY (season_id, name, deck_type),
        FOREIGN KEY (season_id) REFERENCES season (id) ON UPDATE CASCADE ON DELETE CASCADE
    ) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci AS
    SELECT
        card AS name,
        season.season_id,
        SUM(CASE WHEN d.id IS NOT NULL THEN 1 ELSE 0 END) AS num_decks,
        IFNULL(SUM(dsum.wins), 0) AS wins,
        IFNULL(SUM(dsum.losses), 0) AS losses,
        IFNULL(SUM(dsum.draws), 0) AS draws,
        SUM(CASE WHEN dsum.wins >= 5 AND dsum.losses = 0 AND d.source_id IN (SELECT id FROM source WHERE name = 'League') THEN 1 ELSE 0 END) AS perfect_runs,
        SUM(CASE WHEN dsum.finish = 1 THEN 1 ELSE 0 END) AS tournament_wins,
        SUM(CASE WHEN dsum.finish <= 8 THEN 1 ELSE 0 END) AS tournament_top8s,
        (CASE WHEN ct.name = 'League' THEN 'league' WHEN ct.name = 'Gatherling' THEN 'tournament' ELSE 'other' END) AS deck_type
    FROM
        deck AS d
    INNER JOIN
        -- Eiliminate maindeck/sideboard double-counting with DISTINCT. See #5493.
        (SELECT DISTINCT card, deck_id FROM deck_card) AS dc ON d.id = dc.deck_id

    LEFT JOIN
        competition AS c ON d.competition_id = c.id
    LEFT JOIN
        competition_series AS cs ON cs.id = c.competition_series_id
    LEFT JOIN
        competition_type AS ct ON ct.id = cs.competition_type_id

    LEFT JOIN deck_cache AS season ON d.id = season.deck_id

    LEFT JOIN
        (
            SELECT
                d.id,
                d.created_date,
                d.finish,
                SUM(CASE WHEN dm.games > IFNULL(odm.games, 0) THEN 1 ELSE 0 END) AS wins, -- IFNULL so we still count byes as wins.
                SUM(CASE WHEN dm.games < odm.games THEN 1 ELSE 0 END) AS losses,
                SUM(CASE WHEN dm.games = odm.games THEN 1 ELSE 0 END) AS draws
            FROM
                deck_match AS dm
            INNER JOIN
                deck_match AS odm ON dm.match_id = odm.match_id AND dm.deck_id <> odm.deck_id
            INNER JOIN
                deck AS d ON d.id = dm.deck_id
            GROUP BY
                d.id
        ) AS dsum ON d.id = dsum.id

    GROUP BY
        card,
        season.season_id,
        ct.name
```

[] (slow_query, 1078.4, mysql)

Reported on decksite by mysql-perf

Location Hash: 7c1b78e351d987fc4af4134cd398b330cb417fbe

Labels: decksite

vorpal-buildbot commented 2 years ago

Exceeded slow_query limit (1012.2 > 60.0) in mysql: ```

    CREATE TABLE IF NOT EXISTS _new_card_stats (
        name VARCHAR(190) NOT NULL,
        season_id INT NOT NULL,
        num_decks INT NOT NULL,
        wins INT NOT NULL,
        losses INT NOT NULL,
        draws INT NOT NULL,
        perfect_runs INT NOT NULL,
        tournament_wins INT NOT NULL,
        tournament_top8s INT NOT NULL,
        deck_type ENUM('league', 'tournament', 'other') NOT NULL,
        PRIMARY KEY (season_id, name, deck_type),
        FOREIGN KEY (season_id) REFERENCES season (id) ON UPDATE CASCADE ON DELETE CASCADE
    ) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci AS
    SELECT
        card AS name,
        season.season_id,
        SUM(CASE WHEN d.id IS NOT NULL THEN 1 ELSE 0 END) AS num_decks,
        IFNULL(SUM(dsum.wins), 0) AS wins,
        IFNULL(SUM(dsum.losses), 0) AS losses,
        IFNULL(SUM(dsum.draws), 0) AS draws,
        SUM(CASE WHEN dsum.wins >= 5 AND dsum.losses = 0 AND d.source_id IN (SELECT id FROM source WHERE name = 'League') THEN 1 ELSE 0 END) AS perfect_runs,
        SUM(CASE WHEN dsum.finish = 1 THEN 1 ELSE 0 END) AS tournament_wins,
        SUM(CASE WHEN dsum.finish <= 8 THEN 1 ELSE 0 END) AS tournament_top8s,
        (CASE WHEN ct.name = 'League' THEN 'league' WHEN ct.name = 'Gatherling' THEN 'tournament' ELSE 'other' END) AS deck_type
    FROM
        deck AS d
    INNER JOIN
        -- Eiliminate maindeck/sideboard double-counting with DISTINCT. See #5493.
        (SELECT DISTINCT card, deck_id FROM deck_card) AS dc ON d.id = dc.deck_id

    LEFT JOIN
        competition AS c ON d.competition_id = c.id
    LEFT JOIN
        competition_series AS cs ON cs.id = c.competition_series_id
    LEFT JOIN
        competition_type AS ct ON ct.id = cs.competition_type_id

    LEFT JOIN deck_cache AS season ON d.id = season.deck_id

    LEFT JOIN
        (
            SELECT
                d.id,
                d.created_date,
                d.finish,
                SUM(CASE WHEN dm.games > IFNULL(odm.games, 0) THEN 1 ELSE 0 END) AS wins, -- IFNULL so we still count byes as wins.
                SUM(CASE WHEN dm.games < odm.games THEN 1 ELSE 0 END) AS losses,
                SUM(CASE WHEN dm.games = odm.games THEN 1 ELSE 0 END) AS draws
            FROM
                deck_match AS dm
            INNER JOIN
                deck_match AS odm ON dm.match_id = odm.match_id AND dm.deck_id <> odm.deck_id
            INNER JOIN
                deck AS d ON d.id = dm.deck_id
            GROUP BY
                d.id
        ) AS dsum ON d.id = dsum.id

    GROUP BY
        card,
        season.season_id,
        ct.name
```

[] (slow_query, 1012.2, mysql)

Reported on decksite by mysql-perf

Location Hash: 7c1b78e351d987fc4af4134cd398b330cb417fbe

Labels: decksite

vorpal-buildbot commented 1 year ago

Exceeded slow_query limit (1078.5 > 60.0) in mysql: ```

    CREATE TABLE IF NOT EXISTS _new_card_stats (
        name VARCHAR(190) NOT NULL,
        season_id INT NOT NULL,
        num_decks INT NOT NULL,
        wins INT NOT NULL,
        losses INT NOT NULL,
        draws INT NOT NULL,
        perfect_runs INT NOT NULL,
        tournament_wins INT NOT NULL,
        tournament_top8s INT NOT NULL,
        deck_type ENUM('league', 'tournament', 'other') NOT NULL,
        PRIMARY KEY (season_id, name, deck_type),
        FOREIGN KEY (season_id) REFERENCES season (id) ON UPDATE CASCADE ON DELETE CASCADE
    ) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci AS
    SELECT
        card AS name,
        season.season_id,
        SUM(CASE WHEN d.id IS NOT NULL THEN 1 ELSE 0 END) AS num_decks,
        IFNULL(SUM(dsum.wins), 0) AS wins,
        IFNULL(SUM(dsum.losses), 0) AS losses,
        IFNULL(SUM(dsum.draws), 0) AS draws,
        SUM(CASE WHEN dsum.wins >= 5 AND dsum.losses = 0 AND d.source_id IN (SELECT id FROM source WHERE name = 'League') THEN 1 ELSE 0 END) AS perfect_runs,
        SUM(CASE WHEN dsum.finish = 1 THEN 1 ELSE 0 END) AS tournament_wins,
        SUM(CASE WHEN dsum.finish <= 8 THEN 1 ELSE 0 END) AS tournament_top8s,
        (CASE WHEN ct.name = 'League' THEN 'league' WHEN ct.name = 'Gatherling' THEN 'tournament' ELSE 'other' END) AS deck_type
    FROM
        deck AS d
    INNER JOIN
        -- Eiliminate maindeck/sideboard double-counting with DISTINCT. See #5493.
        (SELECT DISTINCT card, deck_id FROM deck_card) AS dc ON d.id = dc.deck_id

    LEFT JOIN
        competition AS c ON d.competition_id = c.id
    LEFT JOIN
        competition_series AS cs ON cs.id = c.competition_series_id
    LEFT JOIN
        competition_type AS ct ON ct.id = cs.competition_type_id

    LEFT JOIN deck_cache AS season ON d.id = season.deck_id

    LEFT JOIN
        (
            SELECT
                d.id,
                d.created_date,
                d.finish,
                SUM(CASE WHEN dm.games > IFNULL(odm.games, 0) THEN 1 ELSE 0 END) AS wins, -- IFNULL so we still count byes as wins.
                SUM(CASE WHEN dm.games < odm.games THEN 1 ELSE 0 END) AS losses,
                SUM(CASE WHEN dm.games = odm.games THEN 1 ELSE 0 END) AS draws
            FROM
                deck_match AS dm
            INNER JOIN
                deck_match AS odm ON dm.match_id = odm.match_id AND dm.deck_id <> odm.deck_id
            INNER JOIN
                deck AS d ON d.id = dm.deck_id
            GROUP BY
                d.id
        ) AS dsum ON d.id = dsum.id

    GROUP BY
        card,
        season.season_id,
        ct.name
```

[] (slow_query, 1078.5, mysql)

Reported on decksite by mysql-perf

Location Hash: 7c1b78e351d987fc4af4134cd398b330cb417fbe

Labels: decksite

vorpal-buildbot commented 1 year ago

Exceeded slow_query limit (1389.9 > 60.0) in mysql: ```

    CREATE TABLE IF NOT EXISTS _new_card_stats (
        name VARCHAR(190) NOT NULL,
        season_id INT NOT NULL,
        num_decks INT NOT NULL,
        wins INT NOT NULL,
        losses INT NOT NULL,
        draws INT NOT NULL,
        perfect_runs INT NOT NULL,
        tournament_wins INT NOT NULL,
        tournament_top8s INT NOT NULL,
        deck_type ENUM('league', 'tournament', 'other') NOT NULL,
        PRIMARY KEY (season_id, name, deck_type),
        FOREIGN KEY (season_id) REFERENCES season (id) ON UPDATE CASCADE ON DELETE CASCADE
    ) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci AS
    SELECT
        card AS name,
        season.season_id,
        SUM(CASE WHEN d.id IS NOT NULL THEN 1 ELSE 0 END) AS num_decks,
        IFNULL(SUM(dsum.wins), 0) AS wins,
        IFNULL(SUM(dsum.losses), 0) AS losses,
        IFNULL(SUM(dsum.draws), 0) AS draws,
        SUM(CASE WHEN dsum.wins >= 5 AND dsum.losses = 0 AND d.source_id IN (SELECT id FROM source WHERE name = 'League') THEN 1 ELSE 0 END) AS perfect_runs,
        SUM(CASE WHEN dsum.finish = 1 THEN 1 ELSE 0 END) AS tournament_wins,
        SUM(CASE WHEN dsum.finish <= 8 THEN 1 ELSE 0 END) AS tournament_top8s,
        (CASE WHEN ct.name = 'League' THEN 'league' WHEN ct.name = 'Gatherling' THEN 'tournament' ELSE 'other' END) AS deck_type
    FROM
        deck AS d
    INNER JOIN
        -- Eiliminate maindeck/sideboard double-counting with DISTINCT. See #5493.
        (SELECT DISTINCT card, deck_id FROM deck_card) AS dc ON d.id = dc.deck_id

    LEFT JOIN
        competition AS c ON d.competition_id = c.id
    LEFT JOIN
        competition_series AS cs ON cs.id = c.competition_series_id
    LEFT JOIN
        competition_type AS ct ON ct.id = cs.competition_type_id

    LEFT JOIN deck_cache AS season ON d.id = season.deck_id

    LEFT JOIN
        (
            SELECT
                d.id,
                d.created_date,
                d.finish,
                SUM(CASE WHEN dm.games > IFNULL(odm.games, 0) THEN 1 ELSE 0 END) AS wins, -- IFNULL so we still count byes as wins.
                SUM(CASE WHEN dm.games < odm.games THEN 1 ELSE 0 END) AS losses,
                SUM(CASE WHEN dm.games = odm.games THEN 1 ELSE 0 END) AS draws
            FROM
                deck_match AS dm
            INNER JOIN
                deck_match AS odm ON dm.match_id = odm.match_id AND dm.deck_id <> odm.deck_id
            INNER JOIN
                deck AS d ON d.id = dm.deck_id
            GROUP BY
                d.id
        ) AS dsum ON d.id = dsum.id

    GROUP BY
        card,
        season.season_id,
        ct.name
```

[] (slow_query, 1389.9, mysql)

Reported on decksite by mysql-perf

Location Hash: 7c1b78e351d987fc4af4134cd398b330cb417fbe

Labels: decksite

vorpal-buildbot commented 1 year ago

Exceeded slow_query limit (1080.4 > 60.0) in mysql: ```

    CREATE TABLE IF NOT EXISTS _new_card_stats (
        name VARCHAR(190) NOT NULL,
        season_id INT NOT NULL,
        num_decks INT NOT NULL,
        wins INT NOT NULL,
        losses INT NOT NULL,
        draws INT NOT NULL,
        perfect_runs INT NOT NULL,
        tournament_wins INT NOT NULL,
        tournament_top8s INT NOT NULL,
        deck_type ENUM('league', 'tournament', 'other') NOT NULL,
        PRIMARY KEY (season_id, name, deck_type),
        FOREIGN KEY (season_id) REFERENCES season (id) ON UPDATE CASCADE ON DELETE CASCADE
    ) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci AS
    SELECT
        card AS name,
        season.season_id,
        SUM(CASE WHEN d.id IS NOT NULL THEN 1 ELSE 0 END) AS num_decks,
        IFNULL(SUM(dsum.wins), 0) AS wins,
        IFNULL(SUM(dsum.losses), 0) AS losses,
        IFNULL(SUM(dsum.draws), 0) AS draws,
        SUM(CASE WHEN dsum.wins >= 5 AND dsum.losses = 0 AND d.source_id IN (SELECT id FROM source WHERE name = 'League') THEN 1 ELSE 0 END) AS perfect_runs,
        SUM(CASE WHEN dsum.finish = 1 THEN 1 ELSE 0 END) AS tournament_wins,
        SUM(CASE WHEN dsum.finish <= 8 THEN 1 ELSE 0 END) AS tournament_top8s,
        (CASE WHEN ct.name = 'League' THEN 'league' WHEN ct.name = 'Gatherling' THEN 'tournament' ELSE 'other' END) AS deck_type
    FROM
        deck AS d
    INNER JOIN
        -- Eiliminate maindeck/sideboard double-counting with DISTINCT. See #5493.
        (SELECT DISTINCT card, deck_id FROM deck_card) AS dc ON d.id = dc.deck_id

    LEFT JOIN
        competition AS c ON d.competition_id = c.id
    LEFT JOIN
        competition_series AS cs ON cs.id = c.competition_series_id
    LEFT JOIN
        competition_type AS ct ON ct.id = cs.competition_type_id

    LEFT JOIN deck_cache AS season ON d.id = season.deck_id

    LEFT JOIN
        (
            SELECT
                d.id,
                d.created_date,
                d.finish,
                SUM(CASE WHEN dm.games > IFNULL(odm.games, 0) THEN 1 ELSE 0 END) AS wins, -- IFNULL so we still count byes as wins.
                SUM(CASE WHEN dm.games < odm.games THEN 1 ELSE 0 END) AS losses,
                SUM(CASE WHEN dm.games = odm.games THEN 1 ELSE 0 END) AS draws
            FROM
                deck_match AS dm
            INNER JOIN
                deck_match AS odm ON dm.match_id = odm.match_id AND dm.deck_id <> odm.deck_id
            INNER JOIN
                deck AS d ON d.id = dm.deck_id
            GROUP BY
                d.id
        ) AS dsum ON d.id = dsum.id

    GROUP BY
        card,
        season.season_id,
        ct.name
```

[] (slow_query, 1080.4, mysql)

Reported on decksite by mysql-perf

Location Hash: 7c1b78e351d987fc4af4134cd398b330cb417fbe

Labels: decksite

vorpal-buildbot commented 1 year ago

Exceeded slow_query limit (969.4 > 60.0) in mysql: ```

    CREATE TABLE IF NOT EXISTS _new_card_stats (
        name VARCHAR(190) NOT NULL,
        season_id INT NOT NULL,
        num_decks INT NOT NULL,
        wins INT NOT NULL,
        losses INT NOT NULL,
        draws INT NOT NULL,
        perfect_runs INT NOT NULL,
        tournament_wins INT NOT NULL,
        tournament_top8s INT NOT NULL,
        deck_type ENUM('league', 'tournament', 'other') NOT NULL,
        PRIMARY KEY (season_id, name, deck_type),
        FOREIGN KEY (season_id) REFERENCES season (id) ON UPDATE CASCADE ON DELETE CASCADE
    ) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci AS
    SELECT
        card AS name,
        season.season_id,
        SUM(CASE WHEN d.id IS NOT NULL THEN 1 ELSE 0 END) AS num_decks,
        IFNULL(SUM(dsum.wins), 0) AS wins,
        IFNULL(SUM(dsum.losses), 0) AS losses,
        IFNULL(SUM(dsum.draws), 0) AS draws,
        SUM(CASE WHEN dsum.wins >= 5 AND dsum.losses = 0 AND d.source_id IN (SELECT id FROM source WHERE name = 'League') THEN 1 ELSE 0 END) AS perfect_runs,
        SUM(CASE WHEN dsum.finish = 1 THEN 1 ELSE 0 END) AS tournament_wins,
        SUM(CASE WHEN dsum.finish <= 8 THEN 1 ELSE 0 END) AS tournament_top8s,
        (CASE WHEN ct.name = 'League' THEN 'league' WHEN ct.name = 'Gatherling' THEN 'tournament' ELSE 'other' END) AS deck_type
    FROM
        deck AS d
    INNER JOIN
        -- Eiliminate maindeck/sideboard double-counting with DISTINCT. See #5493.
        (SELECT DISTINCT card, deck_id FROM deck_card) AS dc ON d.id = dc.deck_id

    LEFT JOIN
        competition AS c ON d.competition_id = c.id
    LEFT JOIN
        competition_series AS cs ON cs.id = c.competition_series_id
    LEFT JOIN
        competition_type AS ct ON ct.id = cs.competition_type_id

    LEFT JOIN deck_cache AS season ON d.id = season.deck_id

    LEFT JOIN
        (
            SELECT
                d.id,
                d.created_date,
                d.finish,
                SUM(CASE WHEN dm.games > IFNULL(odm.games, 0) THEN 1 ELSE 0 END) AS wins, -- IFNULL so we still count byes as wins.
                SUM(CASE WHEN dm.games < odm.games THEN 1 ELSE 0 END) AS losses,
                SUM(CASE WHEN dm.games = odm.games THEN 1 ELSE 0 END) AS draws
            FROM
                deck_match AS dm
            INNER JOIN
                deck_match AS odm ON dm.match_id = odm.match_id AND dm.deck_id <> odm.deck_id
            INNER JOIN
                deck AS d ON d.id = dm.deck_id
            GROUP BY
                d.id
        ) AS dsum ON d.id = dsum.id

    GROUP BY
        card,
        season.season_id,
        ct.name
```

[] (slow_query, 969.4, mysql)

Reported on decksite by mysql-perf

Location Hash: 7c1b78e351d987fc4af4134cd398b330cb417fbe

Labels: decksite

vorpal-buildbot commented 1 year ago

Exceeded slow_query limit (834.0 > 60.0) in mysql: ```

    CREATE TABLE IF NOT EXISTS _new_card_stats (
        name VARCHAR(190) NOT NULL,
        season_id INT NOT NULL,
        num_decks INT NOT NULL,
        wins INT NOT NULL,
        losses INT NOT NULL,
        draws INT NOT NULL,
        perfect_runs INT NOT NULL,
        tournament_wins INT NOT NULL,
        tournament_top8s INT NOT NULL,
        deck_type ENUM('league', 'tournament', 'other') NOT NULL,
        PRIMARY KEY (season_id, name, deck_type),
        FOREIGN KEY (season_id) REFERENCES season (id) ON UPDATE CASCADE ON DELETE CASCADE
    ) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci AS
    SELECT
        card AS name,
        season.season_id,
        SUM(CASE WHEN d.id IS NOT NULL THEN 1 ELSE 0 END) AS num_decks,
        IFNULL(SUM(dsum.wins), 0) AS wins,
        IFNULL(SUM(dsum.losses), 0) AS losses,
        IFNULL(SUM(dsum.draws), 0) AS draws,
        SUM(CASE WHEN dsum.wins >= 5 AND dsum.losses = 0 AND d.source_id IN (SELECT id FROM source WHERE name = 'League') THEN 1 ELSE 0 END) AS perfect_runs,
        SUM(CASE WHEN dsum.finish = 1 THEN 1 ELSE 0 END) AS tournament_wins,
        SUM(CASE WHEN dsum.finish <= 8 THEN 1 ELSE 0 END) AS tournament_top8s,
        (CASE WHEN ct.name = 'League' THEN 'league' WHEN ct.name = 'Gatherling' THEN 'tournament' ELSE 'other' END) AS deck_type
    FROM
        deck AS d
    INNER JOIN
        -- Eiliminate maindeck/sideboard double-counting with DISTINCT. See #5493.
        (SELECT DISTINCT card, deck_id FROM deck_card) AS dc ON d.id = dc.deck_id

    LEFT JOIN
        competition AS c ON d.competition_id = c.id
    LEFT JOIN
        competition_series AS cs ON cs.id = c.competition_series_id
    LEFT JOIN
        competition_type AS ct ON ct.id = cs.competition_type_id

    LEFT JOIN deck_cache AS season ON d.id = season.deck_id

    LEFT JOIN
        (
            SELECT
                d.id,
                d.created_date,
                d.finish,
                SUM(CASE WHEN dm.games > IFNULL(odm.games, 0) THEN 1 ELSE 0 END) AS wins, -- IFNULL so we still count byes as wins.
                SUM(CASE WHEN dm.games < odm.games THEN 1 ELSE 0 END) AS losses,
                SUM(CASE WHEN dm.games = odm.games THEN 1 ELSE 0 END) AS draws
            FROM
                deck_match AS dm
            INNER JOIN
                deck_match AS odm ON dm.match_id = odm.match_id AND dm.deck_id <> odm.deck_id
            INNER JOIN
                deck AS d ON d.id = dm.deck_id
            GROUP BY
                d.id
        ) AS dsum ON d.id = dsum.id

    GROUP BY
        card,
        season.season_id,
        ct.name
```

[] (slow_query, 834.0, mysql)

Reported on decksite by mysql-perf

Location Hash: 7c1b78e351d987fc4af4134cd398b330cb417fbe

Labels: decksite

vorpal-buildbot commented 1 year ago

Exceeded slow_query limit (958.7 > 60.0) in mysql: ```

    CREATE TABLE IF NOT EXISTS _new_card_stats (
        name VARCHAR(190) NOT NULL,
        season_id INT NOT NULL,
        num_decks INT NOT NULL,
        wins INT NOT NULL,
        losses INT NOT NULL,
        draws INT NOT NULL,
        perfect_runs INT NOT NULL,
        tournament_wins INT NOT NULL,
        tournament_top8s INT NOT NULL,
        deck_type ENUM('league', 'tournament', 'other') NOT NULL,
        PRIMARY KEY (season_id, name, deck_type),
        FOREIGN KEY (season_id) REFERENCES season (id) ON UPDATE CASCADE ON DELETE CASCADE
    ) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci AS
    SELECT
        card AS name,
        season.season_id,
        SUM(CASE WHEN d.id IS NOT NULL THEN 1 ELSE 0 END) AS num_decks,
        IFNULL(SUM(dsum.wins), 0) AS wins,
        IFNULL(SUM(dsum.losses), 0) AS losses,
        IFNULL(SUM(dsum.draws), 0) AS draws,
        SUM(CASE WHEN dsum.wins >= 5 AND dsum.losses = 0 AND d.source_id IN (SELECT id FROM source WHERE name = 'League') THEN 1 ELSE 0 END) AS perfect_runs,
        SUM(CASE WHEN dsum.finish = 1 THEN 1 ELSE 0 END) AS tournament_wins,
        SUM(CASE WHEN dsum.finish <= 8 THEN 1 ELSE 0 END) AS tournament_top8s,
        (CASE WHEN ct.name = 'League' THEN 'league' WHEN ct.name = 'Gatherling' THEN 'tournament' ELSE 'other' END) AS deck_type
    FROM
        deck AS d
    INNER JOIN
        -- Eiliminate maindeck/sideboard double-counting with DISTINCT. See #5493.
        (SELECT DISTINCT card, deck_id FROM deck_card) AS dc ON d.id = dc.deck_id

    LEFT JOIN
        competition AS c ON d.competition_id = c.id
    LEFT JOIN
        competition_series AS cs ON cs.id = c.competition_series_id
    LEFT JOIN
        competition_type AS ct ON ct.id = cs.competition_type_id

    LEFT JOIN deck_cache AS season ON d.id = season.deck_id

    LEFT JOIN
        (
            SELECT
                d.id,
                d.created_date,
                d.finish,
                SUM(CASE WHEN dm.games > IFNULL(odm.games, 0) THEN 1 ELSE 0 END) AS wins, -- IFNULL so we still count byes as wins.
                SUM(CASE WHEN dm.games < odm.games THEN 1 ELSE 0 END) AS losses,
                SUM(CASE WHEN dm.games = odm.games THEN 1 ELSE 0 END) AS draws
            FROM
                deck_match AS dm
            INNER JOIN
                deck_match AS odm ON dm.match_id = odm.match_id AND dm.deck_id <> odm.deck_id
            INNER JOIN
                deck AS d ON d.id = dm.deck_id
            GROUP BY
                d.id
        ) AS dsum ON d.id = dsum.id

    GROUP BY
        card,
        season.season_id,
        ct.name
```

[] (slow_query, 958.7, mysql)

Reported on decksite by mysql-perf

Location Hash: 7c1b78e351d987fc4af4134cd398b330cb417fbe

Labels: decksite

vorpal-buildbot commented 1 year ago

Exceeded slow_query limit (815.3 > 60.0) in mysql: ```

    CREATE TABLE IF NOT EXISTS _new_card_stats (
        name VARCHAR(190) NOT NULL,
        season_id INT NOT NULL,
        num_decks INT NOT NULL,
        wins INT NOT NULL,
        losses INT NOT NULL,
        draws INT NOT NULL,
        perfect_runs INT NOT NULL,
        tournament_wins INT NOT NULL,
        tournament_top8s INT NOT NULL,
        deck_type ENUM('league', 'tournament', 'other') NOT NULL,
        PRIMARY KEY (season_id, name, deck_type),
        FOREIGN KEY (season_id) REFERENCES season (id) ON UPDATE CASCADE ON DELETE CASCADE
    ) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci AS
    SELECT
        card AS name,
        season.season_id,
        SUM(CASE WHEN d.id IS NOT NULL THEN 1 ELSE 0 END) AS num_decks,
        IFNULL(SUM(dsum.wins), 0) AS wins,
        IFNULL(SUM(dsum.losses), 0) AS losses,
        IFNULL(SUM(dsum.draws), 0) AS draws,
        SUM(CASE WHEN dsum.wins >= 5 AND dsum.losses = 0 AND d.source_id IN (SELECT id FROM source WHERE name = 'League') THEN 1 ELSE 0 END) AS perfect_runs,
        SUM(CASE WHEN dsum.finish = 1 THEN 1 ELSE 0 END) AS tournament_wins,
        SUM(CASE WHEN dsum.finish <= 8 THEN 1 ELSE 0 END) AS tournament_top8s,
        (CASE WHEN ct.name = 'League' THEN 'league' WHEN ct.name = 'Gatherling' THEN 'tournament' ELSE 'other' END) AS deck_type
    FROM
        deck AS d
    INNER JOIN
        -- Eiliminate maindeck/sideboard double-counting with DISTINCT. See #5493.
        (SELECT DISTINCT card, deck_id FROM deck_card) AS dc ON d.id = dc.deck_id

    LEFT JOIN
        competition AS c ON d.competition_id = c.id
    LEFT JOIN
        competition_series AS cs ON cs.id = c.competition_series_id
    LEFT JOIN
        competition_type AS ct ON ct.id = cs.competition_type_id

    LEFT JOIN deck_cache AS season ON d.id = season.deck_id

    LEFT JOIN
        (
            SELECT
                d.id,
                d.created_date,
                d.finish,
                SUM(CASE WHEN dm.games > IFNULL(odm.games, 0) THEN 1 ELSE 0 END) AS wins, -- IFNULL so we still count byes as wins.
                SUM(CASE WHEN dm.games < odm.games THEN 1 ELSE 0 END) AS losses,
                SUM(CASE WHEN dm.games = odm.games THEN 1 ELSE 0 END) AS draws
            FROM
                deck_match AS dm
            INNER JOIN
                deck_match AS odm ON dm.match_id = odm.match_id AND dm.deck_id <> odm.deck_id
            INNER JOIN
                deck AS d ON d.id = dm.deck_id
            GROUP BY
                d.id
        ) AS dsum ON d.id = dsum.id

    GROUP BY
        card,
        season.season_id,
        ct.name
```

[] (slow_query, 815.3, mysql)

Reported on decksite by mysql-perf

Location Hash: 7c1b78e351d987fc4af4134cd398b330cb417fbe

Labels: decksite

vorpal-buildbot commented 1 year ago

Exceeded slow_query limit (867.4 > 60.0) in mysql: ```

    CREATE TABLE IF NOT EXISTS _new_card_stats (
        name VARCHAR(190) NOT NULL,
        season_id INT NOT NULL,
        num_decks INT NOT NULL,
        wins INT NOT NULL,
        losses INT NOT NULL,
        draws INT NOT NULL,
        perfect_runs INT NOT NULL,
        tournament_wins INT NOT NULL,
        tournament_top8s INT NOT NULL,
        deck_type ENUM('league', 'tournament', 'other') NOT NULL,
        PRIMARY KEY (season_id, name, deck_type),
        FOREIGN KEY (season_id) REFERENCES season (id) ON UPDATE CASCADE ON DELETE CASCADE
    ) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci AS
    SELECT
        card AS name,
        season.season_id,
        SUM(CASE WHEN d.id IS NOT NULL THEN 1 ELSE 0 END) AS num_decks,
        IFNULL(SUM(dsum.wins), 0) AS wins,
        IFNULL(SUM(dsum.losses), 0) AS losses,
        IFNULL(SUM(dsum.draws), 0) AS draws,
        SUM(CASE WHEN dsum.wins >= 5 AND dsum.losses = 0 AND d.source_id IN (SELECT id FROM source WHERE name = 'League') THEN 1 ELSE 0 END) AS perfect_runs,
        SUM(CASE WHEN dsum.finish = 1 THEN 1 ELSE 0 END) AS tournament_wins,
        SUM(CASE WHEN dsum.finish <= 8 THEN 1 ELSE 0 END) AS tournament_top8s,
        (CASE WHEN ct.name = 'League' THEN 'league' WHEN ct.name = 'Gatherling' THEN 'tournament' ELSE 'other' END) AS deck_type
    FROM
        deck AS d
    INNER JOIN
        -- Eiliminate maindeck/sideboard double-counting with DISTINCT. See #5493.
        (SELECT DISTINCT card, deck_id FROM deck_card) AS dc ON d.id = dc.deck_id

    LEFT JOIN
        competition AS c ON d.competition_id = c.id
    LEFT JOIN
        competition_series AS cs ON cs.id = c.competition_series_id
    LEFT JOIN
        competition_type AS ct ON ct.id = cs.competition_type_id

    LEFT JOIN deck_cache AS season ON d.id = season.deck_id

    LEFT JOIN
        (
            SELECT
                d.id,
                d.created_date,
                d.finish,
                SUM(CASE WHEN dm.games > IFNULL(odm.games, 0) THEN 1 ELSE 0 END) AS wins, -- IFNULL so we still count byes as wins.
                SUM(CASE WHEN dm.games < odm.games THEN 1 ELSE 0 END) AS losses,
                SUM(CASE WHEN dm.games = odm.games THEN 1 ELSE 0 END) AS draws
            FROM
                deck_match AS dm
            INNER JOIN
                deck_match AS odm ON dm.match_id = odm.match_id AND dm.deck_id <> odm.deck_id
            INNER JOIN
                deck AS d ON d.id = dm.deck_id
            GROUP BY
                d.id
        ) AS dsum ON d.id = dsum.id

    GROUP BY
        card,
        season.season_id,
        ct.name
```

[] (slow_query, 867.4, mysql)

Reported on decksite by mysql-perf

Location Hash: 7c1b78e351d987fc4af4134cd398b330cb417fbe

Labels: decksite

vorpal-buildbot commented 1 year ago

Exceeded slow_query limit (923.3 > 60.0) in mysql: ```

    CREATE TABLE IF NOT EXISTS _new_card_stats (
        name VARCHAR(190) NOT NULL,
        season_id INT NOT NULL,
        num_decks INT NOT NULL,
        wins INT NOT NULL,
        losses INT NOT NULL,
        draws INT NOT NULL,
        perfect_runs INT NOT NULL,
        tournament_wins INT NOT NULL,
        tournament_top8s INT NOT NULL,
        deck_type ENUM('league', 'tournament', 'other') NOT NULL,
        PRIMARY KEY (season_id, name, deck_type),
        FOREIGN KEY (season_id) REFERENCES season (id) ON UPDATE CASCADE ON DELETE CASCADE
    ) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci AS
    SELECT
        card AS name,
        season.season_id,
        SUM(CASE WHEN d.id IS NOT NULL THEN 1 ELSE 0 END) AS num_decks,
        IFNULL(SUM(dsum.wins), 0) AS wins,
        IFNULL(SUM(dsum.losses), 0) AS losses,
        IFNULL(SUM(dsum.draws), 0) AS draws,
        SUM(CASE WHEN dsum.wins >= 5 AND dsum.losses = 0 AND d.source_id IN (SELECT id FROM source WHERE name = 'League') THEN 1 ELSE 0 END) AS perfect_runs,
        SUM(CASE WHEN dsum.finish = 1 THEN 1 ELSE 0 END) AS tournament_wins,
        SUM(CASE WHEN dsum.finish <= 8 THEN 1 ELSE 0 END) AS tournament_top8s,
        (CASE WHEN ct.name = 'League' THEN 'league' WHEN ct.name = 'Gatherling' THEN 'tournament' ELSE 'other' END) AS deck_type
    FROM
        deck AS d
    INNER JOIN
        -- Eiliminate maindeck/sideboard double-counting with DISTINCT. See #5493.
        (SELECT DISTINCT card, deck_id FROM deck_card) AS dc ON d.id = dc.deck_id

    LEFT JOIN
        competition AS c ON d.competition_id = c.id
    LEFT JOIN
        competition_series AS cs ON cs.id = c.competition_series_id
    LEFT JOIN
        competition_type AS ct ON ct.id = cs.competition_type_id

    LEFT JOIN deck_cache AS season ON d.id = season.deck_id

    LEFT JOIN
        (
            SELECT
                d.id,
                d.created_date,
                d.finish,
                SUM(CASE WHEN dm.games > IFNULL(odm.games, 0) THEN 1 ELSE 0 END) AS wins, -- IFNULL so we still count byes as wins.
                SUM(CASE WHEN dm.games < odm.games THEN 1 ELSE 0 END) AS losses,
                SUM(CASE WHEN dm.games = odm.games THEN 1 ELSE 0 END) AS draws
            FROM
                deck_match AS dm
            INNER JOIN
                deck_match AS odm ON dm.match_id = odm.match_id AND dm.deck_id <> odm.deck_id
            INNER JOIN
                deck AS d ON d.id = dm.deck_id
            GROUP BY
                d.id
        ) AS dsum ON d.id = dsum.id

    GROUP BY
        card,
        season.season_id,
        ct.name
```

[] (slow_query, 923.3, mysql)

Reported on decksite by mysql-perf

Location Hash: 7c1b78e351d987fc4af4134cd398b330cb417fbe

Labels: decksite

vorpal-buildbot commented 1 year ago

Exceeded slow_query limit (867.4 > 60.0) in mysql: ```

    CREATE TABLE IF NOT EXISTS _new_card_stats (
        name VARCHAR(190) NOT NULL,
        season_id INT NOT NULL,
        num_decks INT NOT NULL,
        wins INT NOT NULL,
        losses INT NOT NULL,
        draws INT NOT NULL,
        perfect_runs INT NOT NULL,
        tournament_wins INT NOT NULL,
        tournament_top8s INT NOT NULL,
        deck_type ENUM('league', 'tournament', 'other') NOT NULL,
        PRIMARY KEY (season_id, name, deck_type),
        FOREIGN KEY (season_id) REFERENCES season (id) ON UPDATE CASCADE ON DELETE CASCADE
    ) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci AS
    SELECT
        card AS name,
        season.season_id,
        SUM(CASE WHEN d.id IS NOT NULL THEN 1 ELSE 0 END) AS num_decks,
        IFNULL(SUM(dsum.wins), 0) AS wins,
        IFNULL(SUM(dsum.losses), 0) AS losses,
        IFNULL(SUM(dsum.draws), 0) AS draws,
        SUM(CASE WHEN dsum.wins >= 5 AND dsum.losses = 0 AND d.source_id IN (SELECT id FROM source WHERE name = 'League') THEN 1 ELSE 0 END) AS perfect_runs,
        SUM(CASE WHEN dsum.finish = 1 THEN 1 ELSE 0 END) AS tournament_wins,
        SUM(CASE WHEN dsum.finish <= 8 THEN 1 ELSE 0 END) AS tournament_top8s,
        (CASE WHEN ct.name = 'League' THEN 'league' WHEN ct.name = 'Gatherling' THEN 'tournament' ELSE 'other' END) AS deck_type
    FROM
        deck AS d
    INNER JOIN
        -- Eiliminate maindeck/sideboard double-counting with DISTINCT. See #5493.
        (SELECT DISTINCT card, deck_id FROM deck_card) AS dc ON d.id = dc.deck_id

    LEFT JOIN
        competition AS c ON d.competition_id = c.id
    LEFT JOIN
        competition_series AS cs ON cs.id = c.competition_series_id
    LEFT JOIN
        competition_type AS ct ON ct.id = cs.competition_type_id

    LEFT JOIN deck_cache AS season ON d.id = season.deck_id

    LEFT JOIN
        (
            SELECT
                d.id,
                d.created_date,
                d.finish,
                SUM(CASE WHEN dm.games > IFNULL(odm.games, 0) THEN 1 ELSE 0 END) AS wins, -- IFNULL so we still count byes as wins.
                SUM(CASE WHEN dm.games < odm.games THEN 1 ELSE 0 END) AS losses,
                SUM(CASE WHEN dm.games = odm.games THEN 1 ELSE 0 END) AS draws
            FROM
                deck_match AS dm
            INNER JOIN
                deck_match AS odm ON dm.match_id = odm.match_id AND dm.deck_id <> odm.deck_id
            INNER JOIN
                deck AS d ON d.id = dm.deck_id
            GROUP BY
                d.id
        ) AS dsum ON d.id = dsum.id

    GROUP BY
        card,
        season.season_id,
        ct.name
```

[] (slow_query, 867.4, mysql)

Reported on decksite by mysql-perf

Location Hash: 7c1b78e351d987fc4af4134cd398b330cb417fbe

Labels: decksite

vorpal-buildbot commented 1 year ago

Exceeded slow_query limit (857.7 > 60.0) in mysql: ```

    CREATE TABLE IF NOT EXISTS _new_card_stats (
        name VARCHAR(190) NOT NULL,
        season_id INT NOT NULL,
        num_decks INT NOT NULL,
        wins INT NOT NULL,
        losses INT NOT NULL,
        draws INT NOT NULL,
        perfect_runs INT NOT NULL,
        tournament_wins INT NOT NULL,
        tournament_top8s INT NOT NULL,
        deck_type ENUM('league', 'tournament', 'other') NOT NULL,
        PRIMARY KEY (season_id, name, deck_type),
        FOREIGN KEY (season_id) REFERENCES season (id) ON UPDATE CASCADE ON DELETE CASCADE
    ) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci AS
    SELECT
        card AS name,
        season.season_id,
        SUM(CASE WHEN d.id IS NOT NULL THEN 1 ELSE 0 END) AS num_decks,
        IFNULL(SUM(dsum.wins), 0) AS wins,
        IFNULL(SUM(dsum.losses), 0) AS losses,
        IFNULL(SUM(dsum.draws), 0) AS draws,
        SUM(CASE WHEN dsum.wins >= 5 AND dsum.losses = 0 AND d.source_id IN (SELECT id FROM source WHERE name = 'League') THEN 1 ELSE 0 END) AS perfect_runs,
        SUM(CASE WHEN dsum.finish = 1 THEN 1 ELSE 0 END) AS tournament_wins,
        SUM(CASE WHEN dsum.finish <= 8 THEN 1 ELSE 0 END) AS tournament_top8s,
        (CASE WHEN ct.name = 'League' THEN 'league' WHEN ct.name = 'Gatherling' THEN 'tournament' ELSE 'other' END) AS deck_type
    FROM
        deck AS d
    INNER JOIN
        -- Eiliminate maindeck/sideboard double-counting with DISTINCT. See #5493.
        (SELECT DISTINCT card, deck_id FROM deck_card) AS dc ON d.id = dc.deck_id

    LEFT JOIN
        competition AS c ON d.competition_id = c.id
    LEFT JOIN
        competition_series AS cs ON cs.id = c.competition_series_id
    LEFT JOIN
        competition_type AS ct ON ct.id = cs.competition_type_id

    LEFT JOIN deck_cache AS season ON d.id = season.deck_id

    LEFT JOIN
        (
            SELECT
                d.id,
                d.created_date,
                d.finish,
                SUM(CASE WHEN dm.games > IFNULL(odm.games, 0) THEN 1 ELSE 0 END) AS wins, -- IFNULL so we still count byes as wins.
                SUM(CASE WHEN dm.games < odm.games THEN 1 ELSE 0 END) AS losses,
                SUM(CASE WHEN dm.games = odm.games THEN 1 ELSE 0 END) AS draws
            FROM
                deck_match AS dm
            INNER JOIN
                deck_match AS odm ON dm.match_id = odm.match_id AND dm.deck_id <> odm.deck_id
            INNER JOIN
                deck AS d ON d.id = dm.deck_id
            GROUP BY
                d.id
        ) AS dsum ON d.id = dsum.id

    GROUP BY
        card,
        season.season_id,
        ct.name
```

[] (slow_query, 857.7, mysql)

Reported on decksite by mysql-perf

Location Hash: 7c1b78e351d987fc4af4134cd398b330cb417fbe

Labels: decksite

vorpal-buildbot commented 1 year ago

Exceeded slow_query limit (818.0 > 60.0) in mysql: ```

    CREATE TABLE IF NOT EXISTS _new_card_stats (
        name VARCHAR(190) NOT NULL,
        season_id INT NOT NULL,
        num_decks INT NOT NULL,
        wins INT NOT NULL,
        losses INT NOT NULL,
        draws INT NOT NULL,
        perfect_runs INT NOT NULL,
        tournament_wins INT NOT NULL,
        tournament_top8s INT NOT NULL,
        deck_type ENUM('league', 'tournament', 'other') NOT NULL,
        PRIMARY KEY (season_id, name, deck_type),
        FOREIGN KEY (season_id) REFERENCES season (id) ON UPDATE CASCADE ON DELETE CASCADE
    ) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci AS
    SELECT
        card AS name,
        season.season_id,
        SUM(CASE WHEN d.id IS NOT NULL THEN 1 ELSE 0 END) AS num_decks,
        IFNULL(SUM(dsum.wins), 0) AS wins,
        IFNULL(SUM(dsum.losses), 0) AS losses,
        IFNULL(SUM(dsum.draws), 0) AS draws,
        SUM(CASE WHEN dsum.wins >= 5 AND dsum.losses = 0 AND d.source_id IN (SELECT id FROM source WHERE name = 'League') THEN 1 ELSE 0 END) AS perfect_runs,
        SUM(CASE WHEN dsum.finish = 1 THEN 1 ELSE 0 END) AS tournament_wins,
        SUM(CASE WHEN dsum.finish <= 8 THEN 1 ELSE 0 END) AS tournament_top8s,
        (CASE WHEN ct.name = 'League' THEN 'league' WHEN ct.name = 'Gatherling' THEN 'tournament' ELSE 'other' END) AS deck_type
    FROM
        deck AS d
    INNER JOIN
        -- Eiliminate maindeck/sideboard double-counting with DISTINCT. See #5493.
        (SELECT DISTINCT card, deck_id FROM deck_card) AS dc ON d.id = dc.deck_id

    LEFT JOIN
        competition AS c ON d.competition_id = c.id
    LEFT JOIN
        competition_series AS cs ON cs.id = c.competition_series_id
    LEFT JOIN
        competition_type AS ct ON ct.id = cs.competition_type_id

    LEFT JOIN deck_cache AS season ON d.id = season.deck_id

    LEFT JOIN
        (
            SELECT
                d.id,
                d.created_date,
                d.finish,
                SUM(CASE WHEN dm.games > IFNULL(odm.games, 0) THEN 1 ELSE 0 END) AS wins, -- IFNULL so we still count byes as wins.
                SUM(CASE WHEN dm.games < odm.games THEN 1 ELSE 0 END) AS losses,
                SUM(CASE WHEN dm.games = odm.games THEN 1 ELSE 0 END) AS draws
            FROM
                deck_match AS dm
            INNER JOIN
                deck_match AS odm ON dm.match_id = odm.match_id AND dm.deck_id <> odm.deck_id
            INNER JOIN
                deck AS d ON d.id = dm.deck_id
            GROUP BY
                d.id
        ) AS dsum ON d.id = dsum.id

    GROUP BY
        card,
        season.season_id,
        ct.name
```

[] (slow_query, 818.0, mysql)

Reported on decksite by mysql-perf

Location Hash: 7c1b78e351d987fc4af4134cd398b330cb417fbe

Labels: decksite

vorpal-buildbot commented 1 year ago

Exceeded slow_query limit (954.1 > 60.0) in mysql: ```

    CREATE TABLE IF NOT EXISTS _new_card_stats (
        name VARCHAR(190) NOT NULL,
        season_id INT NOT NULL,
        num_decks INT NOT NULL,
        wins INT NOT NULL,
        losses INT NOT NULL,
        draws INT NOT NULL,
        perfect_runs INT NOT NULL,
        tournament_wins INT NOT NULL,
        tournament_top8s INT NOT NULL,
        deck_type ENUM('league', 'tournament', 'other') NOT NULL,
        PRIMARY KEY (season_id, name, deck_type),
        FOREIGN KEY (season_id) REFERENCES season (id) ON UPDATE CASCADE ON DELETE CASCADE
    ) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci AS
    SELECT
        card AS name,
        season.season_id,
        SUM(CASE WHEN d.id IS NOT NULL THEN 1 ELSE 0 END) AS num_decks,
        IFNULL(SUM(dsum.wins), 0) AS wins,
        IFNULL(SUM(dsum.losses), 0) AS losses,
        IFNULL(SUM(dsum.draws), 0) AS draws,
        SUM(CASE WHEN dsum.wins >= 5 AND dsum.losses = 0 AND d.source_id IN (SELECT id FROM source WHERE name = 'League') THEN 1 ELSE 0 END) AS perfect_runs,
        SUM(CASE WHEN dsum.finish = 1 THEN 1 ELSE 0 END) AS tournament_wins,
        SUM(CASE WHEN dsum.finish <= 8 THEN 1 ELSE 0 END) AS tournament_top8s,
        (CASE WHEN ct.name = 'League' THEN 'league' WHEN ct.name = 'Gatherling' THEN 'tournament' ELSE 'other' END) AS deck_type
    FROM
        deck AS d
    INNER JOIN
        -- Eiliminate maindeck/sideboard double-counting with DISTINCT. See #5493.
        (SELECT DISTINCT card, deck_id FROM deck_card) AS dc ON d.id = dc.deck_id

    LEFT JOIN
        competition AS c ON d.competition_id = c.id
    LEFT JOIN
        competition_series AS cs ON cs.id = c.competition_series_id
    LEFT JOIN
        competition_type AS ct ON ct.id = cs.competition_type_id

    LEFT JOIN deck_cache AS season ON d.id = season.deck_id

    LEFT JOIN
        (
            SELECT
                d.id,
                d.created_date,
                d.finish,
                SUM(CASE WHEN dm.games > IFNULL(odm.games, 0) THEN 1 ELSE 0 END) AS wins, -- IFNULL so we still count byes as wins.
                SUM(CASE WHEN dm.games < odm.games THEN 1 ELSE 0 END) AS losses,
                SUM(CASE WHEN dm.games = odm.games THEN 1 ELSE 0 END) AS draws
            FROM
                deck_match AS dm
            INNER JOIN
                deck_match AS odm ON dm.match_id = odm.match_id AND dm.deck_id <> odm.deck_id
            INNER JOIN
                deck AS d ON d.id = dm.deck_id
            GROUP BY
                d.id
        ) AS dsum ON d.id = dsum.id

    GROUP BY
        card,
        season.season_id,
        ct.name
```

[] (slow_query, 954.1, mysql)

Reported on decksite by mysql-perf

Location Hash: 7c1b78e351d987fc4af4134cd398b330cb417fbe

Labels: decksite

vorpal-buildbot commented 1 year ago

Exceeded slow_query limit (851.4 > 60.0) in mysql: ```

    CREATE TABLE IF NOT EXISTS _new_card_stats (
        name VARCHAR(190) NOT NULL,
        season_id INT NOT NULL,
        num_decks INT NOT NULL,
        wins INT NOT NULL,
        losses INT NOT NULL,
        draws INT NOT NULL,
        perfect_runs INT NOT NULL,
        tournament_wins INT NOT NULL,
        tournament_top8s INT NOT NULL,
        deck_type ENUM('league', 'tournament', 'other') NOT NULL,
        PRIMARY KEY (season_id, name, deck_type),
        FOREIGN KEY (season_id) REFERENCES season (id) ON UPDATE CASCADE ON DELETE CASCADE
    ) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci AS
    SELECT
        card AS name,
        season.season_id,
        SUM(CASE WHEN d.id IS NOT NULL THEN 1 ELSE 0 END) AS num_decks,
        IFNULL(SUM(dsum.wins), 0) AS wins,
        IFNULL(SUM(dsum.losses), 0) AS losses,
        IFNULL(SUM(dsum.draws), 0) AS draws,
        SUM(CASE WHEN dsum.wins >= 5 AND dsum.losses = 0 AND d.source_id IN (SELECT id FROM source WHERE name = 'League') THEN 1 ELSE 0 END) AS perfect_runs,
        SUM(CASE WHEN dsum.finish = 1 THEN 1 ELSE 0 END) AS tournament_wins,
        SUM(CASE WHEN dsum.finish <= 8 THEN 1 ELSE 0 END) AS tournament_top8s,
        (CASE WHEN ct.name = 'League' THEN 'league' WHEN ct.name = 'Gatherling' THEN 'tournament' ELSE 'other' END) AS deck_type
    FROM
        deck AS d
    INNER JOIN
        -- Eiliminate maindeck/sideboard double-counting with DISTINCT. See #5493.
        (SELECT DISTINCT card, deck_id FROM deck_card) AS dc ON d.id = dc.deck_id

    LEFT JOIN
        competition AS c ON d.competition_id = c.id
    LEFT JOIN
        competition_series AS cs ON cs.id = c.competition_series_id
    LEFT JOIN
        competition_type AS ct ON ct.id = cs.competition_type_id

    LEFT JOIN deck_cache AS season ON d.id = season.deck_id

    LEFT JOIN
        (
            SELECT
                d.id,
                d.created_date,
                d.finish,
                SUM(CASE WHEN dm.games > IFNULL(odm.games, 0) THEN 1 ELSE 0 END) AS wins, -- IFNULL so we still count byes as wins.
                SUM(CASE WHEN dm.games < odm.games THEN 1 ELSE 0 END) AS losses,
                SUM(CASE WHEN dm.games = odm.games THEN 1 ELSE 0 END) AS draws
            FROM
                deck_match AS dm
            INNER JOIN
                deck_match AS odm ON dm.match_id = odm.match_id AND dm.deck_id <> odm.deck_id
            INNER JOIN
                deck AS d ON d.id = dm.deck_id
            GROUP BY
                d.id
        ) AS dsum ON d.id = dsum.id

    GROUP BY
        card,
        season.season_id,
        ct.name
```

[] (slow_query, 851.4, mysql)

Reported on decksite by mysql-perf

Location Hash: 7c1b78e351d987fc4af4134cd398b330cb417fbe

Labels: decksite

vorpal-buildbot commented 1 year ago

Exceeded slow_query limit (907.1 > 60.0) in mysql: ```

    CREATE TABLE IF NOT EXISTS _new_card_stats (
        name VARCHAR(190) NOT NULL,
        season_id INT NOT NULL,
        num_decks INT NOT NULL,
        wins INT NOT NULL,
        losses INT NOT NULL,
        draws INT NOT NULL,
        perfect_runs INT NOT NULL,
        tournament_wins INT NOT NULL,
        tournament_top8s INT NOT NULL,
        deck_type ENUM('league', 'tournament', 'other') NOT NULL,
        PRIMARY KEY (season_id, name, deck_type),
        FOREIGN KEY (season_id) REFERENCES season (id) ON UPDATE CASCADE ON DELETE CASCADE
    ) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci AS
    SELECT
        card AS name,
        season.season_id,
        SUM(CASE WHEN d.id IS NOT NULL THEN 1 ELSE 0 END) AS num_decks,
        IFNULL(SUM(dsum.wins), 0) AS wins,
        IFNULL(SUM(dsum.losses), 0) AS losses,
        IFNULL(SUM(dsum.draws), 0) AS draws,
        SUM(CASE WHEN dsum.wins >= 5 AND dsum.losses = 0 AND d.source_id IN (SELECT id FROM source WHERE name = 'League') THEN 1 ELSE 0 END) AS perfect_runs,
        SUM(CASE WHEN dsum.finish = 1 THEN 1 ELSE 0 END) AS tournament_wins,
        SUM(CASE WHEN dsum.finish <= 8 THEN 1 ELSE 0 END) AS tournament_top8s,
        (CASE WHEN ct.name = 'League' THEN 'league' WHEN ct.name = 'Gatherling' THEN 'tournament' ELSE 'other' END) AS deck_type
    FROM
        deck AS d
    INNER JOIN
        -- Eiliminate maindeck/sideboard double-counting with DISTINCT. See #5493.
        (SELECT DISTINCT card, deck_id FROM deck_card) AS dc ON d.id = dc.deck_id

    LEFT JOIN
        competition AS c ON d.competition_id = c.id
    LEFT JOIN
        competition_series AS cs ON cs.id = c.competition_series_id
    LEFT JOIN
        competition_type AS ct ON ct.id = cs.competition_type_id

    LEFT JOIN deck_cache AS season ON d.id = season.deck_id

    LEFT JOIN
        (
            SELECT
                d.id,
                d.created_date,
                d.finish,
                SUM(CASE WHEN dm.games > IFNULL(odm.games, 0) THEN 1 ELSE 0 END) AS wins, -- IFNULL so we still count byes as wins.
                SUM(CASE WHEN dm.games < odm.games THEN 1 ELSE 0 END) AS losses,
                SUM(CASE WHEN dm.games = odm.games THEN 1 ELSE 0 END) AS draws
            FROM
                deck_match AS dm
            INNER JOIN
                deck_match AS odm ON dm.match_id = odm.match_id AND dm.deck_id <> odm.deck_id
            INNER JOIN
                deck AS d ON d.id = dm.deck_id
            GROUP BY
                d.id
        ) AS dsum ON d.id = dsum.id

    GROUP BY
        card,
        season.season_id,
        ct.name
```

[] (slow_query, 907.1, mysql)

Reported on decksite by mysql-perf

Location Hash: 7c1b78e351d987fc4af4134cd398b330cb417fbe

Labels: decksite

vorpal-buildbot commented 1 year ago

Exceeded slow_query limit (1028.2 > 60.0) in mysql: ```

    CREATE TABLE IF NOT EXISTS _new_card_stats (
        name VARCHAR(190) NOT NULL,
        season_id INT NOT NULL,
        num_decks INT NOT NULL,
        wins INT NOT NULL,
        losses INT NOT NULL,
        draws INT NOT NULL,
        perfect_runs INT NOT NULL,
        tournament_wins INT NOT NULL,
        tournament_top8s INT NOT NULL,
        deck_type ENUM('league', 'tournament', 'other') NOT NULL,
        PRIMARY KEY (season_id, name, deck_type),
        FOREIGN KEY (season_id) REFERENCES season (id) ON UPDATE CASCADE ON DELETE CASCADE
    ) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci AS
    SELECT
        card AS name,
        season.season_id,
        SUM(CASE WHEN d.id IS NOT NULL THEN 1 ELSE 0 END) AS num_decks,
        IFNULL(SUM(dsum.wins), 0) AS wins,
        IFNULL(SUM(dsum.losses), 0) AS losses,
        IFNULL(SUM(dsum.draws), 0) AS draws,
        SUM(CASE WHEN dsum.wins >= 5 AND dsum.losses = 0 AND d.source_id IN (SELECT id FROM source WHERE name = 'League') THEN 1 ELSE 0 END) AS perfect_runs,
        SUM(CASE WHEN dsum.finish = 1 THEN 1 ELSE 0 END) AS tournament_wins,
        SUM(CASE WHEN dsum.finish <= 8 THEN 1 ELSE 0 END) AS tournament_top8s,
        (CASE WHEN ct.name = 'League' THEN 'league' WHEN ct.name = 'Gatherling' THEN 'tournament' ELSE 'other' END) AS deck_type
    FROM
        deck AS d
    INNER JOIN
        -- Eiliminate maindeck/sideboard double-counting with DISTINCT. See #5493.
        (SELECT DISTINCT card, deck_id FROM deck_card) AS dc ON d.id = dc.deck_id

    LEFT JOIN
        competition AS c ON d.competition_id = c.id
    LEFT JOIN
        competition_series AS cs ON cs.id = c.competition_series_id
    LEFT JOIN
        competition_type AS ct ON ct.id = cs.competition_type_id

    LEFT JOIN deck_cache AS season ON d.id = season.deck_id

    LEFT JOIN
        (
            SELECT
                d.id,
                d.created_date,
                d.finish,
                SUM(CASE WHEN dm.games > IFNULL(odm.games, 0) THEN 1 ELSE 0 END) AS wins, -- IFNULL so we still count byes as wins.
                SUM(CASE WHEN dm.games < odm.games THEN 1 ELSE 0 END) AS losses,
                SUM(CASE WHEN dm.games = odm.games THEN 1 ELSE 0 END) AS draws
            FROM
                deck_match AS dm
            INNER JOIN
                deck_match AS odm ON dm.match_id = odm.match_id AND dm.deck_id <> odm.deck_id
            INNER JOIN
                deck AS d ON d.id = dm.deck_id
            GROUP BY
                d.id
        ) AS dsum ON d.id = dsum.id

    GROUP BY
        card,
        season.season_id,
        ct.name
```

[] (slow_query, 1028.2, mysql)

Reported on decksite by mysql-perf

Location Hash: 7c1b78e351d987fc4af4134cd398b330cb417fbe

Labels: decksite

vorpal-buildbot commented 1 year ago

Exceeded slow_query limit (837.8 > 60.0) in mysql: ```

    CREATE TABLE IF NOT EXISTS _new_card_stats (
        name VARCHAR(190) NOT NULL,
        season_id INT NOT NULL,
        num_decks INT NOT NULL,
        wins INT NOT NULL,
        losses INT NOT NULL,
        draws INT NOT NULL,
        perfect_runs INT NOT NULL,
        tournament_wins INT NOT NULL,
        tournament_top8s INT NOT NULL,
        deck_type ENUM('league', 'tournament', 'other') NOT NULL,
        PRIMARY KEY (season_id, name, deck_type),
        FOREIGN KEY (season_id) REFERENCES season (id) ON UPDATE CASCADE ON DELETE CASCADE
    ) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci AS
    SELECT
        card AS name,
        season.season_id,
        SUM(CASE WHEN d.id IS NOT NULL THEN 1 ELSE 0 END) AS num_decks,
        IFNULL(SUM(dsum.wins), 0) AS wins,
        IFNULL(SUM(dsum.losses), 0) AS losses,
        IFNULL(SUM(dsum.draws), 0) AS draws,
        SUM(CASE WHEN dsum.wins >= 5 AND dsum.losses = 0 AND d.source_id IN (SELECT id FROM source WHERE name = 'League') THEN 1 ELSE 0 END) AS perfect_runs,
        SUM(CASE WHEN dsum.finish = 1 THEN 1 ELSE 0 END) AS tournament_wins,
        SUM(CASE WHEN dsum.finish <= 8 THEN 1 ELSE 0 END) AS tournament_top8s,
        (CASE WHEN ct.name = 'League' THEN 'league' WHEN ct.name = 'Gatherling' THEN 'tournament' ELSE 'other' END) AS deck_type
    FROM
        deck AS d
    INNER JOIN
        -- Eiliminate maindeck/sideboard double-counting with DISTINCT. See #5493.
        (SELECT DISTINCT card, deck_id FROM deck_card) AS dc ON d.id = dc.deck_id

    LEFT JOIN
        competition AS c ON d.competition_id = c.id
    LEFT JOIN
        competition_series AS cs ON cs.id = c.competition_series_id
    LEFT JOIN
        competition_type AS ct ON ct.id = cs.competition_type_id

    LEFT JOIN deck_cache AS season ON d.id = season.deck_id

    LEFT JOIN
        (
            SELECT
                d.id,
                d.created_date,
                d.finish,
                SUM(CASE WHEN dm.games > IFNULL(odm.games, 0) THEN 1 ELSE 0 END) AS wins, -- IFNULL so we still count byes as wins.
                SUM(CASE WHEN dm.games < odm.games THEN 1 ELSE 0 END) AS losses,
                SUM(CASE WHEN dm.games = odm.games THEN 1 ELSE 0 END) AS draws
            FROM
                deck_match AS dm
            INNER JOIN
                deck_match AS odm ON dm.match_id = odm.match_id AND dm.deck_id <> odm.deck_id
            INNER JOIN
                deck AS d ON d.id = dm.deck_id
            GROUP BY
                d.id
        ) AS dsum ON d.id = dsum.id

    GROUP BY
        card,
        season.season_id,
        ct.name
```

[] (slow_query, 837.8, mysql)

Reported on decksite by mysql-perf

Location Hash: 7c1b78e351d987fc4af4134cd398b330cb417fbe

Labels: decksite

vorpal-buildbot commented 1 year ago

Exceeded slow_query limit (826.0 > 60.0) in mysql: ```

    CREATE TABLE IF NOT EXISTS _new_card_stats (
        name VARCHAR(190) NOT NULL,
        season_id INT NOT NULL,
        num_decks INT NOT NULL,
        wins INT NOT NULL,
        losses INT NOT NULL,
        draws INT NOT NULL,
        perfect_runs INT NOT NULL,
        tournament_wins INT NOT NULL,
        tournament_top8s INT NOT NULL,
        deck_type ENUM('league', 'tournament', 'other') NOT NULL,
        PRIMARY KEY (season_id, name, deck_type),
        FOREIGN KEY (season_id) REFERENCES season (id) ON UPDATE CASCADE ON DELETE CASCADE
    ) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci AS
    SELECT
        card AS name,
        season.season_id,
        SUM(CASE WHEN d.id IS NOT NULL THEN 1 ELSE 0 END) AS num_decks,
        IFNULL(SUM(dsum.wins), 0) AS wins,
        IFNULL(SUM(dsum.losses), 0) AS losses,
        IFNULL(SUM(dsum.draws), 0) AS draws,
        SUM(CASE WHEN dsum.wins >= 5 AND dsum.losses = 0 AND d.source_id IN (SELECT id FROM source WHERE name = 'League') THEN 1 ELSE 0 END) AS perfect_runs,
        SUM(CASE WHEN dsum.finish = 1 THEN 1 ELSE 0 END) AS tournament_wins,
        SUM(CASE WHEN dsum.finish <= 8 THEN 1 ELSE 0 END) AS tournament_top8s,
        (CASE WHEN ct.name = 'League' THEN 'league' WHEN ct.name = 'Gatherling' THEN 'tournament' ELSE 'other' END) AS deck_type
    FROM
        deck AS d
    INNER JOIN
        -- Eiliminate maindeck/sideboard double-counting with DISTINCT. See #5493.
        (SELECT DISTINCT card, deck_id FROM deck_card) AS dc ON d.id = dc.deck_id

    LEFT JOIN
        competition AS c ON d.competition_id = c.id
    LEFT JOIN
        competition_series AS cs ON cs.id = c.competition_series_id
    LEFT JOIN
        competition_type AS ct ON ct.id = cs.competition_type_id

    LEFT JOIN deck_cache AS season ON d.id = season.deck_id

    LEFT JOIN
        (
            SELECT
                d.id,
                d.created_date,
                d.finish,
                SUM(CASE WHEN dm.games > IFNULL(odm.games, 0) THEN 1 ELSE 0 END) AS wins, -- IFNULL so we still count byes as wins.
                SUM(CASE WHEN dm.games < odm.games THEN 1 ELSE 0 END) AS losses,
                SUM(CASE WHEN dm.games = odm.games THEN 1 ELSE 0 END) AS draws
            FROM
                deck_match AS dm
            INNER JOIN
                deck_match AS odm ON dm.match_id = odm.match_id AND dm.deck_id <> odm.deck_id
            INNER JOIN
                deck AS d ON d.id = dm.deck_id
            GROUP BY
                d.id
        ) AS dsum ON d.id = dsum.id

    GROUP BY
        card,
        season.season_id,
        ct.name
```

[] (slow_query, 826.0, mysql)

Reported on decksite by mysql-perf

Location Hash: 7c1b78e351d987fc4af4134cd398b330cb417fbe

Labels: decksite

vorpal-buildbot commented 1 year ago

Exceeded slow_query limit (909.6 > 60.0) in mysql: ```

    CREATE TABLE IF NOT EXISTS _new_card_stats (
        name VARCHAR(190) NOT NULL,
        season_id INT NOT NULL,
        num_decks INT NOT NULL,
        wins INT NOT NULL,
        losses INT NOT NULL,
        draws INT NOT NULL,
        perfect_runs INT NOT NULL,
        tournament_wins INT NOT NULL,
        tournament_top8s INT NOT NULL,
        deck_type ENUM('league', 'tournament', 'other') NOT NULL,
        PRIMARY KEY (season_id, name, deck_type),
        FOREIGN KEY (season_id) REFERENCES season (id) ON UPDATE CASCADE ON DELETE CASCADE
    ) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci AS
    SELECT
        card AS name,
        season.season_id,
        SUM(CASE WHEN d.id IS NOT NULL THEN 1 ELSE 0 END) AS num_decks,
        IFNULL(SUM(dsum.wins), 0) AS wins,
        IFNULL(SUM(dsum.losses), 0) AS losses,
        IFNULL(SUM(dsum.draws), 0) AS draws,
        SUM(CASE WHEN dsum.wins >= 5 AND dsum.losses = 0 AND d.source_id IN (SELECT id FROM source WHERE name = 'League') THEN 1 ELSE 0 END) AS perfect_runs,
        SUM(CASE WHEN dsum.finish = 1 THEN 1 ELSE 0 END) AS tournament_wins,
        SUM(CASE WHEN dsum.finish <= 8 THEN 1 ELSE 0 END) AS tournament_top8s,
        (CASE WHEN ct.name = 'League' THEN 'league' WHEN ct.name = 'Gatherling' THEN 'tournament' ELSE 'other' END) AS deck_type
    FROM
        deck AS d
    INNER JOIN
        -- Eiliminate maindeck/sideboard double-counting with DISTINCT. See #5493.
        (SELECT DISTINCT card, deck_id FROM deck_card) AS dc ON d.id = dc.deck_id

    LEFT JOIN
        competition AS c ON d.competition_id = c.id
    LEFT JOIN
        competition_series AS cs ON cs.id = c.competition_series_id
    LEFT JOIN
        competition_type AS ct ON ct.id = cs.competition_type_id

    LEFT JOIN deck_cache AS season ON d.id = season.deck_id

    LEFT JOIN
        (
            SELECT
                d.id,
                d.created_date,
                d.finish,
                SUM(CASE WHEN dm.games > IFNULL(odm.games, 0) THEN 1 ELSE 0 END) AS wins, -- IFNULL so we still count byes as wins.
                SUM(CASE WHEN dm.games < odm.games THEN 1 ELSE 0 END) AS losses,
                SUM(CASE WHEN dm.games = odm.games THEN 1 ELSE 0 END) AS draws
            FROM
                deck_match AS dm
            INNER JOIN
                deck_match AS odm ON dm.match_id = odm.match_id AND dm.deck_id <> odm.deck_id
            INNER JOIN
                deck AS d ON d.id = dm.deck_id
            GROUP BY
                d.id
        ) AS dsum ON d.id = dsum.id

    GROUP BY
        card,
        season.season_id,
        ct.name
```

[] (slow_query, 909.6, mysql)

Reported on decksite by mysql-perf

Location Hash: 7c1b78e351d987fc4af4134cd398b330cb417fbe

Labels: decksite

vorpal-buildbot commented 1 year ago

Exceeded slow_query limit (971.8 > 60.0) in mysql: ```

    CREATE TABLE IF NOT EXISTS _new_card_stats (
        name VARCHAR(190) NOT NULL,
        season_id INT NOT NULL,
        num_decks INT NOT NULL,
        wins INT NOT NULL,
        losses INT NOT NULL,
        draws INT NOT NULL,
        perfect_runs INT NOT NULL,
        tournament_wins INT NOT NULL,
        tournament_top8s INT NOT NULL,
        deck_type ENUM('league', 'tournament', 'other') NOT NULL,
        PRIMARY KEY (season_id, name, deck_type),
        FOREIGN KEY (season_id) REFERENCES season (id) ON UPDATE CASCADE ON DELETE CASCADE
    ) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci AS
    SELECT
        card AS name,
        season.season_id,
        SUM(CASE WHEN d.id IS NOT NULL THEN 1 ELSE 0 END) AS num_decks,
        IFNULL(SUM(dsum.wins), 0) AS wins,
        IFNULL(SUM(dsum.losses), 0) AS losses,
        IFNULL(SUM(dsum.draws), 0) AS draws,
        SUM(CASE WHEN dsum.wins >= 5 AND dsum.losses = 0 AND d.source_id IN (SELECT id FROM source WHERE name = 'League') THEN 1 ELSE 0 END) AS perfect_runs,
        SUM(CASE WHEN dsum.finish = 1 THEN 1 ELSE 0 END) AS tournament_wins,
        SUM(CASE WHEN dsum.finish <= 8 THEN 1 ELSE 0 END) AS tournament_top8s,
        (CASE WHEN ct.name = 'League' THEN 'league' WHEN ct.name = 'Gatherling' THEN 'tournament' ELSE 'other' END) AS deck_type
    FROM
        deck AS d
    INNER JOIN
        -- Eiliminate maindeck/sideboard double-counting with DISTINCT. See #5493.
        (SELECT DISTINCT card, deck_id FROM deck_card) AS dc ON d.id = dc.deck_id

    LEFT JOIN
        competition AS c ON d.competition_id = c.id
    LEFT JOIN
        competition_series AS cs ON cs.id = c.competition_series_id
    LEFT JOIN
        competition_type AS ct ON ct.id = cs.competition_type_id

    LEFT JOIN deck_cache AS season ON d.id = season.deck_id

    LEFT JOIN
        (
            SELECT
                d.id,
                d.created_date,
                d.finish,
                SUM(CASE WHEN dm.games > IFNULL(odm.games, 0) THEN 1 ELSE 0 END) AS wins, -- IFNULL so we still count byes as wins.
                SUM(CASE WHEN dm.games < odm.games THEN 1 ELSE 0 END) AS losses,
                SUM(CASE WHEN dm.games = odm.games THEN 1 ELSE 0 END) AS draws
            FROM
                deck_match AS dm
            INNER JOIN
                deck_match AS odm ON dm.match_id = odm.match_id AND dm.deck_id <> odm.deck_id
            INNER JOIN
                deck AS d ON d.id = dm.deck_id
            GROUP BY
                d.id
        ) AS dsum ON d.id = dsum.id

    GROUP BY
        card,
        season.season_id,
        ct.name
```

[] (slow_query, 971.8, mysql)

Reported on decksite by mysql-perf

Location Hash: 7c1b78e351d987fc4af4134cd398b330cb417fbe

Labels: decksite

vorpal-buildbot commented 1 year ago

Exceeded slow_query limit (791.7 > 60.0) in mysql: ```

    CREATE TABLE IF NOT EXISTS _new_card_stats (
        name VARCHAR(190) NOT NULL,
        season_id INT NOT NULL,
        num_decks INT NOT NULL,
        wins INT NOT NULL,
        losses INT NOT NULL,
        draws INT NOT NULL,
        perfect_runs INT NOT NULL,
        tournament_wins INT NOT NULL,
        tournament_top8s INT NOT NULL,
        deck_type ENUM('league', 'tournament', 'other') NOT NULL,
        PRIMARY KEY (season_id, name, deck_type),
        FOREIGN KEY (season_id) REFERENCES season (id) ON UPDATE CASCADE ON DELETE CASCADE
    ) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci AS
    SELECT
        card AS name,
        season.season_id,
        SUM(CASE WHEN d.id IS NOT NULL THEN 1 ELSE 0 END) AS num_decks,
        IFNULL(SUM(dsum.wins), 0) AS wins,
        IFNULL(SUM(dsum.losses), 0) AS losses,
        IFNULL(SUM(dsum.draws), 0) AS draws,
        SUM(CASE WHEN dsum.wins >= 5 AND dsum.losses = 0 AND d.source_id IN (SELECT id FROM source WHERE name = 'League') THEN 1 ELSE 0 END) AS perfect_runs,
        SUM(CASE WHEN dsum.finish = 1 THEN 1 ELSE 0 END) AS tournament_wins,
        SUM(CASE WHEN dsum.finish <= 8 THEN 1 ELSE 0 END) AS tournament_top8s,
        (CASE WHEN ct.name = 'League' THEN 'league' WHEN ct.name = 'Gatherling' THEN 'tournament' ELSE 'other' END) AS deck_type
    FROM
        deck AS d
    INNER JOIN
        -- Eiliminate maindeck/sideboard double-counting with DISTINCT. See #5493.
        (SELECT DISTINCT card, deck_id FROM deck_card) AS dc ON d.id = dc.deck_id

    LEFT JOIN
        competition AS c ON d.competition_id = c.id
    LEFT JOIN
        competition_series AS cs ON cs.id = c.competition_series_id
    LEFT JOIN
        competition_type AS ct ON ct.id = cs.competition_type_id

    LEFT JOIN deck_cache AS season ON d.id = season.deck_id

    LEFT JOIN
        (
            SELECT
                d.id,
                d.created_date,
                d.finish,
                SUM(CASE WHEN dm.games > IFNULL(odm.games, 0) THEN 1 ELSE 0 END) AS wins, -- IFNULL so we still count byes as wins.
                SUM(CASE WHEN dm.games < odm.games THEN 1 ELSE 0 END) AS losses,
                SUM(CASE WHEN dm.games = odm.games THEN 1 ELSE 0 END) AS draws
            FROM
                deck_match AS dm
            INNER JOIN
                deck_match AS odm ON dm.match_id = odm.match_id AND dm.deck_id <> odm.deck_id
            INNER JOIN
                deck AS d ON d.id = dm.deck_id
            GROUP BY
                d.id
        ) AS dsum ON d.id = dsum.id

    GROUP BY
        card,
        season.season_id,
        ct.name
```

[] (slow_query, 791.7, mysql)

Reported on decksite by mysql-perf

Location Hash: 7c1b78e351d987fc4af4134cd398b330cb417fbe

Labels: decksite

vorpal-buildbot commented 1 year ago

Exceeded slow_query limit (888.1 > 60.0) in mysql: ```

    CREATE TABLE IF NOT EXISTS _new_card_stats (
        name VARCHAR(190) NOT NULL,
        season_id INT NOT NULL,
        num_decks INT NOT NULL,
        wins INT NOT NULL,
        losses INT NOT NULL,
        draws INT NOT NULL,
        perfect_runs INT NOT NULL,
        tournament_wins INT NOT NULL,
        tournament_top8s INT NOT NULL,
        deck_type ENUM('league', 'tournament', 'other') NOT NULL,
        PRIMARY KEY (season_id, name, deck_type),
        FOREIGN KEY (season_id) REFERENCES season (id) ON UPDATE CASCADE ON DELETE CASCADE
    ) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci AS
    SELECT
        card AS name,
        season.season_id,
        SUM(CASE WHEN d.id IS NOT NULL THEN 1 ELSE 0 END) AS num_decks,
        IFNULL(SUM(dsum.wins), 0) AS wins,
        IFNULL(SUM(dsum.losses), 0) AS losses,
        IFNULL(SUM(dsum.draws), 0) AS draws,
        SUM(CASE WHEN dsum.wins >= 5 AND dsum.losses = 0 AND d.source_id IN (SELECT id FROM source WHERE name = 'League') THEN 1 ELSE 0 END) AS perfect_runs,
        SUM(CASE WHEN dsum.finish = 1 THEN 1 ELSE 0 END) AS tournament_wins,
        SUM(CASE WHEN dsum.finish <= 8 THEN 1 ELSE 0 END) AS tournament_top8s,
        (CASE WHEN ct.name = 'League' THEN 'league' WHEN ct.name = 'Gatherling' THEN 'tournament' ELSE 'other' END) AS deck_type
    FROM
        deck AS d
    INNER JOIN
        -- Eiliminate maindeck/sideboard double-counting with DISTINCT. See #5493.
        (SELECT DISTINCT card, deck_id FROM deck_card) AS dc ON d.id = dc.deck_id

    LEFT JOIN
        competition AS c ON d.competition_id = c.id
    LEFT JOIN
        competition_series AS cs ON cs.id = c.competition_series_id
    LEFT JOIN
        competition_type AS ct ON ct.id = cs.competition_type_id

    LEFT JOIN deck_cache AS season ON d.id = season.deck_id

    LEFT JOIN
        (
            SELECT
                d.id,
                d.created_date,
                d.finish,
                SUM(CASE WHEN dm.games > IFNULL(odm.games, 0) THEN 1 ELSE 0 END) AS wins, -- IFNULL so we still count byes as wins.
                SUM(CASE WHEN dm.games < odm.games THEN 1 ELSE 0 END) AS losses,
                SUM(CASE WHEN dm.games = odm.games THEN 1 ELSE 0 END) AS draws
            FROM
                deck_match AS dm
            INNER JOIN
                deck_match AS odm ON dm.match_id = odm.match_id AND dm.deck_id <> odm.deck_id
            INNER JOIN
                deck AS d ON d.id = dm.deck_id
            GROUP BY
                d.id
        ) AS dsum ON d.id = dsum.id

    GROUP BY
        card,
        season.season_id,
        ct.name
```

[] (slow_query, 888.1, mysql)

Reported on decksite by mysql-perf

Location Hash: 7c1b78e351d987fc4af4134cd398b330cb417fbe

Labels: decksite

vorpal-buildbot commented 1 year ago

Exceeded slow_query limit (858.3 > 60.0) in mysql: ```

    CREATE TABLE IF NOT EXISTS _new_card_stats (
        name VARCHAR(190) NOT NULL,
        season_id INT NOT NULL,
        num_decks INT NOT NULL,
        wins INT NOT NULL,
        losses INT NOT NULL,
        draws INT NOT NULL,
        perfect_runs INT NOT NULL,
        tournament_wins INT NOT NULL,
        tournament_top8s INT NOT NULL,
        deck_type ENUM('league', 'tournament', 'other') NOT NULL,
        PRIMARY KEY (season_id, name, deck_type),
        FOREIGN KEY (season_id) REFERENCES season (id) ON UPDATE CASCADE ON DELETE CASCADE
    ) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci AS
    SELECT
        card AS name,
        season.season_id,
        SUM(CASE WHEN d.id IS NOT NULL THEN 1 ELSE 0 END) AS num_decks,
        IFNULL(SUM(dsum.wins), 0) AS wins,
        IFNULL(SUM(dsum.losses), 0) AS losses,
        IFNULL(SUM(dsum.draws), 0) AS draws,
        SUM(CASE WHEN dsum.wins >= 5 AND dsum.losses = 0 AND d.source_id IN (SELECT id FROM source WHERE name = 'League') THEN 1 ELSE 0 END) AS perfect_runs,
        SUM(CASE WHEN dsum.finish = 1 THEN 1 ELSE 0 END) AS tournament_wins,
        SUM(CASE WHEN dsum.finish <= 8 THEN 1 ELSE 0 END) AS tournament_top8s,
        (CASE WHEN ct.name = 'League' THEN 'league' WHEN ct.name = 'Gatherling' THEN 'tournament' ELSE 'other' END) AS deck_type
    FROM
        deck AS d
    INNER JOIN
        -- Eiliminate maindeck/sideboard double-counting with DISTINCT. See #5493.
        (SELECT DISTINCT card, deck_id FROM deck_card) AS dc ON d.id = dc.deck_id

    LEFT JOIN
        competition AS c ON d.competition_id = c.id
    LEFT JOIN
        competition_series AS cs ON cs.id = c.competition_series_id
    LEFT JOIN
        competition_type AS ct ON ct.id = cs.competition_type_id

    LEFT JOIN deck_cache AS season ON d.id = season.deck_id

    LEFT JOIN
        (
            SELECT
                d.id,
                d.created_date,
                d.finish,
                SUM(CASE WHEN dm.games > IFNULL(odm.games, 0) THEN 1 ELSE 0 END) AS wins, -- IFNULL so we still count byes as wins.
                SUM(CASE WHEN dm.games < odm.games THEN 1 ELSE 0 END) AS losses,
                SUM(CASE WHEN dm.games = odm.games THEN 1 ELSE 0 END) AS draws
            FROM
                deck_match AS dm
            INNER JOIN
                deck_match AS odm ON dm.match_id = odm.match_id AND dm.deck_id <> odm.deck_id
            INNER JOIN
                deck AS d ON d.id = dm.deck_id
            GROUP BY
                d.id
        ) AS dsum ON d.id = dsum.id

    GROUP BY
        card,
        season.season_id,
        ct.name
```

[] (slow_query, 858.3, mysql)

Reported on decksite by mysql-perf

Location Hash: 7c1b78e351d987fc4af4134cd398b330cb417fbe

Labels: decksite

vorpal-buildbot commented 1 year ago

Exceeded slow_query limit (970.3 > 60.0) in mysql: ```

    CREATE TABLE IF NOT EXISTS _new_card_stats (
        name VARCHAR(190) NOT NULL,
        season_id INT NOT NULL,
        num_decks INT NOT NULL,
        wins INT NOT NULL,
        losses INT NOT NULL,
        draws INT NOT NULL,
        perfect_runs INT NOT NULL,
        tournament_wins INT NOT NULL,
        tournament_top8s INT NOT NULL,
        deck_type ENUM('league', 'tournament', 'other') NOT NULL,
        PRIMARY KEY (season_id, name, deck_type),
        FOREIGN KEY (season_id) REFERENCES season (id) ON UPDATE CASCADE ON DELETE CASCADE
    ) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci AS
    SELECT
        card AS name,
        season.season_id,
        SUM(CASE WHEN d.id IS NOT NULL THEN 1 ELSE 0 END) AS num_decks,
        IFNULL(SUM(dsum.wins), 0) AS wins,
        IFNULL(SUM(dsum.losses), 0) AS losses,
        IFNULL(SUM(dsum.draws), 0) AS draws,
        SUM(CASE WHEN dsum.wins >= 5 AND dsum.losses = 0 AND d.source_id IN (SELECT id FROM source WHERE name = 'League') THEN 1 ELSE 0 END) AS perfect_runs,
        SUM(CASE WHEN dsum.finish = 1 THEN 1 ELSE 0 END) AS tournament_wins,
        SUM(CASE WHEN dsum.finish <= 8 THEN 1 ELSE 0 END) AS tournament_top8s,
        (CASE WHEN ct.name = 'League' THEN 'league' WHEN ct.name = 'Gatherling' THEN 'tournament' ELSE 'other' END) AS deck_type
    FROM
        deck AS d
    INNER JOIN
        -- Eiliminate maindeck/sideboard double-counting with DISTINCT. See #5493.
        (SELECT DISTINCT card, deck_id FROM deck_card) AS dc ON d.id = dc.deck_id

    LEFT JOIN
        competition AS c ON d.competition_id = c.id
    LEFT JOIN
        competition_series AS cs ON cs.id = c.competition_series_id
    LEFT JOIN
        competition_type AS ct ON ct.id = cs.competition_type_id

    LEFT JOIN deck_cache AS season ON d.id = season.deck_id

    LEFT JOIN
        (
            SELECT
                d.id,
                d.created_date,
                d.finish,
                SUM(CASE WHEN dm.games > IFNULL(odm.games, 0) THEN 1 ELSE 0 END) AS wins, -- IFNULL so we still count byes as wins.
                SUM(CASE WHEN dm.games < odm.games THEN 1 ELSE 0 END) AS losses,
                SUM(CASE WHEN dm.games = odm.games THEN 1 ELSE 0 END) AS draws
            FROM
                deck_match AS dm
            INNER JOIN
                deck_match AS odm ON dm.match_id = odm.match_id AND dm.deck_id <> odm.deck_id
            INNER JOIN
                deck AS d ON d.id = dm.deck_id
            GROUP BY
                d.id
        ) AS dsum ON d.id = dsum.id

    GROUP BY
        card,
        season.season_id,
        ct.name
```

[] (slow_query, 970.3, mysql)

Reported on decksite by mysql-perf

Location Hash: 7c1b78e351d987fc4af4134cd398b330cb417fbe

Labels: decksite

vorpal-buildbot commented 1 year ago

Exceeded slow_query limit (875.6 > 60.0) in mysql: ```

    CREATE TABLE IF NOT EXISTS _new_card_stats (
        name VARCHAR(190) NOT NULL,
        season_id INT NOT NULL,
        num_decks INT NOT NULL,
        wins INT NOT NULL,
        losses INT NOT NULL,
        draws INT NOT NULL,
        perfect_runs INT NOT NULL,
        tournament_wins INT NOT NULL,
        tournament_top8s INT NOT NULL,
        deck_type ENUM('league', 'tournament', 'other') NOT NULL,
        PRIMARY KEY (season_id, name, deck_type),
        FOREIGN KEY (season_id) REFERENCES season (id) ON UPDATE CASCADE ON DELETE CASCADE
    ) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci AS
    SELECT
        card AS name,
        season.season_id,
        SUM(CASE WHEN d.id IS NOT NULL THEN 1 ELSE 0 END) AS num_decks,
        IFNULL(SUM(dsum.wins), 0) AS wins,
        IFNULL(SUM(dsum.losses), 0) AS losses,
        IFNULL(SUM(dsum.draws), 0) AS draws,
        SUM(CASE WHEN dsum.wins >= 5 AND dsum.losses = 0 AND d.source_id IN (SELECT id FROM source WHERE name = 'League') THEN 1 ELSE 0 END) AS perfect_runs,
        SUM(CASE WHEN dsum.finish = 1 THEN 1 ELSE 0 END) AS tournament_wins,
        SUM(CASE WHEN dsum.finish <= 8 THEN 1 ELSE 0 END) AS tournament_top8s,
        (CASE WHEN ct.name = 'League' THEN 'league' WHEN ct.name = 'Gatherling' THEN 'tournament' ELSE 'other' END) AS deck_type
    FROM
        deck AS d
    INNER JOIN
        -- Eiliminate maindeck/sideboard double-counting with DISTINCT. See #5493.
        (SELECT DISTINCT card, deck_id FROM deck_card) AS dc ON d.id = dc.deck_id

    LEFT JOIN
        competition AS c ON d.competition_id = c.id
    LEFT JOIN
        competition_series AS cs ON cs.id = c.competition_series_id
    LEFT JOIN
        competition_type AS ct ON ct.id = cs.competition_type_id

    LEFT JOIN deck_cache AS season ON d.id = season.deck_id

    LEFT JOIN
        (
            SELECT
                d.id,
                d.created_date,
                d.finish,
                SUM(CASE WHEN dm.games > IFNULL(odm.games, 0) THEN 1 ELSE 0 END) AS wins, -- IFNULL so we still count byes as wins.
                SUM(CASE WHEN dm.games < odm.games THEN 1 ELSE 0 END) AS losses,
                SUM(CASE WHEN dm.games = odm.games THEN 1 ELSE 0 END) AS draws
            FROM
                deck_match AS dm
            INNER JOIN
                deck_match AS odm ON dm.match_id = odm.match_id AND dm.deck_id <> odm.deck_id
            INNER JOIN
                deck AS d ON d.id = dm.deck_id
            GROUP BY
                d.id
        ) AS dsum ON d.id = dsum.id

    GROUP BY
        card,
        season.season_id,
        ct.name
```

[] (slow_query, 875.6, mysql)

Reported on decksite by mysql-perf

Location Hash: 7c1b78e351d987fc4af4134cd398b330cb417fbe

Labels: decksite

vorpal-buildbot commented 1 year ago

Exceeded slow_query limit (991.9 > 60.0) in mysql: ```

    CREATE TABLE IF NOT EXISTS _new_card_stats (
        name VARCHAR(190) NOT NULL,
        season_id INT NOT NULL,
        num_decks INT NOT NULL,
        wins INT NOT NULL,
        losses INT NOT NULL,
        draws INT NOT NULL,
        perfect_runs INT NOT NULL,
        tournament_wins INT NOT NULL,
        tournament_top8s INT NOT NULL,
        deck_type ENUM('league', 'tournament', 'other') NOT NULL,
        PRIMARY KEY (season_id, name, deck_type),
        FOREIGN KEY (season_id) REFERENCES season (id) ON UPDATE CASCADE ON DELETE CASCADE
    ) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci AS
    SELECT
        card AS name,
        season.season_id,
        SUM(CASE WHEN d.id IS NOT NULL THEN 1 ELSE 0 END) AS num_decks,
        IFNULL(SUM(dsum.wins), 0) AS wins,
        IFNULL(SUM(dsum.losses), 0) AS losses,
        IFNULL(SUM(dsum.draws), 0) AS draws,
        SUM(CASE WHEN dsum.wins >= 5 AND dsum.losses = 0 AND d.source_id IN (SELECT id FROM source WHERE name = 'League') THEN 1 ELSE 0 END) AS perfect_runs,
        SUM(CASE WHEN dsum.finish = 1 THEN 1 ELSE 0 END) AS tournament_wins,
        SUM(CASE WHEN dsum.finish <= 8 THEN 1 ELSE 0 END) AS tournament_top8s,
        (CASE WHEN ct.name = 'League' THEN 'league' WHEN ct.name = 'Gatherling' THEN 'tournament' ELSE 'other' END) AS deck_type
    FROM
        deck AS d
    INNER JOIN
        -- Eiliminate maindeck/sideboard double-counting with DISTINCT. See #5493.
        (SELECT DISTINCT card, deck_id FROM deck_card) AS dc ON d.id = dc.deck_id

    LEFT JOIN
        competition AS c ON d.competition_id = c.id
    LEFT JOIN
        competition_series AS cs ON cs.id = c.competition_series_id
    LEFT JOIN
        competition_type AS ct ON ct.id = cs.competition_type_id

    LEFT JOIN deck_cache AS season ON d.id = season.deck_id

    LEFT JOIN
        (
            SELECT
                d.id,
                d.created_date,
                d.finish,
                SUM(CASE WHEN dm.games > IFNULL(odm.games, 0) THEN 1 ELSE 0 END) AS wins, -- IFNULL so we still count byes as wins.
                SUM(CASE WHEN dm.games < odm.games THEN 1 ELSE 0 END) AS losses,
                SUM(CASE WHEN dm.games = odm.games THEN 1 ELSE 0 END) AS draws
            FROM
                deck_match AS dm
            INNER JOIN
                deck_match AS odm ON dm.match_id = odm.match_id AND dm.deck_id <> odm.deck_id
            INNER JOIN
                deck AS d ON d.id = dm.deck_id
            GROUP BY
                d.id
        ) AS dsum ON d.id = dsum.id

    GROUP BY
        card,
        season.season_id,
        ct.name
```

[] (slow_query, 991.9, mysql)

Reported on decksite by mysql-perf

Location Hash: 7c1b78e351d987fc4af4134cd398b330cb417fbe

Labels: decksite

vorpal-buildbot commented 1 year ago

Exceeded slow_query limit (876.1 > 60.0) in mysql: ```

    CREATE TABLE IF NOT EXISTS _new_card_stats (
        name VARCHAR(190) NOT NULL,
        season_id INT NOT NULL,
        num_decks INT NOT NULL,
        wins INT NOT NULL,
        losses INT NOT NULL,
        draws INT NOT NULL,
        perfect_runs INT NOT NULL,
        tournament_wins INT NOT NULL,
        tournament_top8s INT NOT NULL,
        deck_type ENUM('league', 'tournament', 'other') NOT NULL,
        PRIMARY KEY (season_id, name, deck_type),
        FOREIGN KEY (season_id) REFERENCES season (id) ON UPDATE CASCADE ON DELETE CASCADE
    ) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci AS
    SELECT
        card AS name,
        season.season_id,
        SUM(CASE WHEN d.id IS NOT NULL THEN 1 ELSE 0 END) AS num_decks,
        IFNULL(SUM(dsum.wins), 0) AS wins,
        IFNULL(SUM(dsum.losses), 0) AS losses,
        IFNULL(SUM(dsum.draws), 0) AS draws,
        SUM(CASE WHEN dsum.wins >= 5 AND dsum.losses = 0 AND d.source_id IN (SELECT id FROM source WHERE name = 'League') THEN 1 ELSE 0 END) AS perfect_runs,
        SUM(CASE WHEN dsum.finish = 1 THEN 1 ELSE 0 END) AS tournament_wins,
        SUM(CASE WHEN dsum.finish <= 8 THEN 1 ELSE 0 END) AS tournament_top8s,
        (CASE WHEN ct.name = 'League' THEN 'league' WHEN ct.name = 'Gatherling' THEN 'tournament' ELSE 'other' END) AS deck_type
    FROM
        deck AS d
    INNER JOIN
        -- Eiliminate maindeck/sideboard double-counting with DISTINCT. See #5493.
        (SELECT DISTINCT card, deck_id FROM deck_card) AS dc ON d.id = dc.deck_id

    LEFT JOIN
        competition AS c ON d.competition_id = c.id
    LEFT JOIN
        competition_series AS cs ON cs.id = c.competition_series_id
    LEFT JOIN
        competition_type AS ct ON ct.id = cs.competition_type_id

    LEFT JOIN deck_cache AS season ON d.id = season.deck_id

    LEFT JOIN
        (
            SELECT
                d.id,
                d.created_date,
                d.finish,
                SUM(CASE WHEN dm.games > IFNULL(odm.games, 0) THEN 1 ELSE 0 END) AS wins, -- IFNULL so we still count byes as wins.
                SUM(CASE WHEN dm.games < odm.games THEN 1 ELSE 0 END) AS losses,
                SUM(CASE WHEN dm.games = odm.games THEN 1 ELSE 0 END) AS draws
            FROM
                deck_match AS dm
            INNER JOIN
                deck_match AS odm ON dm.match_id = odm.match_id AND dm.deck_id <> odm.deck_id
            INNER JOIN
                deck AS d ON d.id = dm.deck_id
            GROUP BY
                d.id
        ) AS dsum ON d.id = dsum.id

    GROUP BY
        card,
        season.season_id,
        ct.name
```

[] (slow_query, 876.1, mysql)

Reported on decksite by mysql-perf

Location Hash: 7c1b78e351d987fc4af4134cd398b330cb417fbe

Labels: decksite

vorpal-buildbot commented 1 year ago

Exceeded slow_query limit (830.2 > 60.0) in mysql: ```

    CREATE TABLE IF NOT EXISTS _new_card_stats (
        name VARCHAR(190) NOT NULL,
        season_id INT NOT NULL,
        num_decks INT NOT NULL,
        wins INT NOT NULL,
        losses INT NOT NULL,
        draws INT NOT NULL,
        perfect_runs INT NOT NULL,
        tournament_wins INT NOT NULL,
        tournament_top8s INT NOT NULL,
        deck_type ENUM('league', 'tournament', 'other') NOT NULL,
        PRIMARY KEY (season_id, name, deck_type),
        FOREIGN KEY (season_id) REFERENCES season (id) ON UPDATE CASCADE ON DELETE CASCADE
    ) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci AS
    SELECT
        card AS name,
        season.season_id,
        SUM(CASE WHEN d.id IS NOT NULL THEN 1 ELSE 0 END) AS num_decks,
        IFNULL(SUM(dsum.wins), 0) AS wins,
        IFNULL(SUM(dsum.losses), 0) AS losses,
        IFNULL(SUM(dsum.draws), 0) AS draws,
        SUM(CASE WHEN dsum.wins >= 5 AND dsum.losses = 0 AND d.source_id IN (SELECT id FROM source WHERE name = 'League') THEN 1 ELSE 0 END) AS perfect_runs,
        SUM(CASE WHEN dsum.finish = 1 THEN 1 ELSE 0 END) AS tournament_wins,
        SUM(CASE WHEN dsum.finish <= 8 THEN 1 ELSE 0 END) AS tournament_top8s,
        (CASE WHEN ct.name = 'League' THEN 'league' WHEN ct.name = 'Gatherling' THEN 'tournament' ELSE 'other' END) AS deck_type
    FROM
        deck AS d
    INNER JOIN
        -- Eiliminate maindeck/sideboard double-counting with DISTINCT. See #5493.
        (SELECT DISTINCT card, deck_id FROM deck_card) AS dc ON d.id = dc.deck_id

    LEFT JOIN
        competition AS c ON d.competition_id = c.id
    LEFT JOIN
        competition_series AS cs ON cs.id = c.competition_series_id
    LEFT JOIN
        competition_type AS ct ON ct.id = cs.competition_type_id

    LEFT JOIN deck_cache AS season ON d.id = season.deck_id

    LEFT JOIN
        (
            SELECT
                d.id,
                d.created_date,
                d.finish,
                SUM(CASE WHEN dm.games > IFNULL(odm.games, 0) THEN 1 ELSE 0 END) AS wins, -- IFNULL so we still count byes as wins.
                SUM(CASE WHEN dm.games < odm.games THEN 1 ELSE 0 END) AS losses,
                SUM(CASE WHEN dm.games = odm.games THEN 1 ELSE 0 END) AS draws
            FROM
                deck_match AS dm
            INNER JOIN
                deck_match AS odm ON dm.match_id = odm.match_id AND dm.deck_id <> odm.deck_id
            INNER JOIN
                deck AS d ON d.id = dm.deck_id
            GROUP BY
                d.id
        ) AS dsum ON d.id = dsum.id

    GROUP BY
        card,
        season.season_id,
        ct.name
```

[] (slow_query, 830.2, mysql)

Reported on decksite by mysql-perf

Location Hash: 7c1b78e351d987fc4af4134cd398b330cb417fbe

Labels: decksite

vorpal-buildbot commented 1 year ago

Exceeded slow_query limit (819.9 > 60.0) in mysql: ```

    CREATE TABLE IF NOT EXISTS _new_card_stats (
        name VARCHAR(190) NOT NULL,
        season_id INT NOT NULL,
        num_decks INT NOT NULL,
        wins INT NOT NULL,
        losses INT NOT NULL,
        draws INT NOT NULL,
        perfect_runs INT NOT NULL,
        tournament_wins INT NOT NULL,
        tournament_top8s INT NOT NULL,
        deck_type ENUM('league', 'tournament', 'other') NOT NULL,
        PRIMARY KEY (season_id, name, deck_type),
        FOREIGN KEY (season_id) REFERENCES season (id) ON UPDATE CASCADE ON DELETE CASCADE
    ) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci AS
    SELECT
        card AS name,
        season.season_id,
        SUM(CASE WHEN d.id IS NOT NULL THEN 1 ELSE 0 END) AS num_decks,
        IFNULL(SUM(dsum.wins), 0) AS wins,
        IFNULL(SUM(dsum.losses), 0) AS losses,
        IFNULL(SUM(dsum.draws), 0) AS draws,
        SUM(CASE WHEN dsum.wins >= 5 AND dsum.losses = 0 AND d.source_id IN (SELECT id FROM source WHERE name = 'League') THEN 1 ELSE 0 END) AS perfect_runs,
        SUM(CASE WHEN dsum.finish = 1 THEN 1 ELSE 0 END) AS tournament_wins,
        SUM(CASE WHEN dsum.finish <= 8 THEN 1 ELSE 0 END) AS tournament_top8s,
        (CASE WHEN ct.name = 'League' THEN 'league' WHEN ct.name = 'Gatherling' THEN 'tournament' ELSE 'other' END) AS deck_type
    FROM
        deck AS d
    INNER JOIN
        -- Eiliminate maindeck/sideboard double-counting with DISTINCT. See #5493.
        (SELECT DISTINCT card, deck_id FROM deck_card) AS dc ON d.id = dc.deck_id

    LEFT JOIN
        competition AS c ON d.competition_id = c.id
    LEFT JOIN
        competition_series AS cs ON cs.id = c.competition_series_id
    LEFT JOIN
        competition_type AS ct ON ct.id = cs.competition_type_id

    LEFT JOIN deck_cache AS season ON d.id = season.deck_id

    LEFT JOIN
        (
            SELECT
                d.id,
                d.created_date,
                d.finish,
                SUM(CASE WHEN dm.games > IFNULL(odm.games, 0) THEN 1 ELSE 0 END) AS wins, -- IFNULL so we still count byes as wins.
                SUM(CASE WHEN dm.games < odm.games THEN 1 ELSE 0 END) AS losses,
                SUM(CASE WHEN dm.games = odm.games THEN 1 ELSE 0 END) AS draws
            FROM
                deck_match AS dm
            INNER JOIN
                deck_match AS odm ON dm.match_id = odm.match_id AND dm.deck_id <> odm.deck_id
            INNER JOIN
                deck AS d ON d.id = dm.deck_id
            GROUP BY
                d.id
        ) AS dsum ON d.id = dsum.id

    GROUP BY
        card,
        season.season_id,
        ct.name
```

[] (slow_query, 819.9, mysql)

Reported on decksite by mysql-perf

Location Hash: 7c1b78e351d987fc4af4134cd398b330cb417fbe

Labels: decksite

vorpal-buildbot commented 1 year ago

Exceeded slow_query limit (839.5 > 60.0) in mysql: ```

    CREATE TABLE IF NOT EXISTS _new_card_stats (
        name VARCHAR(190) NOT NULL,
        season_id INT NOT NULL,
        num_decks INT NOT NULL,
        wins INT NOT NULL,
        losses INT NOT NULL,
        draws INT NOT NULL,
        perfect_runs INT NOT NULL,
        tournament_wins INT NOT NULL,
        tournament_top8s INT NOT NULL,
        deck_type ENUM('league', 'tournament', 'other') NOT NULL,
        PRIMARY KEY (season_id, name, deck_type),
        FOREIGN KEY (season_id) REFERENCES season (id) ON UPDATE CASCADE ON DELETE CASCADE
    ) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci AS
    SELECT
        card AS name,
        season.season_id,
        SUM(CASE WHEN d.id IS NOT NULL THEN 1 ELSE 0 END) AS num_decks,
        IFNULL(SUM(dsum.wins), 0) AS wins,
        IFNULL(SUM(dsum.losses), 0) AS losses,
        IFNULL(SUM(dsum.draws), 0) AS draws,
        SUM(CASE WHEN dsum.wins >= 5 AND dsum.losses = 0 AND d.source_id IN (SELECT id FROM source WHERE name = 'League') THEN 1 ELSE 0 END) AS perfect_runs,
        SUM(CASE WHEN dsum.finish = 1 THEN 1 ELSE 0 END) AS tournament_wins,
        SUM(CASE WHEN dsum.finish <= 8 THEN 1 ELSE 0 END) AS tournament_top8s,
        (CASE WHEN ct.name = 'League' THEN 'league' WHEN ct.name = 'Gatherling' THEN 'tournament' ELSE 'other' END) AS deck_type
    FROM
        deck AS d
    INNER JOIN
        -- Eiliminate maindeck/sideboard double-counting with DISTINCT. See #5493.
        (SELECT DISTINCT card, deck_id FROM deck_card) AS dc ON d.id = dc.deck_id

    LEFT JOIN
        competition AS c ON d.competition_id = c.id
    LEFT JOIN
        competition_series AS cs ON cs.id = c.competition_series_id
    LEFT JOIN
        competition_type AS ct ON ct.id = cs.competition_type_id

    LEFT JOIN deck_cache AS season ON d.id = season.deck_id

    LEFT JOIN
        (
            SELECT
                d.id,
                d.created_date,
                d.finish,
                SUM(CASE WHEN dm.games > IFNULL(odm.games, 0) THEN 1 ELSE 0 END) AS wins, -- IFNULL so we still count byes as wins.
                SUM(CASE WHEN dm.games < odm.games THEN 1 ELSE 0 END) AS losses,
                SUM(CASE WHEN dm.games = odm.games THEN 1 ELSE 0 END) AS draws
            FROM
                deck_match AS dm
            INNER JOIN
                deck_match AS odm ON dm.match_id = odm.match_id AND dm.deck_id <> odm.deck_id
            INNER JOIN
                deck AS d ON d.id = dm.deck_id
            GROUP BY
                d.id
        ) AS dsum ON d.id = dsum.id

    GROUP BY
        card,
        season.season_id,
        ct.name
```

[] (slow_query, 839.5, mysql)

Reported on decksite by mysql-perf

Location Hash: 7c1b78e351d987fc4af4134cd398b330cb417fbe

Labels: decksite