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

PennyDreadfulMTG / perf-reports

2 stars 7 forks source link

Exceeded slow_query limit (23.9 > 1.0) in mysql: ``` #63277

Open vorpal-buildbot opened 3 months ago

vorpal-buildbot commented 3 months ago

    UPDATE
        deck_cache AS dc
    INNER JOIN
        (
            SELECT
                d.id AS deck_id,
                ROUND(SUM(CASE WHEN dm.games > odm.games THEN 1 ELSE 0 END) / NULLIF((SUM(CASE WHEN dm.games <> odm.games THEN 1 ELSE 0 END)), 0), 2) AS omw
            FROM
                deck AS d
            INNER JOIN
                deck_match AS my_dm ON my_dm.deck_id = d.id
            LEFT JOIN
                deck_match AS my_odm ON my_odm.match_id = my_dm.match_id AND my_odm.deck_id <> d.id
            INNER JOIN
                deck AS od ON od.id = my_odm.deck_id
            INNER JOIN
                deck_match AS dm ON dm.deck_id = od.id
            LEFT JOIN
                deck_match AS odm ON odm.match_id = dm.match_id AND odm.deck_id <> dm.deck_id
            INNER JOIN
                `match` AS m ON m.id = dm.match_id
            WHERE
                d.competition_id NOT IN (SELECT id FROM competition WHERE end_date > UNIX_TIMESTAMP(NOW()))
            GROUP BY
                d.id
        ) AS ds ON dc.deck_id = ds.deck_id
    SET
        dc.omw = ds.omw
```

[] (slow_query, 23.9, mysql)

Reported on decksite by mysql-perf

Location Hash: d98672f3e9183e095b86f9057263e66987f5a47e

vorpal-buildbot commented 3 months ago

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

    UPDATE
        deck_cache AS dc
    INNER JOIN
        (
            SELECT
                d.id AS deck_id,
                ROUND(SUM(CASE WHEN dm.games > odm.games THEN 1 ELSE 0 END) / NULLIF((SUM(CASE WHEN dm.games <> odm.games THEN 1 ELSE 0 END)), 0), 2) AS omw
            FROM
                deck AS d
            INNER JOIN
                deck_match AS my_dm ON my_dm.deck_id = d.id
            LEFT JOIN
                deck_match AS my_odm ON my_odm.match_id = my_dm.match_id AND my_odm.deck_id <> d.id
            INNER JOIN
                deck AS od ON od.id = my_odm.deck_id
            INNER JOIN
                deck_match AS dm ON dm.deck_id = od.id
            LEFT JOIN
                deck_match AS odm ON odm.match_id = dm.match_id AND odm.deck_id <> dm.deck_id
            INNER JOIN
                `match` AS m ON m.id = dm.match_id
            WHERE
                d.competition_id NOT IN (SELECT id FROM competition WHERE end_date > UNIX_TIMESTAMP(NOW()))
            GROUP BY
                d.id
        ) AS ds ON dc.deck_id = ds.deck_id
    SET
        dc.omw = ds.omw
```

[] (slow_query, 22.4, mysql)

Reported on decksite by mysql-perf

Location Hash: d98672f3e9183e095b86f9057263e66987f5a47e

Labels: decksite

vorpal-buildbot commented 3 months ago

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

    UPDATE
        deck_cache AS dc
    INNER JOIN
        (
            SELECT
                d.id AS deck_id,
                ROUND(SUM(CASE WHEN dm.games > odm.games THEN 1 ELSE 0 END) / NULLIF((SUM(CASE WHEN dm.games <> odm.games THEN 1 ELSE 0 END)), 0), 2) AS omw
            FROM
                deck AS d
            INNER JOIN
                deck_match AS my_dm ON my_dm.deck_id = d.id
            LEFT JOIN
                deck_match AS my_odm ON my_odm.match_id = my_dm.match_id AND my_odm.deck_id <> d.id
            INNER JOIN
                deck AS od ON od.id = my_odm.deck_id
            INNER JOIN
                deck_match AS dm ON dm.deck_id = od.id
            LEFT JOIN
                deck_match AS odm ON odm.match_id = dm.match_id AND odm.deck_id <> dm.deck_id
            INNER JOIN
                `match` AS m ON m.id = dm.match_id
            WHERE
                d.competition_id NOT IN (SELECT id FROM competition WHERE end_date > UNIX_TIMESTAMP(NOW()))
            GROUP BY
                d.id
        ) AS ds ON dc.deck_id = ds.deck_id
    SET
        dc.omw = ds.omw
```

[] (slow_query, 25.3, mysql)

Reported on decksite by mysql-perf

Location Hash: d98672f3e9183e095b86f9057263e66987f5a47e

Labels: decksite

vorpal-buildbot commented 3 months ago

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

    UPDATE
        deck_cache AS dc
    INNER JOIN
        (
            SELECT
                d.id AS deck_id,
                ROUND(SUM(CASE WHEN dm.games > odm.games THEN 1 ELSE 0 END) / NULLIF((SUM(CASE WHEN dm.games <> odm.games THEN 1 ELSE 0 END)), 0), 2) AS omw
            FROM
                deck AS d
            INNER JOIN
                deck_match AS my_dm ON my_dm.deck_id = d.id
            LEFT JOIN
                deck_match AS my_odm ON my_odm.match_id = my_dm.match_id AND my_odm.deck_id <> d.id
            INNER JOIN
                deck AS od ON od.id = my_odm.deck_id
            INNER JOIN
                deck_match AS dm ON dm.deck_id = od.id
            LEFT JOIN
                deck_match AS odm ON odm.match_id = dm.match_id AND odm.deck_id <> dm.deck_id
            INNER JOIN
                `match` AS m ON m.id = dm.match_id
            WHERE
                d.competition_id NOT IN (SELECT id FROM competition WHERE end_date > UNIX_TIMESTAMP(NOW()))
            GROUP BY
                d.id
        ) AS ds ON dc.deck_id = ds.deck_id
    SET
        dc.omw = ds.omw
```

[] (slow_query, 28.4, mysql)

Reported on decksite by mysql-perf

Location Hash: d98672f3e9183e095b86f9057263e66987f5a47e

Labels: decksite

vorpal-buildbot commented 3 months ago

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

    UPDATE
        deck_cache AS dc
    INNER JOIN
        (
            SELECT
                d.id AS deck_id,
                ROUND(SUM(CASE WHEN dm.games > odm.games THEN 1 ELSE 0 END) / NULLIF((SUM(CASE WHEN dm.games <> odm.games THEN 1 ELSE 0 END)), 0), 2) AS omw
            FROM
                deck AS d
            INNER JOIN
                deck_match AS my_dm ON my_dm.deck_id = d.id
            LEFT JOIN
                deck_match AS my_odm ON my_odm.match_id = my_dm.match_id AND my_odm.deck_id <> d.id
            INNER JOIN
                deck AS od ON od.id = my_odm.deck_id
            INNER JOIN
                deck_match AS dm ON dm.deck_id = od.id
            LEFT JOIN
                deck_match AS odm ON odm.match_id = dm.match_id AND odm.deck_id <> dm.deck_id
            INNER JOIN
                `match` AS m ON m.id = dm.match_id
            WHERE
                d.competition_id NOT IN (SELECT id FROM competition WHERE end_date > UNIX_TIMESTAMP(NOW()))
            GROUP BY
                d.id
        ) AS ds ON dc.deck_id = ds.deck_id
    SET
        dc.omw = ds.omw
```

[] (slow_query, 22.9, mysql)

Reported on decksite by mysql-perf

Location Hash: d98672f3e9183e095b86f9057263e66987f5a47e

Labels: decksite