Exceeded slow_query limit (30.2 > 1.0) in mysql: ```

PennyDreadfulMTG / perf-reports

2 stars 7 forks source link

Exceeded slow_query limit (30.2 > 1.0) in mysql: ``` #63256

Open vorpal-buildbot opened 3 months ago

vorpal-buildbot commented 3 months ago

    CREATE TABLE IF NOT EXISTS _new_head_to_head_stats (
        person_id INT NOT NULL,
        opponent_id INT NOT NULL,
        season_id INT NOT NULL,
        num_matches INT NOT NULL,
        wins INT NOT NULL,
        losses INT NOT NULL,
        draws INT NOT NULL,
        PRIMARY KEY (season_id, person_id, opponent_id),
        FOREIGN KEY (season_id) REFERENCES season (id) ON UPDATE CASCADE ON DELETE CASCADE,
        FOREIGN KEY (person_id) REFERENCES person (id) ON UPDATE CASCADE ON DELETE CASCADE,
        FOREIGN KEY (opponent_id) REFERENCES person (id) ON UPDATE CASCADE ON DELETE CASCADE
    ) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci AS
    SELECT
        p.id AS person_id,
        opp.id AS opponent_id,
        season.season_id,
        COUNT(p.id) AS num_matches,
        SUM(CASE WHEN dm.games > odm.games THEN 1 ELSE 0 END) 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
        person AS p
    INNER JOIN
        deck AS d ON p.id = d.person_id
    INNER JOIN
        deck_match AS dm ON dm.deck_id = d.id
    INNER JOIN
        deck_match AS odm ON dm.match_id = odm.match_id AND dm.deck_id <> IFNULL(odm.deck_id, 0)
    INNER JOIN
        deck AS od ON odm.deck_id = od.id
    INNER JOIN
        person AS opp ON od.person_id = opp.id
    LEFT JOIN deck_cache AS season ON d.id = season.deck_id
    GROUP BY
        p.id,
        opp.id,
        season.season_id
```

[] (slow_query, 30.2, mysql)

Reported on decksite by mysql-perf

Location Hash: 7e0205ab52a9eb7599b6b9befacfb5abd2dea49a

vorpal-buildbot commented 3 months ago

Exceeded slow_query limit (25.6 > 2.0) in mysql: ```

    CREATE TABLE IF NOT EXISTS _new_head_to_head_stats (
        person_id INT NOT NULL,
        opponent_id INT NOT NULL,
        season_id INT NOT NULL,
        num_matches INT NOT NULL,
        wins INT NOT NULL,
        losses INT NOT NULL,
        draws INT NOT NULL,
        PRIMARY KEY (season_id, person_id, opponent_id),
        FOREIGN KEY (season_id) REFERENCES season (id) ON UPDATE CASCADE ON DELETE CASCADE,
        FOREIGN KEY (person_id) REFERENCES person (id) ON UPDATE CASCADE ON DELETE CASCADE,
        FOREIGN KEY (opponent_id) REFERENCES person (id) ON UPDATE CASCADE ON DELETE CASCADE
    ) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci AS
    SELECT
        p.id AS person_id,
        opp.id AS opponent_id,
        season.season_id,
        COUNT(p.id) AS num_matches,
        SUM(CASE WHEN dm.games > odm.games THEN 1 ELSE 0 END) 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
        person AS p
    INNER JOIN
        deck AS d ON p.id = d.person_id
    INNER JOIN
        deck_match AS dm ON dm.deck_id = d.id
    INNER JOIN
        deck_match AS odm ON dm.match_id = odm.match_id AND dm.deck_id <> IFNULL(odm.deck_id, 0)
    INNER JOIN
        deck AS od ON odm.deck_id = od.id
    INNER JOIN
        person AS opp ON od.person_id = opp.id
    LEFT JOIN deck_cache AS season ON d.id = season.deck_id
    GROUP BY
        p.id,
        opp.id,
        season.season_id
```

[] (slow_query, 25.6, mysql)

Reported on decksite by mysql-perf

Location Hash: 7e0205ab52a9eb7599b6b9befacfb5abd2dea49a

Labels: decksite

vorpal-buildbot commented 3 months ago

Exceeded slow_query limit (25.4 > 2.0) in mysql: ```

    CREATE TABLE IF NOT EXISTS _new_head_to_head_stats (
        person_id INT NOT NULL,
        opponent_id INT NOT NULL,
        season_id INT NOT NULL,
        num_matches INT NOT NULL,
        wins INT NOT NULL,
        losses INT NOT NULL,
        draws INT NOT NULL,
        PRIMARY KEY (season_id, person_id, opponent_id),
        FOREIGN KEY (season_id) REFERENCES season (id) ON UPDATE CASCADE ON DELETE CASCADE,
        FOREIGN KEY (person_id) REFERENCES person (id) ON UPDATE CASCADE ON DELETE CASCADE,
        FOREIGN KEY (opponent_id) REFERENCES person (id) ON UPDATE CASCADE ON DELETE CASCADE
    ) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci AS
    SELECT
        p.id AS person_id,
        opp.id AS opponent_id,
        season.season_id,
        COUNT(p.id) AS num_matches,
        SUM(CASE WHEN dm.games > odm.games THEN 1 ELSE 0 END) 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
        person AS p
    INNER JOIN
        deck AS d ON p.id = d.person_id
    INNER JOIN
        deck_match AS dm ON dm.deck_id = d.id
    INNER JOIN
        deck_match AS odm ON dm.match_id = odm.match_id AND dm.deck_id <> IFNULL(odm.deck_id, 0)
    INNER JOIN
        deck AS od ON odm.deck_id = od.id
    INNER JOIN
        person AS opp ON od.person_id = opp.id
    LEFT JOIN deck_cache AS season ON d.id = season.deck_id
    GROUP BY
        p.id,
        opp.id,
        season.season_id
```

[] (slow_query, 25.4, mysql)

Reported on decksite by mysql-perf

Location Hash: 7e0205ab52a9eb7599b6b9befacfb5abd2dea49a

Labels: decksite

vorpal-buildbot commented 3 months ago

Exceeded slow_query limit (17.9 > 5.0) in mysql: ```

    CREATE TABLE IF NOT EXISTS _new_head_to_head_stats (
        person_id INT NOT NULL,
        opponent_id INT NOT NULL,
        season_id INT NOT NULL,
        num_matches INT NOT NULL,
        wins INT NOT NULL,
        losses INT NOT NULL,
        draws INT NOT NULL,
        PRIMARY KEY (season_id, person_id, opponent_id),
        FOREIGN KEY (season_id) REFERENCES season (id) ON UPDATE CASCADE ON DELETE CASCADE,
        FOREIGN KEY (person_id) REFERENCES person (id) ON UPDATE CASCADE ON DELETE CASCADE,
        FOREIGN KEY (opponent_id) REFERENCES person (id) ON UPDATE CASCADE ON DELETE CASCADE
    ) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci AS
    SELECT
        p.id AS person_id,
        opp.id AS opponent_id,
        season.season_id,
        COUNT(p.id) AS num_matches,
        SUM(CASE WHEN dm.games > odm.games THEN 1 ELSE 0 END) 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
        person AS p
    INNER JOIN
        deck AS d ON p.id = d.person_id
    INNER JOIN
        deck_match AS dm ON dm.deck_id = d.id
    INNER JOIN
        deck_match AS odm ON dm.match_id = odm.match_id AND dm.deck_id <> IFNULL(odm.deck_id, 0)
    INNER JOIN
        deck AS od ON odm.deck_id = od.id
    INNER JOIN
        person AS opp ON od.person_id = opp.id
    LEFT JOIN deck_cache AS season ON d.id = season.deck_id
    GROUP BY
        p.id,
        opp.id,
        season.season_id
```

[] (slow_query, 17.9, mysql)

Reported on decksite by mysql-perf

Location Hash: 7e0205ab52a9eb7599b6b9befacfb5abd2dea49a

Labels: decksite

vorpal-buildbot commented 3 months ago

Exceeded slow_query limit (23.8 > 5.0) in mysql: ```

    CREATE TABLE IF NOT EXISTS _new_head_to_head_stats (
        person_id INT NOT NULL,
        opponent_id INT NOT NULL,
        season_id INT NOT NULL,
        num_matches INT NOT NULL,
        wins INT NOT NULL,
        losses INT NOT NULL,
        draws INT NOT NULL,
        PRIMARY KEY (season_id, person_id, opponent_id),
        FOREIGN KEY (season_id) REFERENCES season (id) ON UPDATE CASCADE ON DELETE CASCADE,
        FOREIGN KEY (person_id) REFERENCES person (id) ON UPDATE CASCADE ON DELETE CASCADE,
        FOREIGN KEY (opponent_id) REFERENCES person (id) ON UPDATE CASCADE ON DELETE CASCADE
    ) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci AS
    SELECT
        p.id AS person_id,
        opp.id AS opponent_id,
        season.season_id,
        COUNT(p.id) AS num_matches,
        SUM(CASE WHEN dm.games > odm.games THEN 1 ELSE 0 END) 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
        person AS p
    INNER JOIN
        deck AS d ON p.id = d.person_id
    INNER JOIN
        deck_match AS dm ON dm.deck_id = d.id
    INNER JOIN
        deck_match AS odm ON dm.match_id = odm.match_id AND dm.deck_id <> IFNULL(odm.deck_id, 0)
    INNER JOIN
        deck AS od ON odm.deck_id = od.id
    INNER JOIN
        person AS opp ON od.person_id = opp.id
    LEFT JOIN deck_cache AS season ON d.id = season.deck_id
    GROUP BY
        p.id,
        opp.id,
        season.season_id
```

[] (slow_query, 23.8, mysql)

Reported on decksite by mysql-perf

Location Hash: 7e0205ab52a9eb7599b6b9befacfb5abd2dea49a

Labels: decksite