Exceeded slow_query limit (143.0 > 60.0) in mysql: DROP TABLE IF EXISTS _new_card_stats

[vorpal-buildbot]

[] (slow_query, 143.0, mysql)

Reported on decksite by mysql-perf

Location Hash: 0065e3617e059afdebdf15333f1c8c04840aa205

Request Data

Request Method: GET
Path: /cards/Walk with the Ancestors/tournament/?locale=da
Cookies: ImmutableMultiDict([])
Endpoint: card
View Args: {'name': 'Walk with the Ancestors', 'deck_type': 'tournament'}
Person: logged_out
Referrer: None
Request Data: {}
Host: pennydreadfulmagic.com
Cf-Ray: 9058bc2a5e57d6a3-SJC
X-Forwarded-For: 44.213.36.21, 172.71.159.47
User-Agent: Mozilla/5.0 AppleWebKit/537.36 (KHTML, like Gecko; compatible; Amazonbot/0.1; +https://developer.amazon.com/support/amazonbot) Chrome/119.0.6045.214 Safari/537.36
Accept-Encoding: gzip, br
Cf-Ipcountry: US
X-Forwarded-Proto: https
Cf-Connecting-Ip: 44.213.36.21
Cf-Visitor: {"scheme":"https"}
Cdn-Loop: cloudflare; loops=1
X-Forwarded-Host: pennydreadfulmagic.com
X-Forwarded-Server: pennydreadfulmagic.com
Connection: Keep-Alive

[vorpal-buildbot]

Exceeded slow_query limit (226.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
        -- Eliminate maindeck/sideboard double-counting with DISTINCT. See #5493.
        (SELECT DISTINCT card, deck_id FROM deck_card) AS dc ON d.id = dc.deck_id
    
    LEFT JOIN
        competition AS c ON d.competition_id = c.id
    LEFT JOIN
        competition_series AS cs ON cs.id = c.competition_series_id
    LEFT JOIN
        competition_type AS ct ON ct.id = cs.competition_type_id

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

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

[] (slow_query, 226.3, mysql)

Reported on decksite by mysql-perf

Location Hash: 0065e3617e059afdebdf15333f1c8c04840aa205

Request Data

Request Method: GET
Path: /cards/Walk with the Ancestors/tournament/?locale=da
Cookies: ImmutableMultiDict([])
Endpoint: card
View Args: {'name': 'Walk with the Ancestors', 'deck_type': 'tournament'}
Person: logged_out
Referrer: None
Request Data: {}
Host: pennydreadfulmagic.com
Cf-Ray: 9058bc2a5e57d6a3-SJC
X-Forwarded-For: 44.213.36.21, 172.71.159.47
User-Agent: Mozilla/5.0 AppleWebKit/537.36 (KHTML, like Gecko; compatible; Amazonbot/0.1; +https://developer.amazon.com/support/amazonbot) Chrome/119.0.6045.214 Safari/537.36
Accept-Encoding: gzip, br
Cf-Ipcountry: US
X-Forwarded-Proto: https
Cf-Connecting-Ip: 44.213.36.21
Cf-Visitor: {"scheme":"https"}
Cdn-Loop: cloudflare; loops=1
X-Forwarded-Host: pennydreadfulmagic.com
X-Forwarded-Server: pennydreadfulmagic.com
Connection: Keep-Alive

Labels: decksite

[vorpal-buildbot]

Exceeded slow_query limit (111.7 > 60.0) in mysql: RENAME TABLE _card_stats TO _old_card_stats, _new_card_stats TO _card_stats

[] (slow_query, 111.7, mysql)

Reported on decksite by mysql-perf

Location Hash: 0065e3617e059afdebdf15333f1c8c04840aa205

Request Data

Request Method: GET
Path: /cards/Walk with the Ancestors/tournament/?locale=da
Cookies: ImmutableMultiDict([])
Endpoint: card
View Args: {'name': 'Walk with the Ancestors', 'deck_type': 'tournament'}
Person: logged_out
Referrer: None
Request Data: {}
Host: pennydreadfulmagic.com
Cf-Ray: 9058bc2a5e57d6a3-SJC
X-Forwarded-For: 44.213.36.21, 172.71.159.47
User-Agent: Mozilla/5.0 AppleWebKit/537.36 (KHTML, like Gecko; compatible; Amazonbot/0.1; +https://developer.amazon.com/support/amazonbot) Chrome/119.0.6045.214 Safari/537.36
Accept-Encoding: gzip, br
Cf-Ipcountry: US
X-Forwarded-Proto: https
Cf-Connecting-Ip: 44.213.36.21
Cf-Visitor: {"scheme":"https"}
Cdn-Loop: cloudflare; loops=1
X-Forwarded-Host: pennydreadfulmagic.com
X-Forwarded-Server: pennydreadfulmagic.com
Connection: Keep-Alive

Labels: decksite