search

PennyDreadfulMTG / perf-reports

2 stars 2 forks source link

Exceeded slow_query limit (2.1 > 1.0) in mysql: ``` #42297

Closed vorpal-buildbot closed 5 years ago

vorpal-buildbot commented 5 years ago 
    SELECT
        d.id,
        d.name AS original_name,
        d.created_date,
        d.updated_date,
        SUM(CASE WHEN dm.games > IFNULL(odm.games, 0) 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,
        d.finish,
        d.archetype_id,
        d.url AS source_url,
        d.competition_id,
        c.name AS competition_name,
        c.end_date AS competition_end_date,
        c.top_n AS competition_top_n,
        ct.name AS competition_type_name,
        d.identifier,
        LOWER(IFNULL(IFNULL(IFNULL(p.name, p.mtgo_username), p.mtggoldfish_username), p.tappedout_username)) AS person,
        p.id AS person_id,
        p.banned,
        p.discord_id,
        d.decklist_hash,
        d.retired,
        d.reviewed,
        s.name AS source_name,
        IFNULL(a.name, '') AS archetype_name,
        cache.normalized_name AS name,
        cache.colors,
        cache.colored_symbols,
        cache.legal_formats,
        ROUND(cache.omw * 100, 2) AS omw,
        season.id AS season_id,
        IFNULL(MAX(m.date), d.created_date) AS active_date
    FROM
        deck AS d
    LEFT JOIN
        person AS p ON d.person_id = p.id
    LEFT JOIN
        source AS s ON d.source_id = s.id
    LEFT JOIN
        archetype AS a ON d.archetype_id = a.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 cache ON d.id = cache.deck_id
    LEFT JOIN
        deck_match AS dm ON d.id = dm.deck_id
    LEFT JOIN
        `match` AS m ON dm.match_id = m.id
    LEFT JOIN
        deck_match AS odm ON odm.deck_id <> d.id AND dm.match_id = odm.match_id

    LEFT JOIN
        (
            SELECT
                `start`.id,
                `start`.code,
                `start`.start_date AS start_date,
                `end`.start_date AS end_date
            FROM
                season AS `start`
            LEFT JOIN
                season AS `end` ON `end`.id = `start`.id + 1
        ) AS season ON season.start_date <= d.created_date AND (season.end_date IS NULL OR season.end_date > d.created_date)

    WHERE
        (d.id IN (SELECT deck_id FROM deck_card WHERE card IN ('Moratorium Stone', 'Thawing Glaciers', 'Repeal', 'Engulf the Shore', 'Void Shatter', 'Power Sink', 'Thopter Spy Network', 'Treasure Cruise', 'Urza''s Factory', 'Compulsive Research', 'Careful Consideration', 'Elixir of Immortality', 'Scrabbling Claws', 'Web of Inertia'))) AND (TRUE)
    GROUP BY
        d.id,
        d.competition_id, -- Every deck has only one competition_id but if we want to use competition_id in the HAVING clause we need this.
        season.id -- In theory this is not necessary as all decks are in a single season and we join on the date but MySQL cannot work that out so give it the hint it needs.
    HAVING
        1 = 1
    ORDER BY
        active_date DESC, d.finish IS NULL, d.finish

```

[] (slow_query, 2.1, mysql)

Reported on decksite by mysql-perf

Location Hash: 6f497143c9f848d77e9f11e9123456c54fb0e387

vorpal-buildbot commented 5 years ago

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

    SELECT
        d.id,
        d.name AS original_name,
        d.created_date,
        d.updated_date,
        SUM(CASE WHEN dm.games > IFNULL(odm.games, 0) 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,
        d.finish,
        d.archetype_id,
        d.url AS source_url,
        d.competition_id,
        c.name AS competition_name,
        c.end_date AS competition_end_date,
        c.top_n AS competition_top_n,
        ct.name AS competition_type_name,
        d.identifier,
        LOWER(IFNULL(IFNULL(IFNULL(p.name, p.mtgo_username), p.mtggoldfish_username), p.tappedout_username)) AS person,
        p.id AS person_id,
        p.banned,
        p.discord_id,
        d.decklist_hash,
        d.retired,
        d.reviewed,
        s.name AS source_name,
        IFNULL(a.name, '') AS archetype_name,
        cache.normalized_name AS name,
        cache.colors,
        cache.colored_symbols,
        cache.legal_formats,
        ROUND(cache.omw * 100, 2) AS omw,
        season.id AS season_id,
        IFNULL(MAX(m.date), d.created_date) AS active_date
    FROM
        deck AS d
    LEFT JOIN
        person AS p ON d.person_id = p.id
    LEFT JOIN
        source AS s ON d.source_id = s.id
    LEFT JOIN
        archetype AS a ON d.archetype_id = a.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 cache ON d.id = cache.deck_id
    LEFT JOIN
        deck_match AS dm ON d.id = dm.deck_id
    LEFT JOIN
        `match` AS m ON dm.match_id = m.id
    LEFT JOIN
        deck_match AS odm ON odm.deck_id <> d.id AND dm.match_id = odm.match_id

    LEFT JOIN
        (
            SELECT
                `start`.id,
                `start`.code,
                `start`.start_date AS start_date,
                `end`.start_date AS end_date
            FROM
                season AS `start`
            LEFT JOIN
                season AS `end` ON `end`.id = `start`.id + 1
        ) AS season ON season.start_date <= d.created_date AND (season.end_date IS NULL OR season.end_date > d.created_date)

    WHERE
        (d.id IN (SELECT deck_id FROM deck_card WHERE card IN ('Declaration in Stone', 'Guard Duty', 'Planar Cleansing', 'Attune with Aether', 'Rampant Growth', 'Sunspring Expedition', 'Evolving Wilds', 'Tranquil Expanse', 'Elixir of Immortality'))) AND (TRUE)
    GROUP BY
        d.id,
        d.competition_id, -- Every deck has only one competition_id but if we want to use competition_id in the HAVING clause we need this.
        season.id -- In theory this is not necessary as all decks are in a single season and we join on the date but MySQL cannot work that out so give it the hint it needs.
    HAVING
        1 = 1
    ORDER BY
        active_date DESC, d.finish IS NULL, d.finish

```

[] (slow_query, 1.3, mysql)

Reported on decksite by mysql-perf

Location Hash: 6f497143c9f848d77e9f11e9123456c54fb0e387

Labels: decksite

vorpal-buildbot commented 5 years ago

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

    SELECT
        d.id,
        d.name AS original_name,
        d.created_date,
        d.updated_date,
        SUM(CASE WHEN dm.games > IFNULL(odm.games, 0) 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,
        d.finish,
        d.archetype_id,
        d.url AS source_url,
        d.competition_id,
        c.name AS competition_name,
        c.end_date AS competition_end_date,
        c.top_n AS competition_top_n,
        ct.name AS competition_type_name,
        d.identifier,
        LOWER(IFNULL(IFNULL(IFNULL(p.name, p.mtgo_username), p.mtggoldfish_username), p.tappedout_username)) AS person,
        p.id AS person_id,
        p.banned,
        p.discord_id,
        d.decklist_hash,
        d.retired,
        d.reviewed,
        s.name AS source_name,
        IFNULL(a.name, '') AS archetype_name,
        cache.normalized_name AS name,
        cache.colors,
        cache.colored_symbols,
        cache.legal_formats,
        ROUND(cache.omw * 100, 2) AS omw,
        season.id AS season_id,
        IFNULL(MAX(m.date), d.created_date) AS active_date
    FROM
        deck AS d
    LEFT JOIN
        person AS p ON d.person_id = p.id
    LEFT JOIN
        source AS s ON d.source_id = s.id
    LEFT JOIN
        archetype AS a ON d.archetype_id = a.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 cache ON d.id = cache.deck_id
    LEFT JOIN
        deck_match AS dm ON d.id = dm.deck_id
    LEFT JOIN
        `match` AS m ON dm.match_id = m.id
    LEFT JOIN
        deck_match AS odm ON odm.deck_id <> d.id AND dm.match_id = odm.match_id

    LEFT JOIN
        (
            SELECT
                `start`.id,
                `start`.code,
                `start`.start_date AS start_date,
                `end`.start_date AS end_date
            FROM
                season AS `start`
            LEFT JOIN
                season AS `end` ON `end`.id = `start`.id + 1
        ) AS season ON season.start_date <= d.created_date AND (season.end_date IS NULL OR season.end_date > d.created_date)

    WHERE
        (d.id IN (SELECT deck_id FROM deck_card WHERE card IN ('Chandra''s Pyrohelix', 'Leyline of Lightning', 'Stormblood Berserker', 'Village Messenger', 'Chandra''s Phoenix', 'Burst Lightning', 'Flame Javelin', 'Crater''s Claws', 'Searing Spear', 'Stromkirk Noble', 'Flametongue Kavu', 'Ash Zealot'))) AND (TRUE)
    GROUP BY
        d.id,
        d.competition_id, -- Every deck has only one competition_id but if we want to use competition_id in the HAVING clause we need this.
        season.id -- In theory this is not necessary as all decks are in a single season and we join on the date but MySQL cannot work that out so give it the hint it needs.
    HAVING
        1 = 1
    ORDER BY
        active_date DESC, d.finish IS NULL, d.finish

```

[] (slow_query, 1.2, mysql)

Reported on decksite by mysql-perf

Location Hash: 6f497143c9f848d77e9f11e9123456c54fb0e387

Labels: decksite

vorpal-buildbot commented 5 years ago

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

    SELECT
        d.id,
        d.name AS original_name,
        d.created_date,
        d.updated_date,
        SUM(CASE WHEN dm.games > IFNULL(odm.games, 0) 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,
        d.finish,
        d.archetype_id,
        d.url AS source_url,
        d.competition_id,
        c.name AS competition_name,
        c.end_date AS competition_end_date,
        c.top_n AS competition_top_n,
        ct.name AS competition_type_name,
        d.identifier,
        LOWER(IFNULL(IFNULL(IFNULL(p.name, p.mtgo_username), p.mtggoldfish_username), p.tappedout_username)) AS person,
        p.id AS person_id,
        p.banned,
        p.discord_id,
        d.decklist_hash,
        d.retired,
        d.reviewed,
        s.name AS source_name,
        IFNULL(a.name, '') AS archetype_name,
        cache.normalized_name AS name,
        cache.colors,
        cache.colored_symbols,
        cache.legal_formats,
        ROUND(cache.omw * 100, 2) AS omw,
        season.id AS season_id,
        IFNULL(MAX(m.date), d.created_date) AS active_date
    FROM
        deck AS d
    LEFT JOIN
        person AS p ON d.person_id = p.id
    LEFT JOIN
        source AS s ON d.source_id = s.id
    LEFT JOIN
        archetype AS a ON d.archetype_id = a.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 cache ON d.id = cache.deck_id
    LEFT JOIN
        deck_match AS dm ON d.id = dm.deck_id
    LEFT JOIN
        `match` AS m ON dm.match_id = m.id
    LEFT JOIN
        deck_match AS odm ON odm.deck_id <> d.id AND dm.match_id = odm.match_id

    LEFT JOIN
        (
            SELECT
                `start`.id,
                `start`.code,
                `start`.start_date AS start_date,
                `end`.start_date AS end_date
            FROM
                season AS `start`
            LEFT JOIN
                season AS `end` ON `end`.id = `start`.id + 1
        ) AS season ON season.start_date <= d.created_date AND (season.end_date IS NULL OR season.end_date > d.created_date)

    WHERE
        (d.id IN (SELECT deck_id FROM deck_card WHERE card IN ('Culling Scales', 'Dimir Keyrune', 'Psychic Intrusion', 'Tribute to Hunger', 'Lone Revenant', 'Void Shatter', 'Azorius Keyrune', 'Summary Dismissal', 'Submerged Boneyard', 'Reciprocate', 'Evolving Wilds', 'Meandering River', 'Dreadship Reef', 'Utter End'))) AND (TRUE)
    GROUP BY
        d.id,
        d.competition_id, -- Every deck has only one competition_id but if we want to use competition_id in the HAVING clause we need this.
        season.id -- In theory this is not necessary as all decks are in a single season and we join on the date but MySQL cannot work that out so give it the hint it needs.
    HAVING
        1 = 1
    ORDER BY
        active_date DESC, d.finish IS NULL, d.finish

```

[] (slow_query, 1.1, mysql)

Reported on decksite by mysql-perf

Location Hash: 6f497143c9f848d77e9f11e9123456c54fb0e387

Labels: decksite