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

PennyDreadfulMTG / perf-reports

2 stars 7 forks source link

Exceeded slow_query limit (43.7 > 1.0) in mysql: ``` #63286

Open vorpal-buildbot opened 1 month ago

vorpal-buildbot commented 1 month ago

    CREATE TABLE IF NOT EXISTS _new_season_archetype_playability (
        name VARCHAR(190) NOT NULL,
        season_id INT NOT NULL,
        archetype_id INT NOT NULL,
        playability DECIMAL(6,5) NOT NULL,
        PRIMARY KEY (name, season_id, archetype_id),
        FOREIGN KEY (season_id) REFERENCES season (id) ON UPDATE CASCADE ON DELETE CASCADE,
        FOREIGN KEY (archetype_id) REFERENCES archetype (id) ON UPDATE CASCADE ON DELETE CASCADE,
        INDEX idx_archetype_id_playability_name (archetype_id, playability, name)
    ) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci AS
    SELECT
        sacc.name,
        sacc.season_id,
        sacc.archetype_id,
        (
            -- num decks playing this card in this archetype in this season
            (sacc.num_decks_maindeck + sacc.num_decks_sideboard_only * 0.2)
                /
            -- num decks in this archetype in this season
            sac.num_decks
        )
            *
        (
            1.0
                -
            (
                -- num decks playing this card in this season
                (scc.num_decks_maindeck + scc.num_decks_sideboard_only * 0.2)
                    /
                -- num decks in this season
                sc.num_decks
            )
        ) AS playability
    FROM
        _season_archetype_card_count AS sacc
    INNER JOIN
        _season_archetype_count AS sac ON sac.archetype_id = sacc.archetype_id AND sac.season_id = sacc.season_id
    INNER JOIN
        _season_card_count AS scc ON scc.name = sacc.name AND scc.season_id = sacc.season_id
    INNER JOIN
        _season_count AS sc ON sc.season_id = sacc.season_id
```

[] (slow_query, 43.7, mysql)

Reported on decksite by mysql-perf

Location Hash: a6cf1c6a4566f4fd7a1be5f8a6f2059687653cdd

vorpal-buildbot commented 1 month ago

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

    CREATE TABLE IF NOT EXISTS _new_season_archetype_playability (
        name VARCHAR(190) NOT NULL,
        season_id INT NOT NULL,
        archetype_id INT NOT NULL,
        playability DECIMAL(6,5) NOT NULL,
        PRIMARY KEY (name, season_id, archetype_id),
        FOREIGN KEY (season_id) REFERENCES season (id) ON UPDATE CASCADE ON DELETE CASCADE,
        FOREIGN KEY (archetype_id) REFERENCES archetype (id) ON UPDATE CASCADE ON DELETE CASCADE,
        INDEX idx_archetype_id_playability_name (archetype_id, playability, name)
    ) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci AS
    SELECT
        sacc.name,
        sacc.season_id,
        sacc.archetype_id,
        (
            -- num decks playing this card in this archetype in this season
            (sacc.num_decks_maindeck + sacc.num_decks_sideboard_only * 0.2)
                /
            -- num decks in this archetype in this season
            sac.num_decks
        )
            *
        (
            1.0
                -
            (
                -- num decks playing this card in this season
                (scc.num_decks_maindeck + scc.num_decks_sideboard_only * 0.2)
                    /
                -- num decks in this season
                sc.num_decks
            )
        ) AS playability
    FROM
        _season_archetype_card_count AS sacc
    INNER JOIN
        _season_archetype_count AS sac ON sac.archetype_id = sacc.archetype_id AND sac.season_id = sacc.season_id
    INNER JOIN
        _season_card_count AS scc ON scc.name = sacc.name AND scc.season_id = sacc.season_id
    INNER JOIN
        _season_count AS sc ON sc.season_id = sacc.season_id
```

[] (slow_query, 12.8, mysql)

Reported on decksite by mysql-perf

Location Hash: a6cf1c6a4566f4fd7a1be5f8a6f2059687653cdd

Labels: decksite

vorpal-buildbot commented 1 month ago

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

    CREATE TABLE IF NOT EXISTS _new_season_archetype_playability (
        name VARCHAR(190) NOT NULL,
        season_id INT NOT NULL,
        archetype_id INT NOT NULL,
        playability DECIMAL(6,5) NOT NULL,
        PRIMARY KEY (name, season_id, archetype_id),
        FOREIGN KEY (season_id) REFERENCES season (id) ON UPDATE CASCADE ON DELETE CASCADE,
        FOREIGN KEY (archetype_id) REFERENCES archetype (id) ON UPDATE CASCADE ON DELETE CASCADE,
        INDEX idx_archetype_id_playability_name (archetype_id, playability, name)
    ) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci AS
    SELECT
        sacc.name,
        sacc.season_id,
        sacc.archetype_id,
        (
            -- num decks playing this card in this archetype in this season
            (sacc.num_decks_maindeck + sacc.num_decks_sideboard_only * 0.2)
                /
            -- num decks in this archetype in this season
            sac.num_decks
        )
            *
        (
            1.0
                -
            (
                -- num decks playing this card in this season
                (scc.num_decks_maindeck + scc.num_decks_sideboard_only * 0.2)
                    /
                -- num decks in this season
                sc.num_decks
            )
        ) AS playability
    FROM
        _season_archetype_card_count AS sacc
    INNER JOIN
        _season_archetype_count AS sac ON sac.archetype_id = sacc.archetype_id AND sac.season_id = sacc.season_id
    INNER JOIN
        _season_card_count AS scc ON scc.name = sacc.name AND scc.season_id = sacc.season_id
    INNER JOIN
        _season_count AS sc ON sc.season_id = sacc.season_id
```

[] (slow_query, 13.0, mysql)

Reported on decksite by mysql-perf

Location Hash: a6cf1c6a4566f4fd7a1be5f8a6f2059687653cdd

Labels: decksite

vorpal-buildbot commented 1 month ago

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

    CREATE TABLE IF NOT EXISTS _new_season_archetype_playability (
        name VARCHAR(190) NOT NULL,
        season_id INT NOT NULL,
        archetype_id INT NOT NULL,
        playability DECIMAL(6,5) NOT NULL,
        PRIMARY KEY (name, season_id, archetype_id),
        FOREIGN KEY (season_id) REFERENCES season (id) ON UPDATE CASCADE ON DELETE CASCADE,
        FOREIGN KEY (archetype_id) REFERENCES archetype (id) ON UPDATE CASCADE ON DELETE CASCADE,
        INDEX idx_archetype_id_playability_name (archetype_id, playability, name)
    ) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci AS
    SELECT
        sacc.name,
        sacc.season_id,
        sacc.archetype_id,
        (
            -- num decks playing this card in this archetype in this season
            (sacc.num_decks_maindeck + sacc.num_decks_sideboard_only * 0.2)
                /
            -- num decks in this archetype in this season
            sac.num_decks
        )
            *
        (
            1.0
                -
            (
                -- num decks playing this card in this season
                (scc.num_decks_maindeck + scc.num_decks_sideboard_only * 0.2)
                    /
                -- num decks in this season
                sc.num_decks
            )
        ) AS playability
    FROM
        _season_archetype_card_count AS sacc
    INNER JOIN
        _season_archetype_count AS sac ON sac.archetype_id = sacc.archetype_id AND sac.season_id = sacc.season_id
    INNER JOIN
        _season_card_count AS scc ON scc.name = sacc.name AND scc.season_id = sacc.season_id
    INNER JOIN
        _season_count AS sc ON sc.season_id = sacc.season_id
```

[] (slow_query, 14.9, mysql)

Reported on decksite by mysql-perf

Location Hash: a6cf1c6a4566f4fd7a1be5f8a6f2059687653cdd

Labels: decksite

vorpal-buildbot commented 1 month ago

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

    CREATE TABLE IF NOT EXISTS _new_season_archetype_playability (
        name VARCHAR(190) NOT NULL,
        season_id INT NOT NULL,
        archetype_id INT NOT NULL,
        playability DECIMAL(6,5) NOT NULL,
        PRIMARY KEY (name, season_id, archetype_id),
        FOREIGN KEY (season_id) REFERENCES season (id) ON UPDATE CASCADE ON DELETE CASCADE,
        FOREIGN KEY (archetype_id) REFERENCES archetype (id) ON UPDATE CASCADE ON DELETE CASCADE,
        INDEX idx_archetype_id_playability_name (archetype_id, playability, name)
    ) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci AS
    SELECT
        sacc.name,
        sacc.season_id,
        sacc.archetype_id,
        (
            -- num decks playing this card in this archetype in this season
            (sacc.num_decks_maindeck + sacc.num_decks_sideboard_only * 0.2)
                /
            -- num decks in this archetype in this season
            sac.num_decks
        )
            *
        (
            1.0
                -
            (
                -- num decks playing this card in this season
                (scc.num_decks_maindeck + scc.num_decks_sideboard_only * 0.2)
                    /
                -- num decks in this season
                sc.num_decks
            )
        ) AS playability
    FROM
        _season_archetype_card_count AS sacc
    INNER JOIN
        _season_archetype_count AS sac ON sac.archetype_id = sacc.archetype_id AND sac.season_id = sacc.season_id
    INNER JOIN
        _season_card_count AS scc ON scc.name = sacc.name AND scc.season_id = sacc.season_id
    INNER JOIN
        _season_count AS sc ON sc.season_id = sacc.season_id
```

[] (slow_query, 12.6, mysql)

Reported on decksite by mysql-perf

Location Hash: a6cf1c6a4566f4fd7a1be5f8a6f2059687653cdd

Labels: decksite