search

PennyDreadfulMTG / perf-reports

2 stars 2 forks source link

Exceeded slow_query limit (92.0 > 1.0) in mysql: ``` #63259

Open vorpal-buildbot opened 5 months ago

vorpal-buildbot commented 5 months 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, 92.0, mysql)

Reported on decksite by mysql-perf

Location Hash: e5fcac04870b9e803b529ce13fbae01aa85dda14

vorpal-buildbot commented 5 months ago

Exceeded slow_query limit (76.6 > 2.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, 76.6, mysql)

Reported on decksite by mysql-perf

Location Hash: e5fcac04870b9e803b529ce13fbae01aa85dda14

Labels: decksite

vorpal-buildbot commented 5 months ago

Exceeded slow_query limit (83.7 > 2.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, 83.7, mysql)

Reported on decksite by mysql-perf

Location Hash: e5fcac04870b9e803b529ce13fbae01aa85dda14

Labels: decksite

vorpal-buildbot commented 5 months ago

Exceeded slow_query limit (91.5 > 5.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, 91.5, mysql)

Reported on decksite by mysql-perf

Location Hash: e5fcac04870b9e803b529ce13fbae01aa85dda14

Labels: decksite

vorpal-buildbot commented 5 months ago

Exceeded slow_query limit (74.7 > 5.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, 74.7, mysql)

Reported on decksite by mysql-perf

Location Hash: e5fcac04870b9e803b529ce13fbae01aa85dda14

Labels: decksite

vorpal-buildbot commented 5 months ago

Exceeded slow_query limit (83.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, 83.1, mysql)

Reported on decksite by mysql-perf

Location Hash: e5fcac04870b9e803b529ce13fbae01aa85dda14

Labels: decksite

vorpal-buildbot commented 4 months ago

Exceeded slow_query limit (79.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, 79.8, mysql)

Reported on decksite by mysql-perf

Location Hash: e5fcac04870b9e803b529ce13fbae01aa85dda14

Labels: decksite

vorpal-buildbot commented 4 months ago

Exceeded slow_query limit (85.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, 85.6, mysql)

Reported on decksite by mysql-perf

Location Hash: e5fcac04870b9e803b529ce13fbae01aa85dda14

Labels: decksite

vorpal-buildbot commented 4 months ago

Exceeded slow_query limit (83.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, 83.5, mysql)

Reported on decksite by mysql-perf

Location Hash: e5fcac04870b9e803b529ce13fbae01aa85dda14

Labels: decksite

vorpal-buildbot commented 4 months ago

Exceeded slow_query limit (81.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, 81.6, mysql)

Reported on decksite by mysql-perf

Location Hash: e5fcac04870b9e803b529ce13fbae01aa85dda14

Labels: decksite

vorpal-buildbot commented 4 months ago

Exceeded slow_query limit (78.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, 78.6, mysql)

Reported on decksite by mysql-perf

Location Hash: e5fcac04870b9e803b529ce13fbae01aa85dda14

Labels: decksite

vorpal-buildbot commented 4 months ago

Exceeded slow_query limit (81.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, 81.1, mysql)

Reported on decksite by mysql-perf

Location Hash: e5fcac04870b9e803b529ce13fbae01aa85dda14

Labels: decksite

vorpal-buildbot commented 4 months ago

Exceeded slow_query limit (79.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, 79.6, mysql)

Reported on decksite by mysql-perf

Location Hash: e5fcac04870b9e803b529ce13fbae01aa85dda14

Labels: decksite

vorpal-buildbot commented 4 months ago

Exceeded slow_query limit (79.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, 79.1, mysql)

Reported on decksite by mysql-perf

Location Hash: e5fcac04870b9e803b529ce13fbae01aa85dda14

Labels: decksite

vorpal-buildbot commented 4 months ago

Exceeded slow_query limit (75.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, 75.6, mysql)

Reported on decksite by mysql-perf

Location Hash: e5fcac04870b9e803b529ce13fbae01aa85dda14

Labels: decksite

vorpal-buildbot commented 4 months ago

Exceeded slow_query limit (73.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, 73.9, mysql)

Reported on decksite by mysql-perf

Location Hash: e5fcac04870b9e803b529ce13fbae01aa85dda14

Labels: decksite

vorpal-buildbot commented 4 months ago

Exceeded slow_query limit (77.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, 77.6, mysql)

Reported on decksite by mysql-perf

Location Hash: e5fcac04870b9e803b529ce13fbae01aa85dda14

Labels: decksite

vorpal-buildbot commented 4 months ago

Exceeded slow_query limit (72.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, 72.0, mysql)

Reported on decksite by mysql-perf

Location Hash: e5fcac04870b9e803b529ce13fbae01aa85dda14

Labels: decksite

vorpal-buildbot commented 4 months ago

Exceeded slow_query limit (78.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, 78.0, mysql)

Reported on decksite by mysql-perf

Location Hash: e5fcac04870b9e803b529ce13fbae01aa85dda14

Labels: decksite

vorpal-buildbot commented 4 months ago

Exceeded slow_query limit (77.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, 77.7, mysql)

Reported on decksite by mysql-perf

Location Hash: e5fcac04870b9e803b529ce13fbae01aa85dda14

Labels: decksite

vorpal-buildbot commented 4 months ago

Exceeded slow_query limit (81.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, 81.0, mysql)

Reported on decksite by mysql-perf

Location Hash: e5fcac04870b9e803b529ce13fbae01aa85dda14

Labels: decksite

vorpal-buildbot commented 4 months ago

Exceeded slow_query limit (81.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, 81.3, mysql)

Reported on decksite by mysql-perf

Location Hash: e5fcac04870b9e803b529ce13fbae01aa85dda14

Labels: decksite

vorpal-buildbot commented 4 months ago

Exceeded slow_query limit (81.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, 81.0, mysql)

Reported on decksite by mysql-perf

Location Hash: e5fcac04870b9e803b529ce13fbae01aa85dda14

Labels: decksite

vorpal-buildbot commented 4 months ago

Exceeded slow_query limit (77.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, 77.9, mysql)

Reported on decksite by mysql-perf

Location Hash: e5fcac04870b9e803b529ce13fbae01aa85dda14

Labels: decksite

vorpal-buildbot commented 4 months ago

Exceeded slow_query limit (81.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, 81.9, mysql)

Reported on decksite by mysql-perf

Location Hash: e5fcac04870b9e803b529ce13fbae01aa85dda14

Labels: decksite

vorpal-buildbot commented 4 months ago

Exceeded slow_query limit (75.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, 75.5, mysql)

Reported on decksite by mysql-perf

Location Hash: e5fcac04870b9e803b529ce13fbae01aa85dda14

Labels: decksite

vorpal-buildbot commented 4 months ago

Exceeded slow_query limit (82.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, 82.4, mysql)

Reported on decksite by mysql-perf

Location Hash: e5fcac04870b9e803b529ce13fbae01aa85dda14

Labels: decksite

vorpal-buildbot commented 4 months ago

Exceeded slow_query limit (86.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, 86.1, mysql)

Reported on decksite by mysql-perf

Location Hash: e5fcac04870b9e803b529ce13fbae01aa85dda14

Labels: decksite

vorpal-buildbot commented 4 months ago

Exceeded slow_query limit (81.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, 81.0, mysql)

Reported on decksite by mysql-perf

Location Hash: e5fcac04870b9e803b529ce13fbae01aa85dda14

Labels: decksite

vorpal-buildbot commented 4 months ago

Exceeded slow_query limit (84.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, 84.9, mysql)

Reported on decksite by mysql-perf

Location Hash: e5fcac04870b9e803b529ce13fbae01aa85dda14

Labels: decksite

vorpal-buildbot commented 4 months ago

Exceeded slow_query limit (83.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, 83.9, mysql)

Reported on decksite by mysql-perf

Location Hash: e5fcac04870b9e803b529ce13fbae01aa85dda14

Labels: decksite

vorpal-buildbot commented 4 months ago

Exceeded slow_query limit (81.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, 81.6, mysql)

Reported on decksite by mysql-perf

Location Hash: e5fcac04870b9e803b529ce13fbae01aa85dda14

Labels: decksite

vorpal-buildbot commented 4 months ago

Exceeded slow_query limit (79.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, 79.9, mysql)

Reported on decksite by mysql-perf

Location Hash: e5fcac04870b9e803b529ce13fbae01aa85dda14

Labels: decksite

vorpal-buildbot commented 4 months ago

Exceeded slow_query limit (79.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, 79.8, mysql)

Reported on decksite by mysql-perf

Location Hash: e5fcac04870b9e803b529ce13fbae01aa85dda14

Labels: decksite

vorpal-buildbot commented 4 months ago

Exceeded slow_query limit (85.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, 85.0, mysql)

Reported on decksite by mysql-perf

Location Hash: e5fcac04870b9e803b529ce13fbae01aa85dda14

Labels: decksite