search

PennyDreadfulMTG / perf-reports

2 stars 2 forks source link

Exceeded slow_query limit (5.3 > 0.5) in mysql: ``` #61146

Open vorpal-buildbot opened 1 year ago

vorpal-buildbot commented 1 year ago 
        SELECT
            deck_id,
            archetype_id,
            archetype_name
        FROM
            (
    WITH rule_card_count AS
    (
        SELECT
            rule.id, COUNT(card) AS card_count
        FROM
            rule
        JOIN
            rule_card
        ON
            rule.id = rule_card.rule_id
        WHERE
            rule_card.include AND TRUE
        GROUP BY
            rule.id
    ),
    candidates AS
    (
        SELECT
            deck.id AS deck_id,
            COUNT(DISTINCT REPLACE(deck_card.card, 'Snow-Covered ', '')) AS included_count,
            MAX(rule_card_count.card_count) AS required_count,-- fake MAX due to aggregate function
            rule.id AS rule_id
        FROM
            deck
        JOIN
            deck_card
        ON
            deck.id = deck_card.deck_id
        JOIN
            (
                SELECT
                    *
                FROM
                    rule_card
                WHERE
                    include
            ) AS inclusions
        ON
            REPLACE(deck_card.card, 'Snow-Covered ', '') = inclusions.card
        JOIN
            rule
        ON
            rule.id = inclusions.rule_id
        JOIN
            rule_card_count
        ON
            rule.id = rule_card_count.id
        WHERE
            NOT deck_card.sideboard AND deck_card.n >= inclusions.n AND deck_id IN (216130, 216128, 216127, 216123, 216119, 216116, 216115, 216114, 216113, 215852, 216111, 216108, 216104, 216100, 215047, 216093, 216022, 216089, 215888, 216088, 215908, 215822, 216084, 216080, 216075, 216077, 216076, 215996, 216074, 216073, 216013, 216070, 216067, 216066, 216063, 216037, 216049, 216053, 216046, 216052, 216042, 216051, 216050, 216041, 216039, 216028, 216000, 216027, 216026, 216014, 216017, 216012, 216016, 215827, 215902, 215637, 216009, 216002, 216004, 216003, 215937, 215999, 215998, 215493, 215997, 215989, 215994, 215886, 215991, 215984, 215979, 215974, 215945, 215931, 215969, 215963, 215959, 215958, 215957, 215968, 215956, 215955, 215953, 215952, 215938, 215941, 215929, 215925, 215930, 215928, 215921, 215373, 215923, 215918, 215916, 215915, 215914, 215643, 215911, 215848, 215905, 215907, 215901, 215899, 215890, 215882, 215879, 215874, 215868, 215867, 215400, 215866, 215860, 215601, 215853, 215850, 215841, 215851, 215846, 215842, 215838, 215824, 215823, 215817, 215830, 215829, 215748, 215812, 215820, 215818, 215813, 215762, 215750, 215460, 215805, 215801, 215799, 215780, 215798, 215755, 215796, 215794, 215784, 215793, 215788, 215785, 215782, 215779, 215776, 215774, 215773, 215769, 215771, 215768, 215767, 215541, 215598, 215758, 215583, 215725, 215749, 215746, 215743, 215745, 215662, 215739, 215738, 215737, 215734, 215729, 215663, 215661, 215653, 215657, 215654, 215655, 215651, 215652, 215648, 215650, 215639, 215640, 215645, 215638, 215448, 215636, 215635, 215632, 215628, 215630, 215621, 215619, 215612, 215617, 215613, 215605, 215590, 215606, 215484, 215603, 215267, 215599, 215585, 215552, 215569, 215432, 215564, 215522, 215561, 215485, 215555, 215553, 215374, 215532, 215534, 215527, 215526, 215525, 215523, 215521, 215519, 215498, 215511, 215504, 215502, 215500, 215489, 215487, 215428, 215483, 215480, 215479, 215481, 215478, 215471, 215456, 215462, 215454, 215451, 215444, 215345, 215442, 215439, 215433, 215403, 215426, 215416, 215422, 215419, 215415, 215404, 215277, 215402, 215399, 215398, 215390, 215178, 215385, 215379, 215375, 215369, 215367, 215372, 215358, 214987, 215342, 215346, 215347, 215338, 215339, 215332, 215324, 215177, 215290, 215276, 215274, 215270, 215150, 215265, 215259, 215141, 215258, 215251, 214998, 215244, 215167, 215133, 215236, 215240, 215219, 215217, 215223, 215216, 215222, 215215, 215220, 215214, 215208, 215207, 215198, 215169, 215195, 215188, 215187, 215180, 215149, 215170, 215175, 215159, 215154, 215084, 215161, 215158, 215156, 215153, 215145, 215138, 215136, 215135, 215129, 215128, 215118, 215115, 215126, 215114, 215122, 215119, 215109, 215106, 215104, 215071, 215090, 215093, 215086, 215091, 215074, 215087, 215085, 215081, 215078, 215056, 215077, 215057, 215067, 215064, 215060, 215049, 214973, 215055, 215053, 215046, 215042, 215040, 215038, 214968, 215036, 215010, 215034, 215008, 215030, 215026, 215018, 215017, 215023, 214992, 214999, 214988, 214978, 214957, 214950, 214946, 214939, 214928, 214917) AND TRUE
        GROUP BY
            deck.id, rule.id
        HAVING
            included_count = required_count
    )
    SELECT
        candidates.deck_id,
        rule.id AS rule_id,
        suggested_archetype.id AS archetype_id,
        suggested_archetype.name AS archetype_name
    FROM
        candidates
    INNER JOIN
        rule
    ON
        candidates.rule_id = rule.id
    JOIN
        archetype AS suggested_archetype
    ON
        rule.archetype_id = suggested_archetype.id
    LEFT JOIN
        (
            SELECT
                *
            FROM
                rule_card
            WHERE
                NOT include
        ) AS exclusions
    ON
        candidates.rule_id = exclusions.rule_id
    LEFT JOIN
        deck_card
    ON
        candidates.deck_id = deck_card.deck_id AND exclusions.card = REPLACE(deck_card.card, 'Snow-Covered ', '') AND deck_card.n >= exclusions.n
    GROUP BY
        candidates.deck_id, rule_id
    HAVING
        COUNT(REPLACE(deck_card.card, 'Snow-Covered ', '')) = 0
) AS applied_rules
        GROUP BY
            deck_id
        HAVING
            COUNT(DISTINCT archetype_id) = 1
    ```

[] (slow_query, 5.3, mysql)

Reported on decksite by mysql-perf

Location Hash: bbe8ef55f158357bcd7bbb39c4fa1ec73979acf1

Request Data ``` Request Method: POST Path: /admin/archetypes/? Cookies: ImmutableMultiDict([('hide_intro', 'True'), ('session', '.eJx1UV1rgzAU_S95dkVN60ff7MY2RmVdnTg6hgS9avyKJJFVS__7YqF2DPaWc-45OTcnJxR3wBvSQivRWvIeNETShrZXkEJDG5bOkIqE8TSmikGm4doYrxwb6zo2XBMv0U1Rs4TUoFTQ3oWBGvxvoW2c97SeQ35Z0YTyHNJpTcHaS7ChWxpqZM7iXgBvSTNpy7iBiig9I70szFhIIieebk0zzLvAeSh3UAlPT4vAdIPmecxew8NNL1ml4tYnRJIEhLhi9PKxt3xaezaMm_vhAEH1vfd53R2jvuyelB-OHeUgYqIKNFaOha2l7VoL3cW2YeLbfOrU0peOrmuIQ6aoYg5J0zDCgzf2UeR7Ufa43W2g9AsPj28We1chqtNOveZTtah-imaD4i6difmwKJmK-NLQ5dJYDpMBbYBw4Oisob8FSsJzUDu3fV2ffwDrUKg6.ZE8QBQ.L9SV7vchBRFRIRTB5P9SwRZfjYE'), ('session', '.eJx1UV1rgzAU_S95dkVN60ff7MY2RmVdnTg6hgS9avyKJJFVS__7YqF2DPaWc-45OTcnJxR3wBvSQivRWvIeNETShrZXkEJDG5bOkIqE8TSmikGm4doYrxwb6zo2XBMv0U1Rs4TUoFTQ3oWBGvxvoW2c97SeQ35Z0YTyHNJpTcHaS7ChWxpqZM7iXgBvSTNpy7iBiig9I70szFhIIieebk0zzLvAeSh3UAlPT4vAdIPmecxew8NNL1ml4tYnRJIEhLhi9PKxt3xaezaMm_vhAEH1vfd53R2jvuyelB-OHeUgYqIKNFaOha2l7VoL3cW2YeLbfOrU0peOrmuIQ6aoYg5J0zDCgzf2UeR7Ufa43W2g9AsPj28We1chqtNOveZTtah-imaD4i6difmwKJmK-NLQ5dJYDpMBbYBw4Oisob8FSsJzUDu3fV2ffwDrUKg6.ZGMWWA.X-592a_nsgFeqt1wgBMX_rlJRUg'), ('page_size', '20'), ('deck_id', '216131'), ('views', '88')]) Endpoint: post_archetypes View Args: {} Person: 219733587300319234 Referrer: https://pennydreadfulmagic.com/admin/archetypes/ Request Data: {'deck_id': '216131', 'archetype_id': '37'} Content-Type: application/x-www-form-urlencoded Content-Length: 12022 Host: pennydreadfulmagic.com Connection: Keep-Alive Accept-Encoding: gzip X-Forwarded-For: 97.103.253.204 Cf-Ray: 7c81436c59539af1-MIA X-Forwarded-Proto: https Cf-Visitor: {"scheme":"https"} Cache-Control: max-age=0 Sec-Ch-Ua: "Google Chrome";v="113", "Chromium";v="113", "Not-A.Brand";v="24" Sec-Ch-Ua-Mobile: ?0 Sec-Ch-Ua-Platform: "Windows" Upgrade-Insecure-Requests: 1 Origin: https://pennydreadfulmagic.com User-Agent: Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/113.0.0.0 Safari/537.36 Accept: text/html,application/xhtml+xml,application/xml;q=0.9,image/avif,image/webp,image/apng,*/*;q=0.8,application/signed-exchange;v=b3;q=0.7 Sec-Fetch-Site: same-origin Sec-Fetch-Mode: navigate Sec-Fetch-User: ?1 Sec-Fetch-Dest: document Referer: https://pennydreadfulmagic.com/admin/archetypes/ Accept-Language: en-US,en;q=0.9 Cookie: hide_intro=True; session=.eJx1UV1rgzAU_S95dkVN60ff7MY2RmVdnTg6hgS9avyKJJFVS__7YqF2DPaWc-45OTcnJxR3wBvSQivRWvIeNETShrZXkEJDG5bOkIqE8TSmikGm4doYrxwb6zo2XBMv0U1Rs4TUoFTQ3oWBGvxvoW2c97SeQ35Z0YTyHNJpTcHaS7ChWxpqZM7iXgBvSTNpy7iBiig9I70szFhIIieebk0zzLvAeSh3UAlPT4vAdIPmecxew8NNL1ml4tYnRJIEhLhi9PKxt3xaezaMm_vhAEH1vfd53R2jvuyelB-OHeUgYqIKNFaOha2l7VoL3cW2YeLbfOrU0peOrmuIQ6aoYg5J0zDCgzf2UeR7Ufa43W2g9AsPj28We1chqtNOveZTtah-imaD4i6difmwKJmK-NLQ5dJYDpMBbYBw4Oisob8FSsJzUDu3fV2ffwDrUKg6.ZE8QBQ.L9SV7vchBRFRIRTB5P9SwRZfjYE; page_size=20; deck_id=216131; views=88; session=.eJx1UV1rgzAU_S95dkVN60ff7MY2RmVdnTg6hgS9avyKJJFVS__7YqF2DPaWc-45OTcnJxR3wBvSQivRWvIeNETShrZXkEJDG5bOkIqE8TSmikGm4doYrxwb6zo2XBMv0U1Rs4TUoFTQ3oWBGvxvoW2c97SeQ35Z0YTyHNJpTcHaS7ChWxpqZM7iXgBvSTNpy7iBiig9I70szFhIIieebk0zzLvAeSh3UAlPT4vAdIPmecxew8NNL1ml4tYnRJIEhLhi9PKxt3xaezaMm_vhAEH1vfd53R2jvuyelB-OHeUgYqIKNFaOha2l7VoL3cW2YeLbfOrU0peOrmuIQ6aoYg5J0zDCgzf2UeR7Ufa43W2g9AsPj28We1chqtNOveZTtah-imaD4i6difmwKJmK-NLQ5dJYDpMBbYBw4Oisob8FSsJzUDu3fV2ffwDrUKg6.ZGMWWA.X-592a_nsgFeqt1wgBMX_rlJRUg Priority: u=0, i Cf-Connecting-Ip: 97.103.253.204 Cf-Ipcountry: US Cdn-Loop: cloudflare ```
vorpal-buildbot commented 1 year ago

Exceeded slow_query limit (4.0 > 0.5) in mysql: ```

        SELECT
            deck_id,
            archetype_id,
            archetype_name
        FROM
            (
    WITH rule_card_count AS
    (
        SELECT
            rule.id, COUNT(card) AS card_count
        FROM
            rule
        JOIN
            rule_card
        ON
            rule.id = rule_card.rule_id
        WHERE
            rule_card.include AND TRUE
        GROUP BY
            rule.id
    ),
    candidates AS
    (
        SELECT
            deck.id AS deck_id,
            COUNT(DISTINCT REPLACE(deck_card.card, 'Snow-Covered ', '')) AS included_count,
            MAX(rule_card_count.card_count) AS required_count,-- fake MAX due to aggregate function
            rule.id AS rule_id
        FROM
            deck
        JOIN
            deck_card
        ON
            deck.id = deck_card.deck_id
        JOIN
            (
                SELECT
                    *
                FROM
                    rule_card
                WHERE
                    include
            ) AS inclusions
        ON
            REPLACE(deck_card.card, 'Snow-Covered ', '') = inclusions.card
        JOIN
            rule
        ON
            rule.id = inclusions.rule_id
        JOIN
            rule_card_count
        ON
            rule.id = rule_card_count.id
        WHERE
            NOT deck_card.sideboard AND deck_card.n >= inclusions.n AND deck_id IN (216167, 215928, 215921, 215373, 215923, 215918, 215916, 215915, 215914, 215643, 215911, 215848, 215905, 215907, 215901, 215899, 215890, 215882, 215874, 215879, 215868, 215867, 215400, 215866, 215860, 215601, 215853, 215850, 215841, 215851, 215846, 215842, 215838, 215824, 215823, 215817, 215830, 215829, 215748, 215812, 215820, 215818, 215813, 215762, 215750, 215460, 215805, 215801, 215799, 215780, 215798, 215755, 215796, 215794, 215793, 215788, 215785, 215784, 215782, 215779, 215776, 215774, 215773, 215769, 215771, 215768, 215767, 215541, 215598, 215758, 215583, 215725, 215749, 215746, 215743, 215745, 215662, 215739, 215738, 215737, 215734, 215729, 215663, 215661, 215653, 215657, 215654, 215655, 215651, 215652, 215648, 215650, 215639, 215640, 215645, 215638, 215448, 215636, 215635, 215632, 215628, 215630, 215621, 215619, 215612, 215617, 215613, 215605, 215590, 215606, 215484, 215603, 215267, 215599, 215585, 215552, 215569, 215432, 215564, 215522, 215561, 215485, 215555, 215553, 215374, 215532, 215534, 215527, 215526, 215525, 215523, 215521, 215519, 215502, 215500, 215498, 215511, 215504, 215489, 215487, 215428, 215483, 215480, 215479, 215481, 215478, 215471, 215456, 215462, 215454, 215451, 215444, 215345, 215442, 215439, 215433, 215403, 215426, 215416, 215422, 215419, 215415, 215404, 215277, 215402, 215399, 215398, 215390, 215178, 215385, 215379, 215375, 215372, 215369, 215367, 215358, 214987, 215342, 215346, 215347, 215338, 215339, 215332, 215324, 215177, 215290, 215276, 215274, 215270, 215150, 215265, 215259, 215141, 215258, 215251, 214998, 215244, 215167, 215133, 215240, 215236, 215220, 215214, 215219, 215217, 215223, 215216, 215222, 215215, 215208, 215207, 215198, 215169, 215195, 215188, 215187, 215180, 215149, 215170, 215175, 215159, 215154, 215084, 215161, 215158, 215156, 215153, 215145, 215138, 215136, 215135, 215129, 215128, 215119, 215118, 215115, 215126, 215114, 215122, 215109, 215106, 215104, 215071, 215090, 215093, 215086, 215091, 215074, 215087, 215085, 215081, 215078, 215056, 215077, 215057, 215067, 215064, 215060, 215049, 214973, 215055, 215053, 215046, 215042, 215040, 215038, 214968, 215036, 215010, 215034, 215008, 215030, 215026, 215018, 215017, 215023, 214992, 214999, 214988, 214978, 214957, 214950, 214946, 214939, 214928, 214917) AND TRUE
        GROUP BY
            deck.id, rule.id
        HAVING
            included_count = required_count
    )
    SELECT
        candidates.deck_id,
        rule.id AS rule_id,
        suggested_archetype.id AS archetype_id,
        suggested_archetype.name AS archetype_name
    FROM
        candidates
    INNER JOIN
        rule
    ON
        candidates.rule_id = rule.id
    JOIN
        archetype AS suggested_archetype
    ON
        rule.archetype_id = suggested_archetype.id
    LEFT JOIN
        (
            SELECT
                *
            FROM
                rule_card
            WHERE
                NOT include
        ) AS exclusions
    ON
        candidates.rule_id = exclusions.rule_id
    LEFT JOIN
        deck_card
    ON
        candidates.deck_id = deck_card.deck_id AND exclusions.card = REPLACE(deck_card.card, 'Snow-Covered ', '') AND deck_card.n >= exclusions.n
    GROUP BY
        candidates.deck_id, rule_id
    HAVING
        COUNT(REPLACE(deck_card.card, 'Snow-Covered ', '')) = 0
) AS applied_rules
        GROUP BY
            deck_id
        HAVING
            COUNT(DISTINCT archetype_id) = 1
    ```

[] (slow_query, 4.0, mysql)

Reported on decksite by mysql-perf

Location Hash: bbe8ef55f158357bcd7bbb39c4fa1ec73979acf1

Request Data ``` Request Method: POST Path: /admin/archetypes/? Cookies: ImmutableMultiDict([('hide_intro', 'True'), ('session', '.eJx1UV1rgzAU_S95dkVN60ff7MY2RmVdnTg6hgS9avyKJJFVS__7YqF2DPaWc-45OTcnJxR3wBvSQivRWvIeNETShrZXkEJDG5bOkIqE8TSmikGm4doYrxwb6zo2XBMv0U1Rs4TUoFTQ3oWBGvxvoW2c97SeQ35Z0YTyHNJpTcHaS7ChWxpqZM7iXgBvSTNpy7iBiig9I70szFhIIieebk0zzLvAeSh3UAlPT4vAdIPmecxew8NNL1ml4tYnRJIEhLhi9PKxt3xaezaMm_vhAEH1vfd53R2jvuyelB-OHeUgYqIKNFaOha2l7VoL3cW2YeLbfOrU0peOrmuIQ6aoYg5J0zDCgzf2UeR7Ufa43W2g9AsPj28We1chqtNOveZTtah-imaD4i6difmwKJmK-NLQ5dJYDpMBbYBw4Oisob8FSsJzUDu3fV2ffwDrUKg6.ZE8QBQ.L9SV7vchBRFRIRTB5P9SwRZfjYE'), ('session', '.eJx1UV1rgzAU_S95dkVN60ff7MY2RmVdnTg6hgS9avyKJJFVS__7YqF2DPaWc-45OTcnJxR3wBvSQivRWvIeNETShrZXkEJDG5bOkIqE8TSmikGm4doYrxwb6zo2XBMv0U1Rs4TUoFTQ3oWBGvxvoW2c97SeQ35Z0YTyHNJpTcHaS7ChWxpqZM7iXgBvSTNpy7iBiig9I70szFhIIieebk0zzLvAeSh3UAlPT4vAdIPmecxew8NNL1ml4tYnRJIEhLhi9PKxt3xaezaMm_vhAEH1vfd53R2jvuyelB-OHeUgYqIKNFaOha2l7VoL3cW2YeLbfOrU0peOrmuIQ6aoYg5J0zDCgzf2UeR7Ufa43W2g9AsPj28We1chqtNOveZTtah-imaD4i6difmwKJmK-NLQ5dJYDpMBbYBw4Oisob8FSsJzUDu3fV2ffwDrUKg6.ZGPolA.7NaRG1fNcTZG8LRBTOxf3uCxnUg'), ('page_size', '100'), ('views', '71')]) Endpoint: post_archetypes View Args: {} Person: 219733587300319234 Referrer: https://pennydreadfulmagic.com/admin/archetypes/ Request Data: {'deck_id': '216166', 'archetype_id': '192'} Content-Type: application/x-www-form-urlencoded Content-Length: 12166 Host: pennydreadfulmagic.com Connection: Keep-Alive Accept-Encoding: gzip X-Forwarded-For: 97.103.253.204 Cf-Ray: 7c86699878e8b0cf-ATL X-Forwarded-Proto: https Cf-Visitor: {"scheme":"https"} Cache-Control: max-age=0 Sec-Ch-Ua: "Google Chrome";v="113", "Chromium";v="113", "Not-A.Brand";v="24" Sec-Ch-Ua-Mobile: ?0 Sec-Ch-Ua-Platform: "Windows" Upgrade-Insecure-Requests: 1 Origin: https://pennydreadfulmagic.com User-Agent: Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/113.0.0.0 Safari/537.36 Accept: text/html,application/xhtml+xml,application/xml;q=0.9,image/avif,image/webp,image/apng,*/*;q=0.8,application/signed-exchange;v=b3;q=0.7 Sec-Fetch-Site: same-origin Sec-Fetch-Mode: navigate Sec-Fetch-User: ?1 Sec-Fetch-Dest: document Referer: https://pennydreadfulmagic.com/admin/archetypes/ Accept-Language: en-US,en;q=0.9 Cookie: hide_intro=True; session=.eJx1UV1rgzAU_S95dkVN60ff7MY2RmVdnTg6hgS9avyKJJFVS__7YqF2DPaWc-45OTcnJxR3wBvSQivRWvIeNETShrZXkEJDG5bOkIqE8TSmikGm4doYrxwb6zo2XBMv0U1Rs4TUoFTQ3oWBGvxvoW2c97SeQ35Z0YTyHNJpTcHaS7ChWxpqZM7iXgBvSTNpy7iBiig9I70szFhIIieebk0zzLvAeSh3UAlPT4vAdIPmecxew8NNL1ml4tYnRJIEhLhi9PKxt3xaezaMm_vhAEH1vfd53R2jvuyelB-OHeUgYqIKNFaOha2l7VoL3cW2YeLbfOrU0peOrmuIQ6aoYg5J0zDCgzf2UeR7Ufa43W2g9AsPj28We1chqtNOveZTtah-imaD4i6difmwKJmK-NLQ5dJYDpMBbYBw4Oisob8FSsJzUDu3fV2ffwDrUKg6.ZE8QBQ.L9SV7vchBRFRIRTB5P9SwRZfjYE; page_size=100; session=.eJx1UV1rgzAU_S95dkVN60ff7MY2RmVdnTg6hgS9avyKJJFVS__7YqF2DPaWc-45OTcnJxR3wBvSQivRWvIeNETShrZXkEJDG5bOkIqE8TSmikGm4doYrxwb6zo2XBMv0U1Rs4TUoFTQ3oWBGvxvoW2c97SeQ35Z0YTyHNJpTcHaS7ChWxpqZM7iXgBvSTNpy7iBiig9I70szFhIIieebk0zzLvAeSh3UAlPT4vAdIPmecxew8NNL1ml4tYnRJIEhLhi9PKxt3xaezaMm_vhAEH1vfd53R2jvuyelB-OHeUgYqIKNFaOha2l7VoL3cW2YeLbfOrU0peOrmuIQ6aoYg5J0zDCgzf2UeR7Ufa43W2g9AsPj28We1chqtNOveZTtah-imaD4i6difmwKJmK-NLQ5dJYDpMBbYBw4Oisob8FSsJzUDu3fV2ffwDrUKg6.ZGPolA.7NaRG1fNcTZG8LRBTOxf3uCxnUg; views=71 Priority: u=0, i Cf-Connecting-Ip: 97.103.253.204 Cf-Ipcountry: US Cdn-Loop: cloudflare ```

Labels: decksite

vorpal-buildbot commented 1 year ago

Exceeded slow_query limit (3.6 > 0.5) in mysql: ```

        SELECT
            deck_id,
            archetype_id,
            archetype_name
        FROM
            (
    WITH rule_card_count AS
    (
        SELECT
            rule.id, COUNT(card) AS card_count
        FROM
            rule
        JOIN
            rule_card
        ON
            rule.id = rule_card.rule_id
        WHERE
            rule_card.include AND TRUE
        GROUP BY
            rule.id
    ),
    candidates AS
    (
        SELECT
            deck.id AS deck_id,
            COUNT(DISTINCT REPLACE(deck_card.card, 'Snow-Covered ', '')) AS included_count,
            MAX(rule_card_count.card_count) AS required_count,-- fake MAX due to aggregate function
            rule.id AS rule_id
        FROM
            deck
        JOIN
            deck_card
        ON
            deck.id = deck_card.deck_id
        JOIN
            (
                SELECT
                    *
                FROM
                    rule_card
                WHERE
                    include
            ) AS inclusions
        ON
            REPLACE(deck_card.card, 'Snow-Covered ', '') = inclusions.card
        JOIN
            rule
        ON
            rule.id = inclusions.rule_id
        JOIN
            rule_card_count
        ON
            rule.id = rule_card_count.id
        WHERE
            NOT deck_card.sideboard AND deck_card.n >= inclusions.n AND deck_id IN (216168, 215812, 215820, 215818, 215813, 215762, 215750, 215460, 215805, 215801, 215799, 215780, 215798, 215755, 215796, 215794, 215788, 215785, 215784, 215793, 215782, 215779, 215776, 215774, 215773, 215769, 215771, 215768, 215767, 215541, 215598, 215758, 215583, 215725, 215749, 215746, 215743, 215745, 215662, 215739, 215738, 215737, 215734, 215729, 215663, 215661, 215653, 215657, 215654, 215655, 215651, 215652, 215648, 215650, 215639, 215640, 215645, 215638, 215448, 215636, 215635, 215632, 215628, 215630, 215621, 215619, 215612, 215617, 215613, 215605, 215590, 215606, 215484, 215603, 215267, 215599, 215585, 215552, 215569, 215432, 215564, 215522, 215561, 215485, 215555, 215553, 215374, 215532, 215534, 215527, 215526, 215525, 215523, 215521, 215519, 215502, 215500, 215498, 215511, 215504, 215489, 215487, 215428, 215483, 215480, 215479, 215481, 215478, 215471, 215456, 215462, 215454, 215451, 215444, 215345, 215442, 215439, 215433, 215403, 215426, 215416, 215422, 215419, 215415, 215404, 215277, 215402, 215399, 215398, 215390, 215178, 215385, 215379, 215375, 215367, 215372, 215369, 215358, 214987, 215342, 215346, 215347, 215338, 215339, 215332, 215324, 215177, 215290, 215276, 215274, 215270, 215150, 215265, 215259, 215141, 215258, 215251, 214998, 215244, 215167, 215133, 215240, 215236, 215217, 215223, 215216, 215222, 215215, 215220, 215214, 215219, 215207, 215208, 215198, 215169, 215195, 215188, 215187, 215180, 215149, 215170, 215175, 215159, 215154, 215084, 215161, 215158, 215156, 215153, 215145, 215138, 215136, 215135, 215128, 215129, 215115, 215126, 215114, 215122, 215119, 215118, 215109, 215106, 215104, 215071, 215090, 215093, 215086, 215091, 215074, 215087, 215085, 215081, 215078, 215056, 215077, 215057, 215067, 215064, 215060, 215049, 214973, 215055, 215053, 215046, 215042, 215040, 215038, 214968, 215036, 215010, 215034, 215008, 215026, 215030, 215023, 215018, 215017, 214992, 214999, 214988, 214978, 214957, 214950, 214946, 214939, 214928, 214917) AND TRUE
        GROUP BY
            deck.id, rule.id
        HAVING
            included_count = required_count
    )
    SELECT
        candidates.deck_id,
        rule.id AS rule_id,
        suggested_archetype.id AS archetype_id,
        suggested_archetype.name AS archetype_name
    FROM
        candidates
    INNER JOIN
        rule
    ON
        candidates.rule_id = rule.id
    JOIN
        archetype AS suggested_archetype
    ON
        rule.archetype_id = suggested_archetype.id
    LEFT JOIN
        (
            SELECT
                *
            FROM
                rule_card
            WHERE
                NOT include
        ) AS exclusions
    ON
        candidates.rule_id = exclusions.rule_id
    LEFT JOIN
        deck_card
    ON
        candidates.deck_id = deck_card.deck_id AND exclusions.card = REPLACE(deck_card.card, 'Snow-Covered ', '') AND deck_card.n >= exclusions.n
    GROUP BY
        candidates.deck_id, rule_id
    HAVING
        COUNT(REPLACE(deck_card.card, 'Snow-Covered ', '')) = 0
) AS applied_rules
        GROUP BY
            deck_id
        HAVING
            COUNT(DISTINCT archetype_id) = 1
    ```

[] (slow_query, 3.6, mysql)

Reported on decksite by mysql-perf

Location Hash: bbe8ef55f158357bcd7bbb39c4fa1ec73979acf1

Request Data ``` Request Method: POST Path: /admin/archetypes/? Cookies: ImmutableMultiDict([('hide_intro', 'True'), ('session', '.eJx1UV1rgzAU_S95dkVN60ff7MY2RmVdnTg6hgS9avyKJJFVS__7YqF2DPaWc-45OTcnJxR3wBvSQivRWvIeNETShrZXkEJDG5bOkIqE8TSmikGm4doYrxwb6zo2XBMv0U1Rs4TUoFTQ3oWBGvxvoW2c97SeQ35Z0YTyHNJpTcHaS7ChWxpqZM7iXgBvSTNpy7iBiig9I70szFhIIieebk0zzLvAeSh3UAlPT4vAdIPmecxew8NNL1ml4tYnRJIEhLhi9PKxt3xaezaMm_vhAEH1vfd53R2jvuyelB-OHeUgYqIKNFaOha2l7VoL3cW2YeLbfOrU0peOrmuIQ6aoYg5J0zDCgzf2UeR7Ufa43W2g9AsPj28We1chqtNOveZTtah-imaD4i6difmwKJmK-NLQ5dJYDpMBbYBw4Oisob8FSsJzUDu3fV2ffwDrUKg6.ZE8QBQ.L9SV7vchBRFRIRTB5P9SwRZfjYE'), ('session', '.eJx1UV1rgzAU_S95dkVN60ff7MY2RmVdnTg6hgS9avyKJJFVS__7YqF2DPaWc-45OTcnJxR3wBvSQivRWvIeNETShrZXkEJDG5bOkIqE8TSmikGm4doYrxwb6zo2XBMv0U1Rs4TUoFTQ3oWBGvxvoW2c97SeQ35Z0YTyHNJpTcHaS7ChWxpqZM7iXgBvSTNpy7iBiig9I70szFhIIieebk0zzLvAeSh3UAlPT4vAdIPmecxew8NNL1ml4tYnRJIEhLhi9PKxt3xaezaMm_vhAEH1vfd53R2jvuyelB-OHeUgYqIKNFaOha2l7VoL3cW2YeLbfOrU0peOrmuIQ6aoYg5J0zDCgzf2UeR7Ufa43W2g9AsPj28We1chqtNOveZTtah-imaD4i6difmwKJmK-NLQ5dJYDpMBbYBw4Oisob8FSsJzUDu3fV2ffwDrUKg6.ZGPuIg.qwbWudXjGc8z8QWmOYyhpoXt4qw'), ('page_size', '100'), ('views', '91')]) Endpoint: post_archetypes View Args: {} Person: 219733587300319234 Referrer: https://pennydreadfulmagic.com/admin/archetypes/ Request Data: {'deck_id': '216167', 'archetype_id': '310'} Content-Type: application/x-www-form-urlencoded Content-Length: 8394 Host: pennydreadfulmagic.com Connection: Keep-Alive Accept-Encoding: gzip X-Forwarded-For: 97.103.253.204 Cf-Ray: 7c86950b2c47080c-IAD X-Forwarded-Proto: https Cf-Visitor: {"scheme":"https"} Cache-Control: max-age=0 Sec-Ch-Ua: "Google Chrome";v="113", "Chromium";v="113", "Not-A.Brand";v="24" Sec-Ch-Ua-Mobile: ?0 Sec-Ch-Ua-Platform: "Windows" Upgrade-Insecure-Requests: 1 Origin: https://pennydreadfulmagic.com User-Agent: Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/113.0.0.0 Safari/537.36 Accept: text/html,application/xhtml+xml,application/xml;q=0.9,image/avif,image/webp,image/apng,*/*;q=0.8,application/signed-exchange;v=b3;q=0.7 Sec-Fetch-Site: same-origin Sec-Fetch-Mode: navigate Sec-Fetch-User: ?1 Sec-Fetch-Dest: document Referer: https://pennydreadfulmagic.com/admin/archetypes/ Accept-Language: en-US,en;q=0.9 Cookie: hide_intro=True; session=.eJx1UV1rgzAU_S95dkVN60ff7MY2RmVdnTg6hgS9avyKJJFVS__7YqF2DPaWc-45OTcnJxR3wBvSQivRWvIeNETShrZXkEJDG5bOkIqE8TSmikGm4doYrxwb6zo2XBMv0U1Rs4TUoFTQ3oWBGvxvoW2c97SeQ35Z0YTyHNJpTcHaS7ChWxpqZM7iXgBvSTNpy7iBiig9I70szFhIIieebk0zzLvAeSh3UAlPT4vAdIPmecxew8NNL1ml4tYnRJIEhLhi9PKxt3xaezaMm_vhAEH1vfd53R2jvuyelB-OHeUgYqIKNFaOha2l7VoL3cW2YeLbfOrU0peOrmuIQ6aoYg5J0zDCgzf2UeR7Ufa43W2g9AsPj28We1chqtNOveZTtah-imaD4i6difmwKJmK-NLQ5dJYDpMBbYBw4Oisob8FSsJzUDu3fV2ffwDrUKg6.ZE8QBQ.L9SV7vchBRFRIRTB5P9SwRZfjYE; page_size=100; views=91; session=.eJx1UV1rgzAU_S95dkVN60ff7MY2RmVdnTg6hgS9avyKJJFVS__7YqF2DPaWc-45OTcnJxR3wBvSQivRWvIeNETShrZXkEJDG5bOkIqE8TSmikGm4doYrxwb6zo2XBMv0U1Rs4TUoFTQ3oWBGvxvoW2c97SeQ35Z0YTyHNJpTcHaS7ChWxpqZM7iXgBvSTNpy7iBiig9I70szFhIIieebk0zzLvAeSh3UAlPT4vAdIPmecxew8NNL1ml4tYnRJIEhLhi9PKxt3xaezaMm_vhAEH1vfd53R2jvuyelB-OHeUgYqIKNFaOha2l7VoL3cW2YeLbfOrU0peOrmuIQ6aoYg5J0zDCgzf2UeR7Ufa43W2g9AsPj28We1chqtNOveZTtah-imaD4i6difmwKJmK-NLQ5dJYDpMBbYBw4Oisob8FSsJzUDu3fV2ffwDrUKg6.ZGPuIg.qwbWudXjGc8z8QWmOYyhpoXt4qw Priority: u=0, i Cf-Connecting-Ip: 97.103.253.204 Cf-Ipcountry: US Cdn-Loop: cloudflare ```

Labels: decksite

vorpal-buildbot commented 1 year ago

Exceeded slow_query limit (3.3 > 0.5) in mysql: ```

        SELECT
            deck_id,
            archetype_id,
            archetype_name
        FROM
            (
    WITH rule_card_count AS
    (
        SELECT
            rule.id, COUNT(card) AS card_count
        FROM
            rule
        JOIN
            rule_card
        ON
            rule.id = rule_card.rule_id
        WHERE
            rule_card.include AND TRUE
        GROUP BY
            rule.id
    ),
    candidates AS
    (
        SELECT
            deck.id AS deck_id,
            COUNT(DISTINCT REPLACE(deck_card.card, 'Snow-Covered ', '')) AS included_count,
            MAX(rule_card_count.card_count) AS required_count,-- fake MAX due to aggregate function
            rule.id AS rule_id
        FROM
            deck
        JOIN
            deck_card
        ON
            deck.id = deck_card.deck_id
        JOIN
            (
                SELECT
                    *
                FROM
                    rule_card
                WHERE
                    include
            ) AS inclusions
        ON
            REPLACE(deck_card.card, 'Snow-Covered ', '') = inclusions.card
        JOIN
            rule
        ON
            rule.id = inclusions.rule_id
        JOIN
            rule_card_count
        ON
            rule.id = rule_card_count.id
        WHERE
            NOT deck_card.sideboard AND deck_card.n >= inclusions.n AND deck_id IN (215780, 215798, 215755, 215796, 215794, 215784, 215793, 215788, 215785, 215782, 215779, 215776, 215774, 215773, 215769, 215771, 215768, 215767, 215541, 215598, 215758, 215583, 215725, 215749, 215746, 215743, 215745, 215662, 215739, 215738, 215737, 215734, 215729, 215663, 215661, 215653, 215657, 215654, 215655, 215651, 215652, 215648, 215650, 215639, 215640, 215645, 215638, 215448, 215636, 215635, 215632, 215628, 215630, 215621, 215619, 215612, 215617, 215613, 215605, 215590, 215606, 215484, 215603, 215267, 215599, 215585, 215552, 215569, 215432, 215564, 215522, 215561, 215485, 215555, 215553, 215374, 215532, 215534, 215527, 215526, 215525, 215523, 215521, 215519, 215504, 215502, 215500, 215498, 215511, 215489, 215487, 215428, 215483, 215480, 215479, 215481, 215478, 215471, 215456, 215462, 215454, 215451, 215444, 215345, 215442, 215439, 215433, 215403, 215426, 215416, 215422, 215419, 215415, 215404, 215277, 215402, 215399, 215398, 215390, 215178, 215385, 215379, 215375, 215367, 215372, 215369, 215358, 214987, 215342, 215346, 215347, 215338, 215339, 215332, 215324, 215177, 215290, 215276, 215274, 215270, 215150, 215265, 215259, 215141, 215258, 215251, 214998, 215244, 215167, 215133, 215240, 215236, 215217, 215223, 215216, 215222, 215215, 215220, 215214, 215219, 215207, 215208, 215198, 215169, 215195, 215188, 215187, 215180, 215149, 215170, 215175, 215159, 215154, 215084, 215161, 215158, 215156, 215153, 215145, 215138, 215136, 215135, 215128, 215129, 215115, 215126, 215114, 215122, 215119, 215118, 215109, 215106, 215104, 215071, 215090, 215093, 215086, 215091, 215074, 215087, 215085, 215081, 215078, 215056, 215077, 215057, 215067, 215064, 215060, 215049, 214973, 215055, 215053, 215046, 215042, 215040, 215038, 214968, 215036, 215010, 215034, 215008, 215026, 215030, 215023, 215018, 215017, 214992, 214999, 214988, 214978, 214957, 214950, 214946, 214939, 214928, 214917) AND TRUE
        GROUP BY
            deck.id, rule.id
        HAVING
            included_count = required_count
    )
    SELECT
        candidates.deck_id,
        rule.id AS rule_id,
        suggested_archetype.id AS archetype_id,
        suggested_archetype.name AS archetype_name
    FROM
        candidates
    INNER JOIN
        rule
    ON
        candidates.rule_id = rule.id
    JOIN
        archetype AS suggested_archetype
    ON
        rule.archetype_id = suggested_archetype.id
    LEFT JOIN
        (
            SELECT
                *
            FROM
                rule_card
            WHERE
                NOT include
        ) AS exclusions
    ON
        candidates.rule_id = exclusions.rule_id
    LEFT JOIN
        deck_card
    ON
        candidates.deck_id = deck_card.deck_id AND exclusions.card = REPLACE(deck_card.card, 'Snow-Covered ', '') AND deck_card.n >= exclusions.n
    GROUP BY
        candidates.deck_id, rule_id
    HAVING
        COUNT(REPLACE(deck_card.card, 'Snow-Covered ', '')) = 0
) AS applied_rules
        GROUP BY
            deck_id
        HAVING
            COUNT(DISTINCT archetype_id) = 1
    ```

[] (slow_query, 3.3, mysql)

Reported on decksite by mysql-perf

Location Hash: bbe8ef55f158357bcd7bbb39c4fa1ec73979acf1

Request Data ``` Request Method: POST Path: /admin/archetypes/? Cookies: ImmutableMultiDict([('hide_intro', 'True'), ('session', '.eJx1UV1rgzAU_S95dkVN60ff7MY2RmVdnTg6hgS9avyKJJFVS__7YqF2DPaWc-45OTcnJxR3wBvSQivRWvIeNETShrZXkEJDG5bOkIqE8TSmikGm4doYrxwb6zo2XBMv0U1Rs4TUoFTQ3oWBGvxvoW2c97SeQ35Z0YTyHNJpTcHaS7ChWxpqZM7iXgBvSTNpy7iBiig9I70szFhIIieebk0zzLvAeSh3UAlPT4vAdIPmecxew8NNL1ml4tYnRJIEhLhi9PKxt3xaezaMm_vhAEH1vfd53R2jvuyelB-OHeUgYqIKNFaOha2l7VoL3cW2YeLbfOrU0peOrmuIQ6aoYg5J0zDCgzf2UeR7Ufa43W2g9AsPj28We1chqtNOveZTtah-imaD4i6difmwKJmK-NLQ5dJYDpMBbYBw4Oisob8FSsJzUDu3fV2ffwDrUKg6.ZE8QBQ.L9SV7vchBRFRIRTB5P9SwRZfjYE'), ('session', '.eJx1UV1rgzAU_S95dkVN60ff7MY2RmVdnTg6hgS9avyKJJFVS__7YqF2DPaWc-45OTcnJxR3wBvSQivRWvIeNETShrZXkEJDG5bOkIqE8TSmikGm4doYrxwb6zo2XBMv0U1Rs4TUoFTQ3oWBGvxvoW2c97SeQ35Z0YTyHNJpTcHaS7ChWxpqZM7iXgBvSTNpy7iBiig9I70szFhIIieebk0zzLvAeSh3UAlPT4vAdIPmecxew8NNL1ml4tYnRJIEhLhi9PKxt3xaezaMm_vhAEH1vfd53R2jvuyelB-OHeUgYqIKNFaOha2l7VoL3cW2YeLbfOrU0peOrmuIQ6aoYg5J0zDCgzf2UeR7Ufa43W2g9AsPj28We1chqtNOveZTtah-imaD4i6difmwKJmK-NLQ5dJYDpMBbYBw4Oisob8FSsJzUDu3fV2ffwDrUKg6.ZGPwRw.zVcYo_8heiH4ZrRFOWdVdjOpnBs'), ('page_size', '100'), ('views', '96')]) Endpoint: post_archetypes View Args: {} Person: 219733587300319234 Referrer: https://pennydreadfulmagic.com/admin/archetypes/ Request Data: {'deck_id': '216168', 'archetype_id': '440'} Content-Type: application/x-www-form-urlencoded Content-Length: 7222 Host: pennydreadfulmagic.com Connection: Keep-Alive Accept-Encoding: gzip X-Forwarded-For: 97.103.253.204 Cf-Ray: 7c869ec5aee95a27-IAD X-Forwarded-Proto: https Cf-Visitor: {"scheme":"https"} Cache-Control: max-age=0 Sec-Ch-Ua: "Google Chrome";v="113", "Chromium";v="113", "Not-A.Brand";v="24" Sec-Ch-Ua-Mobile: ?0 Sec-Ch-Ua-Platform: "Windows" Upgrade-Insecure-Requests: 1 Origin: https://pennydreadfulmagic.com User-Agent: Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/113.0.0.0 Safari/537.36 Accept: text/html,application/xhtml+xml,application/xml;q=0.9,image/avif,image/webp,image/apng,*/*;q=0.8,application/signed-exchange;v=b3;q=0.7 Sec-Fetch-Site: same-origin Sec-Fetch-Mode: navigate Sec-Fetch-User: ?1 Sec-Fetch-Dest: document Referer: https://pennydreadfulmagic.com/admin/archetypes/ Accept-Language: en-US,en;q=0.9 Cookie: hide_intro=True; session=.eJx1UV1rgzAU_S95dkVN60ff7MY2RmVdnTg6hgS9avyKJJFVS__7YqF2DPaWc-45OTcnJxR3wBvSQivRWvIeNETShrZXkEJDG5bOkIqE8TSmikGm4doYrxwb6zo2XBMv0U1Rs4TUoFTQ3oWBGvxvoW2c97SeQ35Z0YTyHNJpTcHaS7ChWxpqZM7iXgBvSTNpy7iBiig9I70szFhIIieebk0zzLvAeSh3UAlPT4vAdIPmecxew8NNL1ml4tYnRJIEhLhi9PKxt3xaezaMm_vhAEH1vfd53R2jvuyelB-OHeUgYqIKNFaOha2l7VoL3cW2YeLbfOrU0peOrmuIQ6aoYg5J0zDCgzf2UeR7Ufa43W2g9AsPj28We1chqtNOveZTtah-imaD4i6difmwKJmK-NLQ5dJYDpMBbYBw4Oisob8FSsJzUDu3fV2ffwDrUKg6.ZE8QBQ.L9SV7vchBRFRIRTB5P9SwRZfjYE; page_size=100; views=96; session=.eJx1UV1rgzAU_S95dkVN60ff7MY2RmVdnTg6hgS9avyKJJFVS__7YqF2DPaWc-45OTcnJxR3wBvSQivRWvIeNETShrZXkEJDG5bOkIqE8TSmikGm4doYrxwb6zo2XBMv0U1Rs4TUoFTQ3oWBGvxvoW2c97SeQ35Z0YTyHNJpTcHaS7ChWxpqZM7iXgBvSTNpy7iBiig9I70szFhIIieebk0zzLvAeSh3UAlPT4vAdIPmecxew8NNL1ml4tYnRJIEhLhi9PKxt3xaezaMm_vhAEH1vfd53R2jvuyelB-OHeUgYqIKNFaOha2l7VoL3cW2YeLbfOrU0peOrmuIQ6aoYg5J0zDCgzf2UeR7Ufa43W2g9AsPj28We1chqtNOveZTtah-imaD4i6difmwKJmK-NLQ5dJYDpMBbYBw4Oisob8FSsJzUDu3fV2ffwDrUKg6.ZGPwRw.zVcYo_8heiH4ZrRFOWdVdjOpnBs Priority: u=0, i Cf-Connecting-Ip: 97.103.253.204 Cf-Ipcountry: US Cdn-Loop: cloudflare ```

Labels: decksite

vorpal-buildbot commented 1 year ago

Exceeded slow_query limit (3.6 > 0.5) in mysql: ```

        SELECT
            deck_id,
            archetype_id,
            archetype_name
        FROM
            (
    WITH rule_card_count AS
    (
        SELECT
            rule.id, COUNT(card) AS card_count
        FROM
            rule
        JOIN
            rule_card
        ON
            rule.id = rule_card.rule_id
        WHERE
            rule_card.include AND TRUE
        GROUP BY
            rule.id
    ),
    candidates AS
    (
        SELECT
            deck.id AS deck_id,
            COUNT(DISTINCT REPLACE(deck_card.card, 'Snow-Covered ', '')) AS included_count,
            MAX(rule_card_count.card_count) AS required_count,-- fake MAX due to aggregate function
            rule.id AS rule_id
        FROM
            deck
        JOIN
            deck_card
        ON
            deck.id = deck_card.deck_id
        JOIN
            (
                SELECT
                    *
                FROM
                    rule_card
                WHERE
                    include
            ) AS inclusions
        ON
            REPLACE(deck_card.card, 'Snow-Covered ', '') = inclusions.card
        JOIN
            rule
        ON
            rule.id = inclusions.rule_id
        JOIN
            rule_card_count
        ON
            rule.id = rule_card_count.id
        WHERE
            NOT deck_card.sideboard AND deck_card.n >= inclusions.n AND deck_id IN (216200, 216198, 216185, 216197, 216196, 216190, 216187, 216186, 216183, 216181, 216178, 216177, 216175, 216172, 216169, 215780, 215798, 215755, 215796, 215794, 215793, 215788, 215785, 215784, 215782, 215779, 215776, 215774, 215773, 215769, 215771, 215768, 215767, 215541, 215598, 215758, 215583, 215725, 215749, 215746, 215743, 215745, 215662, 215739, 215738, 215737, 215734, 215729, 215663, 215661, 215653, 215657, 215654, 215655, 215651, 215652, 215648, 215650, 215639, 215640, 215645, 215638, 215448, 215636, 215635, 215632, 215628, 215630, 215621, 215619, 215612, 215617, 215613, 215605, 215590, 215606, 215484, 215603, 215267, 215599, 215585, 215552, 215569, 215432, 215564, 215522, 215561, 215485, 215555, 215553, 215374, 215532, 215534, 215527, 215526, 215525, 215523, 215521, 215519, 215500, 215498, 215511, 215504, 215502, 215489, 215487, 215428, 215483, 215480, 215479, 215481, 215478, 215471, 215456, 215462, 215454, 215451, 215444, 215345, 215442, 215439, 215433, 215403, 215426, 215416, 215422, 215419, 215415, 215404, 215277, 215402, 215399, 215398, 215390, 215178, 215385, 215379, 215375, 215372, 215369, 215367, 215358, 214987, 215342, 215346, 215347, 215338, 215339, 215332, 215324, 215177, 215290, 215276, 215274, 215270, 215150, 215265, 215259, 215141, 215258, 215251, 214998, 215244, 215167, 215133, 215240, 215236, 215222, 215215, 215220, 215214, 215219, 215217, 215223, 215216, 215208, 215207, 215198, 215169, 215195, 215188, 215187, 215180, 215149, 215170, 215175, 215159, 215154, 215084, 215161, 215158, 215156, 215153, 215145, 215138, 215136, 215135, 215129, 215128, 215122, 215119, 215118, 215115, 215126, 215114, 215109, 215106, 215104, 215071, 215090, 215093, 215086, 215091, 215074, 215087, 215085, 215081, 215078, 215056, 215077, 215057, 215067, 215064, 215060, 215049, 214973, 215055, 215053, 215046, 215042, 215040, 215038, 214968, 215036, 215010, 215034, 215008, 215030, 215026, 215018, 215017, 215023, 214992, 214999, 214988, 214978, 214957, 214950, 214946, 214939, 214928, 214917) AND TRUE
        GROUP BY
            deck.id, rule.id
        HAVING
            included_count = required_count
    )
    SELECT
        candidates.deck_id,
        rule.id AS rule_id,
        suggested_archetype.id AS archetype_id,
        suggested_archetype.name AS archetype_name
    FROM
        candidates
    INNER JOIN
        rule
    ON
        candidates.rule_id = rule.id
    JOIN
        archetype AS suggested_archetype
    ON
        rule.archetype_id = suggested_archetype.id
    LEFT JOIN
        (
            SELECT
                *
            FROM
                rule_card
            WHERE
                NOT include
        ) AS exclusions
    ON
        candidates.rule_id = exclusions.rule_id
    LEFT JOIN
        deck_card
    ON
        candidates.deck_id = deck_card.deck_id AND exclusions.card = REPLACE(deck_card.card, 'Snow-Covered ', '') AND deck_card.n >= exclusions.n
    GROUP BY
        candidates.deck_id, rule_id
    HAVING
        COUNT(REPLACE(deck_card.card, 'Snow-Covered ', '')) = 0
) AS applied_rules
        GROUP BY
            deck_id
        HAVING
            COUNT(DISTINCT archetype_id) = 1
    ```

[] (slow_query, 3.6, mysql)

Reported on decksite by mysql-perf

Location Hash: bbe8ef55f158357bcd7bbb39c4fa1ec73979acf1

Request Data ``` Request Method: POST Path: /admin/archetypes/? Cookies: ImmutableMultiDict([('hide_intro', 'True'), ('session', '.eJx1UV1rgzAU_S95dkVN60ff7MY2RmVdnTg6hgS9avyKJJFVS__7YqF2DPaWc-45OTcnJxR3wBvSQivRWvIeNETShrZXkEJDG5bOkIqE8TSmikGm4doYrxwb6zo2XBMv0U1Rs4TUoFTQ3oWBGvxvoW2c97SeQ35Z0YTyHNJpTcHaS7ChWxpqZM7iXgBvSTNpy7iBiig9I70szFhIIieebk0zzLvAeSh3UAlPT4vAdIPmecxew8NNL1ml4tYnRJIEhLhi9PKxt3xaezaMm_vhAEH1vfd53R2jvuyelB-OHeUgYqIKNFaOha2l7VoL3cW2YeLbfOrU0peOrmuIQ6aoYg5J0zDCgzf2UeR7Ufa43W2g9AsPj28We1chqtNOveZTtah-imaD4i6difmwKJmK-NLQ5dJYDpMBbYBw4Oisob8FSsJzUDu3fV2ffwDrUKg6.ZE8QBQ.L9SV7vchBRFRIRTB5P9SwRZfjYE'), ('session', '.eJx1UV1rgzAU_S95dkVN60ff7MY2RmVdnTg6hgS9avyKJJFVS__7YqF2DPaWc-45OTcnJxR3wBvSQivRWvIeNETShrZXkEJDG5bOkIqE8TSmikGm4doYrxwb6zo2XBMv0U1Rs4TUoFTQ3oWBGvxvoW2c97SeQ35Z0YTyHNJpTcHaS7ChWxpqZM7iXgBvSTNpy7iBiig9I70szFhIIieebk0zzLvAeSh3UAlPT4vAdIPmecxew8NNL1ml4tYnRJIEhLhi9PKxt3xaezaMm_vhAEH1vfd53R2jvuyelB-OHeUgYqIKNFaOha2l7VoL3cW2YeLbfOrU0peOrmuIQ6aoYg5J0zDCgzf2UeR7Ufa43W2g9AsPj28We1chqtNOveZTtah-imaD4i6difmwKJmK-NLQ5dJYDpMBbYBw4Oisob8FSsJzUDu3fV2ffwDrUKg6.ZGUcNA.DAkoZptUPQaPGKXNBnjO6y37nt4'), ('views', '14')]) Endpoint: post_archetypes View Args: {} Person: 219733587300319234 Referrer: https://pennydreadfulmagic.com/admin/archetypes/ Request Data: {'deck_id': '216200', 'archetype_id': ''} Content-Type: application/x-www-form-urlencoded Content-Length: 7843 Host: pennydreadfulmagic.com Connection: Keep-Alive Accept-Encoding: gzip X-Forwarded-For: 97.103.253.204 Cf-Ray: 7c8de8135ae1b0d9-ATL X-Forwarded-Proto: https Cf-Visitor: {"scheme":"https"} Cache-Control: max-age=0 Sec-Ch-Ua: "Google Chrome";v="113", "Chromium";v="113", "Not-A.Brand";v="24" Sec-Ch-Ua-Mobile: ?0 Sec-Ch-Ua-Platform: "Windows" Upgrade-Insecure-Requests: 1 Origin: https://pennydreadfulmagic.com User-Agent: Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/113.0.0.0 Safari/537.36 Accept: text/html,application/xhtml+xml,application/xml;q=0.9,image/avif,image/webp,image/apng,*/*;q=0.8,application/signed-exchange;v=b3;q=0.7 Sec-Fetch-Site: same-origin Sec-Fetch-Mode: navigate Sec-Fetch-User: ?1 Sec-Fetch-Dest: document Referer: https://pennydreadfulmagic.com/admin/archetypes/ Accept-Language: en-US,en;q=0.9 Cookie: hide_intro=True; session=.eJx1UV1rgzAU_S95dkVN60ff7MY2RmVdnTg6hgS9avyKJJFVS__7YqF2DPaWc-45OTcnJxR3wBvSQivRWvIeNETShrZXkEJDG5bOkIqE8TSmikGm4doYrxwb6zo2XBMv0U1Rs4TUoFTQ3oWBGvxvoW2c97SeQ35Z0YTyHNJpTcHaS7ChWxpqZM7iXgBvSTNpy7iBiig9I70szFhIIieebk0zzLvAeSh3UAlPT4vAdIPmecxew8NNL1ml4tYnRJIEhLhi9PKxt3xaezaMm_vhAEH1vfd53R2jvuyelB-OHeUgYqIKNFaOha2l7VoL3cW2YeLbfOrU0peOrmuIQ6aoYg5J0zDCgzf2UeR7Ufa43W2g9AsPj28We1chqtNOveZTtah-imaD4i6difmwKJmK-NLQ5dJYDpMBbYBw4Oisob8FSsJzUDu3fV2ffwDrUKg6.ZE8QBQ.L9SV7vchBRFRIRTB5P9SwRZfjYE; session=.eJx1UV1rgzAU_S95dkVN60ff7MY2RmVdnTg6hgS9avyKJJFVS__7YqF2DPaWc-45OTcnJxR3wBvSQivRWvIeNETShrZXkEJDG5bOkIqE8TSmikGm4doYrxwb6zo2XBMv0U1Rs4TUoFTQ3oWBGvxvoW2c97SeQ35Z0YTyHNJpTcHaS7ChWxpqZM7iXgBvSTNpy7iBiig9I70szFhIIieebk0zzLvAeSh3UAlPT4vAdIPmecxew8NNL1ml4tYnRJIEhLhi9PKxt3xaezaMm_vhAEH1vfd53R2jvuyelB-OHeUgYqIKNFaOha2l7VoL3cW2YeLbfOrU0peOrmuIQ6aoYg5J0zDCgzf2UeR7Ufa43W2g9AsPj28We1chqtNOveZTtah-imaD4i6difmwKJmK-NLQ5dJYDpMBbYBw4Oisob8FSsJzUDu3fV2ffwDrUKg6.ZGUcNA.DAkoZptUPQaPGKXNBnjO6y37nt4; views=14 Priority: u=0, i Cf-Connecting-Ip: 97.103.253.204 Cf-Ipcountry: US Cdn-Loop: cloudflare ```

Labels: decksite

vorpal-buildbot commented 1 year ago

Exceeded slow_query limit (2.7 > 0.5) in mysql: ```

        SELECT
            deck_id,
            archetype_id,
            archetype_name
        FROM
            (
    WITH rule_card_count AS
    (
        SELECT
            rule.id, COUNT(card) AS card_count
        FROM
            rule
        JOIN
            rule_card
        ON
            rule.id = rule_card.rule_id
        WHERE
            rule_card.include AND TRUE
        GROUP BY
            rule.id
    ),
    candidates AS
    (
        SELECT
            deck.id AS deck_id,
            COUNT(DISTINCT REPLACE(deck_card.card, 'Snow-Covered ', '')) AS included_count,
            MAX(rule_card_count.card_count) AS required_count,-- fake MAX due to aggregate function
            rule.id AS rule_id
        FROM
            deck
        JOIN
            deck_card
        ON
            deck.id = deck_card.deck_id
        JOIN
            (
                SELECT
                    *
                FROM
                    rule_card
                WHERE
                    include
            ) AS inclusions
        ON
            REPLACE(deck_card.card, 'Snow-Covered ', '') = inclusions.card
        JOIN
            rule
        ON
            rule.id = inclusions.rule_id
        JOIN
            rule_card_count
        ON
            rule.id = rule_card_count.id
        WHERE
            NOT deck_card.sideboard AND deck_card.n >= inclusions.n AND deck_id IN (215448, 215636, 215635, 215632, 215628, 215630, 215621, 215619, 215612, 215617, 215613, 215605, 215590, 215606, 215484, 215603, 215267, 215599, 215585, 215552, 215569, 215432, 215564, 215522, 215561, 215485, 215555, 215553, 215374, 215532, 215534, 215527, 215526, 215525, 215523, 215521, 215519, 215500, 215498, 215511, 215504, 215502, 215489, 215487, 215428, 215483, 215480, 215479, 215481, 215478, 215471, 215456, 215462, 215454, 215451, 215444, 215345, 215442, 215439, 215433, 215403, 215426, 215416, 215422, 215419, 215415, 215404, 215277, 215402, 215399, 215398, 215390, 215178, 215385, 215379, 215375, 215372, 215369, 215367, 215358, 214987, 215342, 215346, 215347, 215338, 215339, 215332, 215324, 215177, 215290, 215276, 215274, 215270, 215150, 215265, 215259, 215141, 215258, 215251, 214998, 215244, 215167, 215133, 215240, 215236, 215222, 215215, 215220, 215214, 215219, 215217, 215223, 215216, 215208, 215207, 215198, 215169, 215195, 215188, 215187, 215180, 215149, 215170, 215175, 215159, 215154, 215084, 215161, 215158, 215156, 215153, 215145, 215138, 215136, 215135, 215129, 215128, 215122, 215119, 215118, 215115, 215126, 215114, 215109, 215106, 215104, 215071, 215090, 215093, 215086, 215091, 215074, 215087, 215085, 215081, 215078, 215056, 215077, 215057, 215067, 215064, 215060, 215049, 214973, 215055, 215053, 215046, 215042, 215040, 215038, 214968, 215036, 215010, 215034, 215008, 215030, 215026, 215018, 215017, 215023, 214992, 214999, 214988, 214978, 214957, 214950, 214946, 214939, 214928, 214917) AND TRUE
        GROUP BY
            deck.id, rule.id
        HAVING
            included_count = required_count
    )
    SELECT
        candidates.deck_id,
        rule.id AS rule_id,
        suggested_archetype.id AS archetype_id,
        suggested_archetype.name AS archetype_name
    FROM
        candidates
    INNER JOIN
        rule
    ON
        candidates.rule_id = rule.id
    JOIN
        archetype AS suggested_archetype
    ON
        rule.archetype_id = suggested_archetype.id
    LEFT JOIN
        (
            SELECT
                *
            FROM
                rule_card
            WHERE
                NOT include
        ) AS exclusions
    ON
        candidates.rule_id = exclusions.rule_id
    LEFT JOIN
        deck_card
    ON
        candidates.deck_id = deck_card.deck_id AND exclusions.card = REPLACE(deck_card.card, 'Snow-Covered ', '') AND deck_card.n >= exclusions.n
    GROUP BY
        candidates.deck_id, rule_id
    HAVING
        COUNT(REPLACE(deck_card.card, 'Snow-Covered ', '')) = 0
) AS applied_rules
        GROUP BY
            deck_id
        HAVING
            COUNT(DISTINCT archetype_id) = 1
    ```

[] (slow_query, 2.7, mysql)

Reported on decksite by mysql-perf

Location Hash: bbe8ef55f158357bcd7bbb39c4fa1ec73979acf1

Request Data ``` Request Method: POST Path: /admin/archetypes/? Cookies: ImmutableMultiDict([('hide_intro', 'True'), ('session', '.eJx1UV1rgzAU_S95dkVN60ff7MY2RmVdnTg6hgS9avyKJJFVS__7YqF2DPaWc-45OTcnJxR3wBvSQivRWvIeNETShrZXkEJDG5bOkIqE8TSmikGm4doYrxwb6zo2XBMv0U1Rs4TUoFTQ3oWBGvxvoW2c97SeQ35Z0YTyHNJpTcHaS7ChWxpqZM7iXgBvSTNpy7iBiig9I70szFhIIieebk0zzLvAeSh3UAlPT4vAdIPmecxew8NNL1ml4tYnRJIEhLhi9PKxt3xaezaMm_vhAEH1vfd53R2jvuyelB-OHeUgYqIKNFaOha2l7VoL3cW2YeLbfOrU0peOrmuIQ6aoYg5J0zDCgzf2UeR7Ufa43W2g9AsPj28We1chqtNOveZTtah-imaD4i6difmwKJmK-NLQ5dJYDpMBbYBw4Oisob8FSsJzUDu3fV2ffwDrUKg6.ZE8QBQ.L9SV7vchBRFRIRTB5P9SwRZfjYE'), ('session', '.eJx1UV1rgzAU_S95dkVN60ff7MY2RmVdnTg6hgS9avyKJJFVS__7YqF2DPaWc-45OTcnJxR3wBvSQivRWvIeNETShrZXkEJDG5bOkIqE8TSmikGm4doYrxwb6zo2XBMv0U1Rs4TUoFTQ3oWBGvxvoW2c97SeQ35Z0YTyHNJpTcHaS7ChWxpqZM7iXgBvSTNpy7iBiig9I70szFhIIieebk0zzLvAeSh3UAlPT4vAdIPmecxew8NNL1ml4tYnRJIEhLhi9PKxt3xaezaMm_vhAEH1vfd53R2jvuyelB-OHeUgYqIKNFaOha2l7VoL3cW2YeLbfOrU0peOrmuIQ6aoYg5J0zDCgzf2UeR7Ufa43W2g9AsPj28We1chqtNOveZTtah-imaD4i6difmwKJmK-NLQ5dJYDpMBbYBw4Oisob8FSsJzUDu3fV2ffwDrUKg6.ZGU3wA.gePiOSRdQXseqSJncLZifTTnFlU'), ('views', '46')]) Endpoint: post_archetypes View Args: {} Person: 219733587300319234 Referrer: https://pennydreadfulmagic.com/admin/archetypes/ Request Data: {'deck_id': '216202', 'archetype_id': '7'} Content-Type: application/x-www-form-urlencoded Content-Length: 7534 Host: pennydreadfulmagic.com Connection: Keep-Alive Accept-Encoding: gzip X-Forwarded-For: 97.103.253.204 Cf-Ray: 7c8e94326f20b0a6-ATL X-Forwarded-Proto: https Cf-Visitor: {"scheme":"https"} Cache-Control: max-age=0 Sec-Ch-Ua: "Google Chrome";v="113", "Chromium";v="113", "Not-A.Brand";v="24" Sec-Ch-Ua-Mobile: ?0 Sec-Ch-Ua-Platform: "Windows" Upgrade-Insecure-Requests: 1 Origin: https://pennydreadfulmagic.com User-Agent: Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/113.0.0.0 Safari/537.36 Accept: text/html,application/xhtml+xml,application/xml;q=0.9,image/avif,image/webp,image/apng,*/*;q=0.8,application/signed-exchange;v=b3;q=0.7 Sec-Fetch-Site: same-origin Sec-Fetch-Mode: navigate Sec-Fetch-User: ?1 Sec-Fetch-Dest: document Referer: https://pennydreadfulmagic.com/admin/archetypes/ Accept-Language: en-US,en;q=0.9 Cookie: hide_intro=True; session=.eJx1UV1rgzAU_S95dkVN60ff7MY2RmVdnTg6hgS9avyKJJFVS__7YqF2DPaWc-45OTcnJxR3wBvSQivRWvIeNETShrZXkEJDG5bOkIqE8TSmikGm4doYrxwb6zo2XBMv0U1Rs4TUoFTQ3oWBGvxvoW2c97SeQ35Z0YTyHNJpTcHaS7ChWxpqZM7iXgBvSTNpy7iBiig9I70szFhIIieebk0zzLvAeSh3UAlPT4vAdIPmecxew8NNL1ml4tYnRJIEhLhi9PKxt3xaezaMm_vhAEH1vfd53R2jvuyelB-OHeUgYqIKNFaOha2l7VoL3cW2YeLbfOrU0peOrmuIQ6aoYg5J0zDCgzf2UeR7Ufa43W2g9AsPj28We1chqtNOveZTtah-imaD4i6difmwKJmK-NLQ5dJYDpMBbYBw4Oisob8FSsJzUDu3fV2ffwDrUKg6.ZE8QBQ.L9SV7vchBRFRIRTB5P9SwRZfjYE; views=46; session=.eJx1UV1rgzAU_S95dkVN60ff7MY2RmVdnTg6hgS9avyKJJFVS__7YqF2DPaWc-45OTcnJxR3wBvSQivRWvIeNETShrZXkEJDG5bOkIqE8TSmikGm4doYrxwb6zo2XBMv0U1Rs4TUoFTQ3oWBGvxvoW2c97SeQ35Z0YTyHNJpTcHaS7ChWxpqZM7iXgBvSTNpy7iBiig9I70szFhIIieebk0zzLvAeSh3UAlPT4vAdIPmecxew8NNL1ml4tYnRJIEhLhi9PKxt3xaezaMm_vhAEH1vfd53R2jvuyelB-OHeUgYqIKNFaOha2l7VoL3cW2YeLbfOrU0peOrmuIQ6aoYg5J0zDCgzf2UeR7Ufa43W2g9AsPj28We1chqtNOveZTtah-imaD4i6difmwKJmK-NLQ5dJYDpMBbYBw4Oisob8FSsJzUDu3fV2ffwDrUKg6.ZGU3wA.gePiOSRdQXseqSJncLZifTTnFlU Priority: u=0, i Cf-Connecting-Ip: 97.103.253.204 Cf-Ipcountry: US Cdn-Loop: cloudflare ```

Labels: decksite

vorpal-buildbot commented 1 year ago

Exceeded slow_query limit (2.1 > 0.5) in mysql: ```

        SELECT
            deck_id,
            archetype_id,
            archetype_name
        FROM
            (
    WITH rule_card_count AS
    (
        SELECT
            rule.id, COUNT(card) AS card_count
        FROM
            rule
        JOIN
            rule_card
        ON
            rule.id = rule_card.rule_id
        WHERE
            rule_card.include AND TRUE
        GROUP BY
            rule.id
    ),
    candidates AS
    (
        SELECT
            deck.id AS deck_id,
            COUNT(DISTINCT REPLACE(deck_card.card, 'Snow-Covered ', '')) AS included_count,
            MAX(rule_card_count.card_count) AS required_count,-- fake MAX due to aggregate function
            rule.id AS rule_id
        FROM
            deck
        JOIN
            deck_card
        ON
            deck.id = deck_card.deck_id
        JOIN
            (
                SELECT
                    *
                FROM
                    rule_card
                WHERE
                    include
            ) AS inclusions
        ON
            REPLACE(deck_card.card, 'Snow-Covered ', '') = inclusions.card
        JOIN
            rule
        ON
            rule.id = inclusions.rule_id
        JOIN
            rule_card_count
        ON
            rule.id = rule_card_count.id
        WHERE
            NOT deck_card.sideboard AND deck_card.n >= inclusions.n AND deck_id IN (216203, 215428, 215483, 215480, 215479, 215481, 215478, 215471, 215456, 215462, 215454, 215451, 215444, 215345, 215442, 215439, 215433, 215403, 215426, 215416, 215422, 215419, 215415, 215404, 215277, 215402, 215399, 215398, 215390, 215178, 215385, 215379, 215375, 215372, 215369, 215367, 215358, 214987, 215342, 215346, 215347, 215338, 215339, 215332, 215324, 215177, 215290, 215276, 215274, 215270, 215150, 215265, 215259, 215141, 215258, 215251, 214998, 215244, 215167, 215133, 215240, 215236, 215220, 215214, 215219, 215217, 215223, 215216, 215222, 215215, 215208, 215207, 215198, 215169, 215195, 215188, 215187, 215180, 215149, 215170, 215175, 215159, 215154, 215084, 215161, 215158, 215156, 215153, 215145, 215138, 215136, 215135, 215129, 215128, 215119, 215118, 215115, 215126, 215114, 215122, 215109, 215106, 215104, 215071, 215090, 215093, 215086, 215091, 215074, 215087, 215085, 215081, 215078, 215056, 215077, 215057, 215067, 215064, 215060, 215049, 214973, 215055, 215053, 215046, 215042, 215040, 215038, 214968, 215036, 215010, 215034, 215008, 215030, 215026, 215018, 215017, 215023, 214992, 214999, 214988, 214978, 214957, 214950, 214946, 214939, 214928, 214917) AND TRUE
        GROUP BY
            deck.id, rule.id
        HAVING
            included_count = required_count
    )
    SELECT
        candidates.deck_id,
        rule.id AS rule_id,
        suggested_archetype.id AS archetype_id,
        suggested_archetype.name AS archetype_name
    FROM
        candidates
    INNER JOIN
        rule
    ON
        candidates.rule_id = rule.id
    JOIN
        archetype AS suggested_archetype
    ON
        rule.archetype_id = suggested_archetype.id
    LEFT JOIN
        (
            SELECT
                *
            FROM
                rule_card
            WHERE
                NOT include
        ) AS exclusions
    ON
        candidates.rule_id = exclusions.rule_id
    LEFT JOIN
        deck_card
    ON
        candidates.deck_id = deck_card.deck_id AND exclusions.card = REPLACE(deck_card.card, 'Snow-Covered ', '') AND deck_card.n >= exclusions.n
    GROUP BY
        candidates.deck_id, rule_id
    HAVING
        COUNT(REPLACE(deck_card.card, 'Snow-Covered ', '')) = 0
) AS applied_rules
        GROUP BY
            deck_id
        HAVING
            COUNT(DISTINCT archetype_id) = 1
    ```

[] (slow_query, 2.1, mysql)

Reported on decksite by mysql-perf

Location Hash: bbe8ef55f158357bcd7bbb39c4fa1ec73979acf1

Request Data ``` Request Method: POST Path: /admin/archetypes/? Cookies: ImmutableMultiDict([('hide_intro', 'True'), ('session', '.eJx1UV1rgzAU_S95dkVN60ff7MY2RmVdnTg6hgS9avyKJJFVS__7YqF2DPaWc-45OTcnJxR3wBvSQivRWvIeNETShrZXkEJDG5bOkIqE8TSmikGm4doYrxwb6zo2XBMv0U1Rs4TUoFTQ3oWBGvxvoW2c97SeQ35Z0YTyHNJpTcHaS7ChWxpqZM7iXgBvSTNpy7iBiig9I70szFhIIieebk0zzLvAeSh3UAlPT4vAdIPmecxew8NNL1ml4tYnRJIEhLhi9PKxt3xaezaMm_vhAEH1vfd53R2jvuyelB-OHeUgYqIKNFaOha2l7VoL3cW2YeLbfOrU0peOrmuIQ6aoYg5J0zDCgzf2UeR7Ufa43W2g9AsPj28We1chqtNOveZTtah-imaD4i6difmwKJmK-NLQ5dJYDpMBbYBw4Oisob8FSsJzUDu3fV2ffwDrUKg6.ZE8QBQ.L9SV7vchBRFRIRTB5P9SwRZfjYE'), ('session', '.eJx1UV1rgzAU_S95dkVN60ff7MY2RmVdnTg6hgS9avyKJJFVS__7YqF2DPaWc-45OTcnJxR3wBvSQivRWvIeNETShrZXkEJDG5bOkIqE8TSmikGm4doYrxwb6zo2XBMv0U1Rs4TUoFTQ3oWBGvxvoW2c97SeQ35Z0YTyHNJpTcHaS7ChWxpqZM7iXgBvSTNpy7iBiig9I70szFhIIieebk0zzLvAeSh3UAlPT4vAdIPmecxew8NNL1ml4tYnRJIEhLhi9PKxt3xaezaMm_vhAEH1vfd53R2jvuyelB-OHeUgYqIKNFaOha2l7VoL3cW2YeLbfOrU0peOrmuIQ6aoYg5J0zDCgzf2UeR7Ufa43W2g9AsPj28We1chqtNOveZTtah-imaD4i6difmwKJmK-NLQ5dJYDpMBbYBw4Oisob8FSsJzUDu3fV2ffwDrUKg6.ZGU5lA.lrEuEjk9k01kktsU00Vj40uFPnQ'), ('page_size', '20'), ('views', '95')]) Endpoint: post_archetypes View Args: {} Person: 219733587300319234 Referrer: https://pennydreadfulmagic.com/admin/archetypes/ Request Data: {'deck_id': '215448', 'archetype_id': '171'} Content-Type: application/x-www-form-urlencoded Content-Length: 5619 Host: pennydreadfulmagic.com Connection: Keep-Alive Accept-Encoding: gzip X-Forwarded-For: 97.103.253.204 Cf-Ray: 7c8ea3093a4cb0c7-ATL X-Forwarded-Proto: https Cf-Visitor: {"scheme":"https"} Cache-Control: max-age=0 Sec-Ch-Ua: "Google Chrome";v="113", "Chromium";v="113", "Not-A.Brand";v="24" Sec-Ch-Ua-Mobile: ?0 Sec-Ch-Ua-Platform: "Windows" Upgrade-Insecure-Requests: 1 Origin: https://pennydreadfulmagic.com User-Agent: Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/113.0.0.0 Safari/537.36 Accept: text/html,application/xhtml+xml,application/xml;q=0.9,image/avif,image/webp,image/apng,*/*;q=0.8,application/signed-exchange;v=b3;q=0.7 Sec-Fetch-Site: same-origin Sec-Fetch-Mode: navigate Sec-Fetch-User: ?1 Sec-Fetch-Dest: document Referer: https://pennydreadfulmagic.com/admin/archetypes/ Accept-Language: en-US,en;q=0.9 Cookie: hide_intro=True; session=.eJx1UV1rgzAU_S95dkVN60ff7MY2RmVdnTg6hgS9avyKJJFVS__7YqF2DPaWc-45OTcnJxR3wBvSQivRWvIeNETShrZXkEJDG5bOkIqE8TSmikGm4doYrxwb6zo2XBMv0U1Rs4TUoFTQ3oWBGvxvoW2c97SeQ35Z0YTyHNJpTcHaS7ChWxpqZM7iXgBvSTNpy7iBiig9I70szFhIIieebk0zzLvAeSh3UAlPT4vAdIPmecxew8NNL1ml4tYnRJIEhLhi9PKxt3xaezaMm_vhAEH1vfd53R2jvuyelB-OHeUgYqIKNFaOha2l7VoL3cW2YeLbfOrU0peOrmuIQ6aoYg5J0zDCgzf2UeR7Ufa43W2g9AsPj28We1chqtNOveZTtah-imaD4i6difmwKJmK-NLQ5dJYDpMBbYBw4Oisob8FSsJzUDu3fV2ffwDrUKg6.ZE8QBQ.L9SV7vchBRFRIRTB5P9SwRZfjYE; page_size=20; views=95; session=.eJx1UV1rgzAU_S95dkVN60ff7MY2RmVdnTg6hgS9avyKJJFVS__7YqF2DPaWc-45OTcnJxR3wBvSQivRWvIeNETShrZXkEJDG5bOkIqE8TSmikGm4doYrxwb6zo2XBMv0U1Rs4TUoFTQ3oWBGvxvoW2c97SeQ35Z0YTyHNJpTcHaS7ChWxpqZM7iXgBvSTNpy7iBiig9I70szFhIIieebk0zzLvAeSh3UAlPT4vAdIPmecxew8NNL1ml4tYnRJIEhLhi9PKxt3xaezaMm_vhAEH1vfd53R2jvuyelB-OHeUgYqIKNFaOha2l7VoL3cW2YeLbfOrU0peOrmuIQ6aoYg5J0zDCgzf2UeR7Ufa43W2g9AsPj28We1chqtNOveZTtah-imaD4i6difmwKJmK-NLQ5dJYDpMBbYBw4Oisob8FSsJzUDu3fV2ffwDrUKg6.ZGU5lA.lrEuEjk9k01kktsU00Vj40uFPnQ Priority: u=0, i Cf-Connecting-Ip: 97.103.253.204 Cf-Ipcountry: US Cdn-Loop: cloudflare ```

Labels: decksite

vorpal-buildbot commented 1 year ago

Exceeded slow_query limit (0.8 > 0.5) in mysql: ```

        SELECT
            deck_id,
            archetype_id,
            archetype_name
        FROM
            (
    WITH rule_card_count AS
    (
        SELECT
            rule.id, COUNT(card) AS card_count
        FROM
            rule
        JOIN
            rule_card
        ON
            rule.id = rule_card.rule_id
        WHERE
            rule_card.include AND TRUE
        GROUP BY
            rule.id
    ),
    candidates AS
    (
        SELECT
            deck.id AS deck_id,
            COUNT(DISTINCT REPLACE(deck_card.card, 'Snow-Covered ', '')) AS included_count,
            MAX(rule_card_count.card_count) AS required_count,-- fake MAX due to aggregate function
            rule.id AS rule_id
        FROM
            deck
        JOIN
            deck_card
        ON
            deck.id = deck_card.deck_id
        JOIN
            (
                SELECT
                    *
                FROM
                    rule_card
                WHERE
                    include
            ) AS inclusions
        ON
            REPLACE(deck_card.card, 'Snow-Covered ', '') = inclusions.card
        JOIN
            rule
        ON
            rule.id = inclusions.rule_id
        JOIN
            rule_card_count
        ON
            rule.id = rule_card_count.id
        WHERE
            NOT deck_card.sideboard AND deck_card.n >= inclusions.n AND deck_id IN (215128, 215115, 215126, 215114, 215122, 215119, 215118, 215109, 215106, 215104, 215071, 215090, 215093, 215086, 215091, 215074, 215087, 215085, 215081, 215078, 215056, 215077, 215057, 215067, 215064, 215060, 215049, 214973, 215055, 215053, 215046, 215042, 215040, 215038, 214968, 215036, 215010, 215034, 215008, 215030, 215026, 215023, 215018, 215017, 214992, 214999, 214988, 214978, 214957, 214950, 214946, 214939, 214928, 214917) AND TRUE
        GROUP BY
            deck.id, rule.id
        HAVING
            included_count = required_count
    )
    SELECT
        candidates.deck_id,
        rule.id AS rule_id,
        suggested_archetype.id AS archetype_id,
        suggested_archetype.name AS archetype_name
    FROM
        candidates
    INNER JOIN
        rule
    ON
        candidates.rule_id = rule.id
    JOIN
        archetype AS suggested_archetype
    ON
        rule.archetype_id = suggested_archetype.id
    LEFT JOIN
        (
            SELECT
                *
            FROM
                rule_card
            WHERE
                NOT include
        ) AS exclusions
    ON
        candidates.rule_id = exclusions.rule_id
    LEFT JOIN
        deck_card
    ON
        candidates.deck_id = deck_card.deck_id AND exclusions.card = REPLACE(deck_card.card, 'Snow-Covered ', '') AND deck_card.n >= exclusions.n
    GROUP BY
        candidates.deck_id, rule_id
    HAVING
        COUNT(REPLACE(deck_card.card, 'Snow-Covered ', '')) = 0
) AS applied_rules
        GROUP BY
            deck_id
        HAVING
            COUNT(DISTINCT archetype_id) = 1
    ```

[] (slow_query, 0.8, mysql)

Reported on decksite by mysql-perf

Location Hash: bbe8ef55f158357bcd7bbb39c4fa1ec73979acf1

Request Data ``` Request Method: POST Path: /admin/archetypes/? Cookies: ImmutableMultiDict([('hide_intro', 'True'), ('session', '.eJx1UV1rgzAU_S95dkVN60ff7MY2RmVdnTg6hgS9avyKJJFVS__7YqF2DPaWc-45OTcnJxR3wBvSQivRWvIeNETShrZXkEJDG5bOkIqE8TSmikGm4doYrxwb6zo2XBMv0U1Rs4TUoFTQ3oWBGvxvoW2c97SeQ35Z0YTyHNJpTcHaS7ChWxpqZM7iXgBvSTNpy7iBiig9I70szFhIIieebk0zzLvAeSh3UAlPT4vAdIPmecxew8NNL1ml4tYnRJIEhLhi9PKxt3xaezaMm_vhAEH1vfd53R2jvuyelB-OHeUgYqIKNFaOha2l7VoL3cW2YeLbfOrU0peOrmuIQ6aoYg5J0zDCgzf2UeR7Ufa43W2g9AsPj28We1chqtNOveZTtah-imaD4i6difmwKJmK-NLQ5dJYDpMBbYBw4Oisob8FSsJzUDu3fV2ffwDrUKg6.ZE8QBQ.L9SV7vchBRFRIRTB5P9SwRZfjYE'), ('session', '.eJx1UV1rgzAU_S95dkVN60ff7MY2RmVdnTg6hgS9avyKJJFVS__7YqF2DPaWc-45OTcnJxR3wBvSQivRWvIeNETShrZXkEJDG5bOkIqE8TSmikGm4doYrxwb6zo2XBMv0U1Rs4TUoFTQ3oWBGvxvoW2c97SeQ35Z0YTyHNJpTcHaS7ChWxpqZM7iXgBvSTNpy7iBiig9I70szFhIIieebk0zzLvAeSh3UAlPT4vAdIPmecxew8NNL1ml4tYnRJIEhLhi9PKxt3xaezaMm_vhAEH1vfd53R2jvuyelB-OHeUgYqIKNFaOha2l7VoL3cW2YeLbfOrU0peOrmuIQ6aoYg5J0zDCgzf2UeR7Ufa43W2g9AsPj28We1chqtNOveZTtah-imaD4i6difmwKJmK-NLQ5dJYDpMBbYBw4Oisob8FSsJzUDu3fV2ffwDrUKg6.ZGU_ag.iohIH7yt8Iwfhc1KxlK9D7Lhguw'), ('page_size', '20'), ('views', '145')]) Endpoint: post_archetypes View Args: {} Person: 219733587300319234 Referrer: https://pennydreadfulmagic.com/admin/archetypes/ Request Data: {'deck_id': '216203', 'archetype_id': '423'} Content-Type: application/x-www-form-urlencoded Content-Length: 4492 Host: pennydreadfulmagic.com Connection: Keep-Alive Accept-Encoding: gzip X-Forwarded-For: 97.103.253.204 Cf-Ray: 7c8ec4291ca3b0b5-ATL X-Forwarded-Proto: https Cf-Visitor: {"scheme":"https"} Cache-Control: max-age=0 Sec-Ch-Ua: "Google Chrome";v="113", "Chromium";v="113", "Not-A.Brand";v="24" Sec-Ch-Ua-Mobile: ?0 Sec-Ch-Ua-Platform: "Windows" Upgrade-Insecure-Requests: 1 Origin: https://pennydreadfulmagic.com User-Agent: Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/113.0.0.0 Safari/537.36 Accept: text/html,application/xhtml+xml,application/xml;q=0.9,image/avif,image/webp,image/apng,*/*;q=0.8,application/signed-exchange;v=b3;q=0.7 Sec-Fetch-Site: same-origin Sec-Fetch-Mode: navigate Sec-Fetch-User: ?1 Sec-Fetch-Dest: document Referer: https://pennydreadfulmagic.com/admin/archetypes/ Accept-Language: en-US,en;q=0.9 Cookie: hide_intro=True; session=.eJx1UV1rgzAU_S95dkVN60ff7MY2RmVdnTg6hgS9avyKJJFVS__7YqF2DPaWc-45OTcnJxR3wBvSQivRWvIeNETShrZXkEJDG5bOkIqE8TSmikGm4doYrxwb6zo2XBMv0U1Rs4TUoFTQ3oWBGvxvoW2c97SeQ35Z0YTyHNJpTcHaS7ChWxpqZM7iXgBvSTNpy7iBiig9I70szFhIIieebk0zzLvAeSh3UAlPT4vAdIPmecxew8NNL1ml4tYnRJIEhLhi9PKxt3xaezaMm_vhAEH1vfd53R2jvuyelB-OHeUgYqIKNFaOha2l7VoL3cW2YeLbfOrU0peOrmuIQ6aoYg5J0zDCgzf2UeR7Ufa43W2g9AsPj28We1chqtNOveZTtah-imaD4i6difmwKJmK-NLQ5dJYDpMBbYBw4Oisob8FSsJzUDu3fV2ffwDrUKg6.ZE8QBQ.L9SV7vchBRFRIRTB5P9SwRZfjYE; page_size=20; views=145; session=.eJx1UV1rgzAU_S95dkVN60ff7MY2RmVdnTg6hgS9avyKJJFVS__7YqF2DPaWc-45OTcnJxR3wBvSQivRWvIeNETShrZXkEJDG5bOkIqE8TSmikGm4doYrxwb6zo2XBMv0U1Rs4TUoFTQ3oWBGvxvoW2c97SeQ35Z0YTyHNJpTcHaS7ChWxpqZM7iXgBvSTNpy7iBiig9I70szFhIIieebk0zzLvAeSh3UAlPT4vAdIPmecxew8NNL1ml4tYnRJIEhLhi9PKxt3xaezaMm_vhAEH1vfd53R2jvuyelB-OHeUgYqIKNFaOha2l7VoL3cW2YeLbfOrU0peOrmuIQ6aoYg5J0zDCgzf2UeR7Ufa43W2g9AsPj28We1chqtNOveZTtah-imaD4i6difmwKJmK-NLQ5dJYDpMBbYBw4Oisob8FSsJzUDu3fV2ffwDrUKg6.ZGU_ag.iohIH7yt8Iwfhc1KxlK9D7Lhguw Priority: u=0, i Cf-Connecting-Ip: 97.103.253.204 Cf-Ipcountry: US Cdn-Loop: cloudflare ```

Labels: decksite

vorpal-buildbot commented 1 year ago

Exceeded slow_query limit (0.6 > 0.5) in mysql: ```

        SELECT
            deck_id,
            archetype_id,
            archetype_name
        FROM
            (
    WITH rule_card_count AS
    (
        SELECT
            rule.id, COUNT(card) AS card_count
        FROM
            rule
        JOIN
            rule_card
        ON
            rule.id = rule_card.rule_id
        WHERE
            rule_card.include AND TRUE
        GROUP BY
            rule.id
    ),
    candidates AS
    (
        SELECT
            deck.id AS deck_id,
            COUNT(DISTINCT REPLACE(deck_card.card, 'Snow-Covered ', '')) AS included_count,
            MAX(rule_card_count.card_count) AS required_count,-- fake MAX due to aggregate function
            rule.id AS rule_id
        FROM
            deck
        JOIN
            deck_card
        ON
            deck.id = deck_card.deck_id
        JOIN
            (
                SELECT
                    *
                FROM
                    rule_card
                WHERE
                    include
            ) AS inclusions
        ON
            REPLACE(deck_card.card, 'Snow-Covered ', '') = inclusions.card
        JOIN
            rule
        ON
            rule.id = inclusions.rule_id
        JOIN
            rule_card_count
        ON
            rule.id = rule_card_count.id
        WHERE
            NOT deck_card.sideboard AND deck_card.n >= inclusions.n AND deck_id IN (216453, 216452, 216451, 216450, 216449, 216447, 216446, 216445, 216444, 216423, 216441, 216433, 216442, 216429, 216427, 216421, 216418, 216410, 216391, 216415, 216414, 216413, 216407, 216408, 216406, 216401, 216404, 216397, 216399, 216389, 216381, 216395, 216382, 216388, 216387, 216384, 216383, 216379, 216377, 216376, 216375) AND TRUE
        GROUP BY
            deck.id, rule.id
        HAVING
            included_count = required_count
    )
    SELECT
        candidates.deck_id,
        rule.id AS rule_id,
        suggested_archetype.id AS archetype_id,
        suggested_archetype.name AS archetype_name
    FROM
        candidates
    INNER JOIN
        rule
    ON
        candidates.rule_id = rule.id
    JOIN
        archetype AS suggested_archetype
    ON
        rule.archetype_id = suggested_archetype.id
    LEFT JOIN
        (
            SELECT
                *
            FROM
                rule_card
            WHERE
                NOT include
        ) AS exclusions
    ON
        candidates.rule_id = exclusions.rule_id
    LEFT JOIN
        deck_card
    ON
        candidates.deck_id = deck_card.deck_id AND exclusions.card = REPLACE(deck_card.card, 'Snow-Covered ', '') AND deck_card.n >= exclusions.n
    GROUP BY
        candidates.deck_id, rule_id
    HAVING
        COUNT(REPLACE(deck_card.card, 'Snow-Covered ', '')) = 0
) AS applied_rules
        GROUP BY
            deck_id
        HAVING
            COUNT(DISTINCT archetype_id) = 1
    ```

[] (slow_query, 0.6, mysql)

Reported on decksite by mysql-perf

Location Hash: bbe8ef55f158357bcd7bbb39c4fa1ec73979acf1

Request Data ``` Request Method: POST Path: /admin/archetypes/? Cookies: ImmutableMultiDict([('hide_intro', 'True'), ('session', '.eJx1UV1vgjAU_S99ZgYoIOxNhwkuLi7xI5vLQipcaxFa1pYNZvzvKy5qsmRvveeec8-9p0eU1iArwoFrdK9lAxYiecX4pcihYpXIryVTmZB5ygyCXCcaYuyHQ2zb2Ilc7KEboxQZKcGwgN-tFqbxv4TxlDasvJqUglLI-8WU4Gcrxw4sVGkq0kaB5KTqBxdpBQdi9II0eu-mShPd456IJZ3DSBW02STRy3b23SXx03oZxq-dvPG1OIC584hIloFSlxq56xZYHCYrv31eLhaPU1yLOOHbYOZ_jvpLoK2ZBJUSE5kT-N4wcqIoHPhRcOv1CQa2F9q2hSTsDLS_GkymwhXaK9pJ8LXRH3iDg28ynq7pIZw3D8bAJFibS97QORZlkN_HoBBmbh-l-S6269C7hc5DU931AjQGIkGik4X-hqeJpGD25U1Znn4A3taihw.ZEtFRA.TSXLZDIxWZhyrgLHLMC4xqRUrJI'), ('session', '.eJx1UV1vgjAU_S99ZgYoIOxNhwkuLi7xI5vLQipcaxFa1pYNZvzvKy5qsmRvveeec8-9p0eU1iArwoFrdK9lAxYiecX4pcihYpXIryVTmZB5ygyCXCcaYuyHQ2zb2Ilc7KEboxQZKcGwgN-tFqbxv4TxlDasvJqUglLI-8WU4Gcrxw4sVGkq0kaB5KTqBxdpBQdi9II0eu-mShPd456IJZ3DSBW02STRy3b23SXx03oZxq-dvPG1OIC584hIloFSlxq56xZYHCYrv31eLhaPU1yLOOHbYOZ_jvpLoK2ZBJUSE5kT-N4wcqIoHPhRcOv1CQa2F9q2hSTsDLS_GkymwhXaK9pJ8LXRH3iDg28ynq7pIZw3D8bAJFibS97QORZlkN_HoBBmbh-l-S6269C7hc5DU931AjQGIkGik4X-hqeJpGD25U1Znn4A3taihw.ZGlyxw.21PNK2ZXpDLlaFpD8xok1ofnnh8'), ('page_size', '100'), ('views', '272')]) Endpoint: post_archetypes View Args: {} Person: 219733587300319234 Referrer: https://pennydreadfulmagic.com/admin/archetypes/ Request Data: {'deck_id': '216454', 'archetype_id': '363'} Content-Type: application/x-www-form-urlencoded Content-Length: 2007 Host: pennydreadfulmagic.com Connection: Keep-Alive Accept-Encoding: gzip X-Forwarded-For: 2600:1006:b051:bf3b:6ca0:10b3:5a4d:f9d6 Cf-Ray: 7ca905714ca5455e-ATL X-Forwarded-Proto: https Cf-Visitor: {"scheme":"https"} Cache-Control: max-age=0 Sec-Ch-Ua: "Google Chrome";v="113", "Chromium";v="113", "Not-A.Brand";v="24" Sec-Ch-Ua-Mobile: ?1 Sec-Ch-Ua-Platform: "Android" Upgrade-Insecure-Requests: 1 Origin: https://pennydreadfulmagic.com User-Agent: Mozilla/5.0 (Linux; Android 10; K) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/113.0.0.0 Mobile Safari/537.36 Accept: text/html,application/xhtml+xml,application/xml;q=0.9,image/avif,image/webp,image/apng,*/*;q=0.8,application/signed-exchange;v=b3;q=0.7 Sec-Fetch-Site: same-origin Sec-Fetch-Mode: navigate Sec-Fetch-User: ?1 Sec-Fetch-Dest: document Referer: https://pennydreadfulmagic.com/admin/archetypes/ Accept-Language: en-US,en;q=0.9 Cookie: hide_intro=True; session=.eJx1UV1vgjAU_S99ZgYoIOxNhwkuLi7xI5vLQipcaxFa1pYNZvzvKy5qsmRvveeec8-9p0eU1iArwoFrdK9lAxYiecX4pcihYpXIryVTmZB5ygyCXCcaYuyHQ2zb2Ilc7KEboxQZKcGwgN-tFqbxv4TxlDasvJqUglLI-8WU4Gcrxw4sVGkq0kaB5KTqBxdpBQdi9II0eu-mShPd456IJZ3DSBW02STRy3b23SXx03oZxq-dvPG1OIC584hIloFSlxq56xZYHCYrv31eLhaPU1yLOOHbYOZ_jvpLoK2ZBJUSE5kT-N4wcqIoHPhRcOv1CQa2F9q2hSTsDLS_GkymwhXaK9pJ8LXRH3iDg28ynq7pIZw3D8bAJFibS97QORZlkN_HoBBmbh-l-S6269C7hc5DU931AjQGIkGik4X-hqeJpGD25U1Znn4A3taihw.ZEtFRA.TSXLZDIxWZhyrgLHLMC4xqRUrJI; page_size=100; views=272; session=.eJx1UV1vgjAU_S99ZgYoIOxNhwkuLi7xI5vLQipcaxFa1pYNZvzvKy5qsmRvveeec8-9p0eU1iArwoFrdK9lAxYiecX4pcihYpXIryVTmZB5ygyCXCcaYuyHQ2zb2Ilc7KEboxQZKcGwgN-tFqbxv4TxlDasvJqUglLI-8WU4Gcrxw4sVGkq0kaB5KTqBxdpBQdi9II0eu-mShPd456IJZ3DSBW02STRy3b23SXx03oZxq-dvPG1OIC584hIloFSlxq56xZYHCYrv31eLhaPU1yLOOHbYOZ_jvpLoK2ZBJUSE5kT-N4wcqIoHPhRcOv1CQa2F9q2hSTsDLS_GkymwhXaK9pJ8LXRH3iDg28ynq7pIZw3D8bAJFibS97QORZlkN_HoBBmbh-l-S6269C7hc5DU931AjQGIkGik4X-hqeJpGD25U1Znn4A3taihw.ZGlyxw.21PNK2ZXpDLlaFpD8xok1ofnnh8 Priority: u=0, i Cf-Connecting-Ip: 2600:1006:b051:bf3b:6ca0:10b3:5a4d:f9d6 Cf-Ipcountry: US Cdn-Loop: cloudflare ```

Labels: decksite

vorpal-buildbot commented 1 year ago

Exceeded slow_query limit (0.7 > 0.5) in mysql: ```

        SELECT
            deck_id,
            archetype_id,
            archetype_name
        FROM
            (
    WITH rule_card_count AS
    (
        SELECT
            rule.id, COUNT(card) AS card_count
        FROM
            rule
        JOIN
            rule_card
        ON
            rule.id = rule_card.rule_id
        WHERE
            rule_card.include AND TRUE
        GROUP BY
            rule.id
    ),
    candidates AS
    (
        SELECT
            deck.id AS deck_id,
            COUNT(DISTINCT REPLACE(deck_card.card, 'Snow-Covered ', '')) AS included_count,
            MAX(rule_card_count.card_count) AS required_count,-- fake MAX due to aggregate function
            rule.id AS rule_id
        FROM
            deck
        JOIN
            deck_card
        ON
            deck.id = deck_card.deck_id
        JOIN
            (
                SELECT
                    *
                FROM
                    rule_card
                WHERE
                    include
            ) AS inclusions
        ON
            REPLACE(deck_card.card, 'Snow-Covered ', '') = inclusions.card
        JOIN
            rule
        ON
            rule.id = inclusions.rule_id
        JOIN
            rule_card_count
        ON
            rule.id = rule_card_count.id
        WHERE
            NOT deck_card.sideboard AND deck_card.n >= inclusions.n AND deck_id IN (216463, 216460, 216455, 216459, 216456, 216453, 216452, 216451, 216450, 216449, 216447, 216446, 216445, 216444, 216423, 216442, 216441, 216433, 216427, 216429, 216421, 216418, 216410, 216391, 216415, 216414, 216413, 216407, 216408, 216406, 216401, 216404, 216397, 216399, 216389, 216381, 216395, 216382, 216388, 216387, 216384, 216383, 216379, 216377, 216376, 216375) AND TRUE
        GROUP BY
            deck.id, rule.id
        HAVING
            included_count = required_count
    )
    SELECT
        candidates.deck_id,
        rule.id AS rule_id,
        suggested_archetype.id AS archetype_id,
        suggested_archetype.name AS archetype_name
    FROM
        candidates
    INNER JOIN
        rule
    ON
        candidates.rule_id = rule.id
    JOIN
        archetype AS suggested_archetype
    ON
        rule.archetype_id = suggested_archetype.id
    LEFT JOIN
        (
            SELECT
                *
            FROM
                rule_card
            WHERE
                NOT include
        ) AS exclusions
    ON
        candidates.rule_id = exclusions.rule_id
    LEFT JOIN
        deck_card
    ON
        candidates.deck_id = deck_card.deck_id AND exclusions.card = REPLACE(deck_card.card, 'Snow-Covered ', '') AND deck_card.n >= exclusions.n
    GROUP BY
        candidates.deck_id, rule_id
    HAVING
        COUNT(REPLACE(deck_card.card, 'Snow-Covered ', '')) = 0
) AS applied_rules
        GROUP BY
            deck_id
        HAVING
            COUNT(DISTINCT archetype_id) = 1
    ```

[] (slow_query, 0.7, mysql)

Reported on decksite by mysql-perf

Location Hash: bbe8ef55f158357bcd7bbb39c4fa1ec73979acf1

Request Data ``` Request Method: POST Path: /admin/archetypes/? Cookies: ImmutableMultiDict([('hide_intro', 'True'), ('session', '.eJx1UV1rgzAU_S95dkVN60ff7MY2RmVdnTg6hgS9avyKJJFVS__7YqF2DPaWc-45OTcnJxR3wBvSQivRWvIeNETShrZXkEJDG5bOkIqE8TSmikGm4doYrxwb6zo2XBMv0U1Rs4TUoFTQ3oWBGvxvoW2c97SeQ35Z0YTyHNJpTcHaS7ChWxpqZM7iXgBvSTNpy7iBiig9I70szFhIIieebk0zzLvAeSh3UAlPT4vAdIPmecxew8NNL1ml4tYnRJIEhLhi9PKxt3xaezaMm_vhAEH1vfd53R2jvuyelB-OHeUgYqIKNFaOha2l7VoL3cW2YeLbfOrU0peOrmuIQ6aoYg5J0zDCgzf2UeR7Ufa43W2g9AsPj28We1chqtNOveZTtah-imaD4i6difmwKJmK-NLQ5dJYDpMBbYBw4Oisob8FSsJzUDu3fV2ffwDrUKg6.ZE8QBQ.L9SV7vchBRFRIRTB5P9SwRZfjYE'), ('session', '.eJx1UV1rgzAU_S95dkVN60ff7MY2RmVdnTg6hgS9avyKJJFVS__7YqF2DPaWc-45OTcnJxR3wBvSQivRWvIeNETShrZXkEJDG5bOkIqE8TSmikGm4doYrxwb6zo2XBMv0U1Rs4TUoFTQ3oWBGvxvoW2c97SeQ35Z0YTyHNJpTcHaS7ChWxpqZM7iXgBvSTNpy7iBiig9I70szFhIIieebk0zzLvAeSh3UAlPT4vAdIPmecxew8NNL1ml4tYnRJIEhLhi9PKxt3xaezaMm_vhAEH1vfd53R2jvuyelB-OHeUgYqIKNFaOha2l7VoL3cW2YeLbfOrU0peOrmuIQ6aoYg5J0zDCgzf2UeR7Ufa43W2g9AsPj28We1chqtNOveZTtah-imaD4i6difmwKJmK-NLQ5dJYDpMBbYBw4Oisob8FSsJzUDu3fV2ffwDrUKg6.ZGmluQ.hy5n2CdMk3slOQNbN-tFPdkdyi8'), ('views', '13')]) Endpoint: post_archetypes View Args: {} Person: 219733587300319234 Referrer: https://pennydreadfulmagic.com/admin/archetypes/ Request Data: {'deck_id': '216466', 'archetype_id': '73'} Content-Type: application/x-www-form-urlencoded Content-Length: 1553 Host: pennydreadfulmagic.com Connection: Keep-Alive Accept-Encoding: gzip X-Forwarded-For: 97.103.253.204 Cf-Ray: 7caa43729bae05ee-IAD X-Forwarded-Proto: https Cf-Visitor: {"scheme":"https"} Cache-Control: max-age=0 Sec-Ch-Ua: "Google Chrome";v="113", "Chromium";v="113", "Not-A.Brand";v="24" Sec-Ch-Ua-Mobile: ?0 Sec-Ch-Ua-Platform: "Windows" Upgrade-Insecure-Requests: 1 Origin: https://pennydreadfulmagic.com User-Agent: Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/113.0.0.0 Safari/537.36 Accept: text/html,application/xhtml+xml,application/xml;q=0.9,image/avif,image/webp,image/apng,*/*;q=0.8,application/signed-exchange;v=b3;q=0.7 Sec-Fetch-Site: same-origin Sec-Fetch-Mode: navigate Sec-Fetch-User: ?1 Sec-Fetch-Dest: document Referer: https://pennydreadfulmagic.com/admin/archetypes/ Accept-Language: en-US,en;q=0.9 Cookie: hide_intro=True; session=.eJx1UV1rgzAU_S95dkVN60ff7MY2RmVdnTg6hgS9avyKJJFVS__7YqF2DPaWc-45OTcnJxR3wBvSQivRWvIeNETShrZXkEJDG5bOkIqE8TSmikGm4doYrxwb6zo2XBMv0U1Rs4TUoFTQ3oWBGvxvoW2c97SeQ35Z0YTyHNJpTcHaS7ChWxpqZM7iXgBvSTNpy7iBiig9I70szFhIIieebk0zzLvAeSh3UAlPT4vAdIPmecxew8NNL1ml4tYnRJIEhLhi9PKxt3xaezaMm_vhAEH1vfd53R2jvuyelB-OHeUgYqIKNFaOha2l7VoL3cW2YeLbfOrU0peOrmuIQ6aoYg5J0zDCgzf2UeR7Ufa43W2g9AsPj28We1chqtNOveZTtah-imaD4i6difmwKJmK-NLQ5dJYDpMBbYBw4Oisob8FSsJzUDu3fV2ffwDrUKg6.ZE8QBQ.L9SV7vchBRFRIRTB5P9SwRZfjYE; views=13; session=.eJx1UV1rgzAU_S95dkVN60ff7MY2RmVdnTg6hgS9avyKJJFVS__7YqF2DPaWc-45OTcnJxR3wBvSQivRWvIeNETShrZXkEJDG5bOkIqE8TSmikGm4doYrxwb6zo2XBMv0U1Rs4TUoFTQ3oWBGvxvoW2c97SeQ35Z0YTyHNJpTcHaS7ChWxpqZM7iXgBvSTNpy7iBiig9I70szFhIIieebk0zzLvAeSh3UAlPT4vAdIPmecxew8NNL1ml4tYnRJIEhLhi9PKxt3xaezaMm_vhAEH1vfd53R2jvuyelB-OHeUgYqIKNFaOha2l7VoL3cW2YeLbfOrU0peOrmuIQ6aoYg5J0zDCgzf2UeR7Ufa43W2g9AsPj28We1chqtNOveZTtah-imaD4i6difmwKJmK-NLQ5dJYDpMBbYBw4Oisob8FSsJzUDu3fV2ffwDrUKg6.ZGmluQ.hy5n2CdMk3slOQNbN-tFPdkdyi8 Priority: u=0, i Cf-Connecting-Ip: 97.103.253.204 Cf-Ipcountry: US Cdn-Loop: cloudflare ```

Labels: decksite

vorpal-buildbot commented 1 year ago

Exceeded slow_query limit (0.6 > 0.5) in mysql: ```

        SELECT
            deck_id,
            archetype_id,
            archetype_name
        FROM
            (
    WITH rule_card_count AS
    (
        SELECT
            rule.id, COUNT(card) AS card_count
        FROM
            rule
        JOIN
            rule_card
        ON
            rule.id = rule_card.rule_id
        WHERE
            rule_card.include AND TRUE
        GROUP BY
            rule.id
    ),
    candidates AS
    (
        SELECT
            deck.id AS deck_id,
            COUNT(DISTINCT REPLACE(deck_card.card, 'Snow-Covered ', '')) AS included_count,
            MAX(rule_card_count.card_count) AS required_count,-- fake MAX due to aggregate function
            rule.id AS rule_id
        FROM
            deck
        JOIN
            deck_card
        ON
            deck.id = deck_card.deck_id
        JOIN
            (
                SELECT
                    *
                FROM
                    rule_card
                WHERE
                    include
            ) AS inclusions
        ON
            REPLACE(deck_card.card, 'Snow-Covered ', '') = inclusions.card
        JOIN
            rule
        ON
            rule.id = inclusions.rule_id
        JOIN
            rule_card_count
        ON
            rule.id = rule_card_count.id
        WHERE
            NOT deck_card.sideboard AND deck_card.n >= inclusions.n AND deck_id IN (216590, 216589, 216588, 216585, 216586, 216587, 216578, 216584, 216579, 216575, 216574, 216571, 216566, 216570, 216569, 216563, 216558, 216550, 216549, 216534, 216545, 216542, 216540, 216537, 216528, 216527, 216514, 216524, 216522, 216520, 216515, 216517, 216513, 216512, 216507, 216511, 216510, 216508, 216506, 216505) AND TRUE
        GROUP BY
            deck.id, rule.id
        HAVING
            included_count = required_count
    )
    SELECT
        candidates.deck_id,
        rule.id AS rule_id,
        suggested_archetype.id AS archetype_id,
        suggested_archetype.name AS archetype_name
    FROM
        candidates
    INNER JOIN
        rule
    ON
        candidates.rule_id = rule.id
    JOIN
        archetype AS suggested_archetype
    ON
        rule.archetype_id = suggested_archetype.id
    LEFT JOIN
        (
            SELECT
                *
            FROM
                rule_card
            WHERE
                NOT include
        ) AS exclusions
    ON
        candidates.rule_id = exclusions.rule_id
    LEFT JOIN
        deck_card
    ON
        candidates.deck_id = deck_card.deck_id AND exclusions.card = REPLACE(deck_card.card, 'Snow-Covered ', '') AND deck_card.n >= exclusions.n
    GROUP BY
        candidates.deck_id, rule_id
    HAVING
        COUNT(REPLACE(deck_card.card, 'Snow-Covered ', '')) = 0
) AS applied_rules
        GROUP BY
            deck_id
        HAVING
            COUNT(DISTINCT archetype_id) = 1
    ```

[] (slow_query, 0.6, mysql)

Reported on decksite by mysql-perf

Location Hash: bbe8ef55f158357bcd7bbb39c4fa1ec73979acf1

Request Data ``` Request Method: POST Path: /admin/archetypes/? Cookies: ImmutableMultiDict([('hide_intro', 'True'), ('hide_intro', 'True'), ('views', '792'), ('views', '791'), ('session', '.eJx1UV1rwkAQ_C_3nMolXuKlbw0VbaFQqlhrKeHIXeLV-wj3YRPF_96LoEKhj7M7O7M7ewRly4wkiikH7p3xLAKESq4ugDLJpaZXyG2lDS15qIA4ReNsjFGC0ngyRmmCwY0hdEUECyym7mZFaPw_wlXZeC6uJkI3DaPDYlars1WcZxGQrtGlt8woIgdhywWxxPRBQRPvtklpHXFDZ1Y5ipbZ7oC7QrTWr3Kxn__kHX_N91je-E7vWLj0CEhVMWsvGKyk7d-eHusXOu3qqc8L0W2p2dDFJMUfJsyzruWG2ZKE0OIsQynKMwxHEKUYw_GtP-SYQYQhjIBhdShtrybL5838sLG6bfsU0hhqKx8k8otkt27f18Ek5NiGaz5DcuE7vB4OPedkR986KF8Q-IrAWbR0_TAACkYMM-AUgb8ROmIaFnZWXojTL_36pfA.ZGt1GA.gp-M7UU0bfNJ8m8UjLCDUwCY7_g'), ('session', '.eJx1UV1rwkAQ_C_3nMolXuKlbw0VbaFQqlhrKeHIXeLV-wj3YRPF_96LoEKhj7M7O7M7ewRly4wkiikH7p3xLAKESq4ugDLJpaZXyG2lDS15qIA4ReNsjFGC0ngyRmmCwY0hdEUECyym7mZFaPw_wlXZeC6uJkI3DaPDYlars1WcZxGQrtGlt8woIgdhywWxxPRBQRPvtklpHXFDZ1Y5ipbZ7oC7QrTWr3Kxn__kHX_N91je-E7vWLj0CEhVMWsvGKyk7d-eHusXOu3qqc8L0W2p2dDFJMUfJsyzruWG2ZKE0OIsQynKMwxHEKUYw_GtP-SYQYQhjIBhdShtrybL5838sLG6bfsU0hhqKx8k8otkt27f18Ek5NiGaz5DcuE7vB4OPedkR986KF8Q-IrAWbR0_TAACkYMM-AUgb8ROmIaFnZWXojTL_36pfA.ZGpbbw.WUrJsXeqV-nJL50QaNBRqp38Jjg'), ('page_size', '20'), ('deck_id', '199486')]) Endpoint: post_archetypes View Args: {} Person: 154363842451734528 Referrer: https://pennydreadfulmagic.com/admin/archetypes/ Request Data: {'deck_id': '216590', 'archetype_id': ''} Content-Type: application/x-www-form-urlencoded Content-Length: 1318 Host: pennydreadfulmagic.com Connection: Keep-Alive Accept-Encoding: gzip X-Forwarded-For: 119.17.133.163 Cf-Ray: 7cb593937e572b36-MEL X-Forwarded-Proto: https Cf-Visitor: {"scheme":"https"} Priority: u=1 User-Agent: Mozilla/5.0 (Windows NT 10.0; Win64; x64; rv:109.0) Gecko/20100101 Firefox/113.0 Accept: text/html,application/xhtml+xml,application/xml;q=0.9,image/avif,image/webp,*/*;q=0.8 Accept-Language: en-AU,en-GB;q=0.8,en-US;q=0.5,en;q=0.3 Origin: https://pennydreadfulmagic.com Dnt: 1 Referer: https://pennydreadfulmagic.com/admin/archetypes/ Upgrade-Insecure-Requests: 1 Sec-Fetch-Dest: document Sec-Fetch-Mode: navigate Sec-Fetch-Site: same-origin Sec-Fetch-User: ?1 Cookie: hide_intro=True; views=792; hide_intro=True; session=.eJx1UV1rwkAQ_C_3nMolXuKlbw0VbaFQqlhrKeHIXeLV-wj3YRPF_96LoEKhj7M7O7M7ewRly4wkiikH7p3xLAKESq4ugDLJpaZXyG2lDS15qIA4ReNsjFGC0ngyRmmCwY0hdEUECyym7mZFaPw_wlXZeC6uJkI3DaPDYlars1WcZxGQrtGlt8woIgdhywWxxPRBQRPvtklpHXFDZ1Y5ipbZ7oC7QrTWr3Kxn__kHX_N91je-E7vWLj0CEhVMWsvGKyk7d-eHusXOu3qqc8L0W2p2dDFJMUfJsyzruWG2ZKE0OIsQynKMwxHEKUYw_GtP-SYQYQhjIBhdShtrybL5838sLG6bfsU0hhqKx8k8otkt27f18Ek5NiGaz5DcuE7vB4OPedkR986KF8Q-IrAWbR0_TAACkYMM-AUgb8ROmIaFnZWXojTL_36pfA.ZGt1GA.gp-M7UU0bfNJ8m8UjLCDUwCY7_g; session=.eJx1UV1rwkAQ_C_3nMolXuKlbw0VbaFQqlhrKeHIXeLV-wj3YRPF_96LoEKhj7M7O7M7ewRly4wkiikH7p3xLAKESq4ugDLJpaZXyG2lDS15qIA4ReNsjFGC0ngyRmmCwY0hdEUECyym7mZFaPw_wlXZeC6uJkI3DaPDYlars1WcZxGQrtGlt8woIgdhywWxxPRBQRPvtklpHXFDZ1Y5ipbZ7oC7QrTWr3Kxn__kHX_N91je-E7vWLj0CEhVMWsvGKyk7d-eHusXOu3qqc8L0W2p2dDFJMUfJsyzruWG2ZKE0OIsQynKMwxHEKUYw_GtP-SYQYQhjIBhdShtrybL5838sLG6bfsU0hhqKx8k8otkt27f18Ek5NiGaz5DcuE7vB4OPedkR986KF8Q-IrAWbR0_TAACkYMM-AUgb8ROmIaFnZWXojTL_36pfA.ZGpbbw.WUrJsXeqV-nJL50QaNBRqp38Jjg; page_size=20; deck_id=199486; views=791 Cf-Connecting-Ip: 119.17.133.163 Cf-Ipcountry: AU Cdn-Loop: cloudflare ```

Labels: decksite

vorpal-buildbot commented 1 year ago

Exceeded slow_query limit (4.7 > 0.5) in mysql: ```

        SELECT
            deck_id,
            archetype_id,
            archetype_name
        FROM
            (
    WITH rule_card_count AS
    (
        SELECT
            rule.id, COUNT(card) AS card_count
        FROM
            rule
        JOIN
            rule_card
        ON
            rule.id = rule_card.rule_id
        WHERE
            rule_card.include AND TRUE
        GROUP BY
            rule.id
    ),
    candidates AS
    (
        SELECT
            deck.id AS deck_id,
            COUNT(DISTINCT REPLACE(deck_card.card, 'Snow-Covered ', '')) AS included_count,
            MAX(rule_card_count.card_count) AS required_count,-- fake MAX due to aggregate function
            rule.id AS rule_id
        FROM
            deck
        JOIN
            deck_card
        ON
            deck.id = deck_card.deck_id
        JOIN
            (
                SELECT
                    *
                FROM
                    rule_card
                WHERE
                    include
            ) AS inclusions
        ON
            REPLACE(deck_card.card, 'Snow-Covered ', '') = inclusions.card
        JOIN
            rule
        ON
            rule.id = inclusions.rule_id
        JOIN
            rule_card_count
        ON
            rule.id = rule_card_count.id
        WHERE
            NOT deck_card.sideboard AND deck_card.n >= inclusions.n AND deck_id IN (216666) AND TRUE
        GROUP BY
            deck.id, rule.id
        HAVING
            included_count = required_count
    )
    SELECT
        candidates.deck_id,
        rule.id AS rule_id,
        suggested_archetype.id AS archetype_id,
        suggested_archetype.name AS archetype_name
    FROM
        candidates
    INNER JOIN
        rule
    ON
        candidates.rule_id = rule.id
    JOIN
        archetype AS suggested_archetype
    ON
        rule.archetype_id = suggested_archetype.id
    LEFT JOIN
        (
            SELECT
                *
            FROM
                rule_card
            WHERE
                NOT include
        ) AS exclusions
    ON
        candidates.rule_id = exclusions.rule_id
    LEFT JOIN
        deck_card
    ON
        candidates.deck_id = deck_card.deck_id AND exclusions.card = REPLACE(deck_card.card, 'Snow-Covered ', '') AND deck_card.n >= exclusions.n
    GROUP BY
        candidates.deck_id, rule_id
    HAVING
        COUNT(REPLACE(deck_card.card, 'Snow-Covered ', '')) = 0
) AS applied_rules
        GROUP BY
            deck_id
        HAVING
            COUNT(DISTINCT archetype_id) = 1
    ```

[] (slow_query, 4.7, mysql)

Reported on decksite by mysql-perf

Location Hash: bbe8ef55f158357bcd7bbb39c4fa1ec73979acf1

Request Data ``` Request Method: POST Path: /admin/archetypes/? Cookies: ImmutableMultiDict([('hide_intro', 'True'), ('session', '.eJx1UV1rgzAU_S95dkVN60ff7MY2RmVdnTg6hgS9avyKJJFVS__7YqF2DPaWc-45OTcnJxR3wBvSQivRWvIeNETShrZXkEJDG5bOkIqE8TSmikGm4doYrxwb6zo2XBMv0U1Rs4TUoFTQ3oWBGvxvoW2c97SeQ35Z0YTyHNJpTcHaS7ChWxpqZM7iXgBvSTNpy7iBiig9I70szFhIIieebk0zzLvAeSh3UAlPT4vAdIPmecxew8NNL1ml4tYnRJIEhLhi9PKxt3xaezaMm_vhAEH1vfd53R2jvuyelB-OHeUgYqIKNFaOha2l7VoL3cW2YeLbfOrU0peOrmuIQ6aoYg5J0zDCgzf2UeR7Ufa43W2g9AsPj28We1chqtNOveZTtah-imaD4i6difmwKJmK-NLQ5dJYDpMBbYBw4Oisob8FSsJzUDu3fV2ffwDrUKg6.ZE8QBQ.L9SV7vchBRFRIRTB5P9SwRZfjYE'), ('session', '.eJx1UV1rgzAU_S95dkVN60ff7MY2RmVdnTg6hgS9avyKJJFVS__7YqF2DPaWc-45OTcnJxR3wBvSQivRWvIeNETShrZXkEJDG5bOkIqE8TSmikGm4doYrxwb6zo2XBMv0U1Rs4TUoFTQ3oWBGvxvoW2c97SeQ35Z0YTyHNJpTcHaS7ChWxpqZM7iXgBvSTNpy7iBiig9I70szFhIIieebk0zzLvAeSh3UAlPT4vAdIPmecxew8NNL1ml4tYnRJIEhLhi9PKxt3xaezaMm_vhAEH1vfd53R2jvuyelB-OHeUgYqIKNFaOha2l7VoL3cW2YeLbfOrU0peOrmuIQ6aoYg5J0zDCgzf2UeR7Ufa43W2g9AsPj28We1chqtNOveZTtah-imaD4i6difmwKJmK-NLQ5dJYDpMBbYBw4Oisob8FSsJzUDu3fV2ffwDrUKg6.ZGywFw.AvAXV4XbxoBLCDFsRF7F_8ZgR8Y'), ('views', '26')]) Endpoint: post_archetypes View Args: {} Person: 219733587300319234 Referrer: https://pennydreadfulmagic.com/admin/archetypes/ Request Data: {'deck_id': '216665', 'archetype_id': '486'} Content-Type: application/x-www-form-urlencoded Content-Length: 496 Host: pennydreadfulmagic.com Connection: Keep-Alive Accept-Encoding: gzip X-Forwarded-For: 97.103.253.204 Cf-Ray: 7cbd44a6ca7213bb-IAD X-Forwarded-Proto: https Cf-Visitor: {"scheme":"https"} Cache-Control: max-age=0 Sec-Ch-Ua: "Google Chrome";v="113", "Chromium";v="113", "Not-A.Brand";v="24" Sec-Ch-Ua-Mobile: ?0 Sec-Ch-Ua-Platform: "Windows" Upgrade-Insecure-Requests: 1 Origin: https://pennydreadfulmagic.com User-Agent: Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/113.0.0.0 Safari/537.36 Accept: text/html,application/xhtml+xml,application/xml;q=0.9,image/avif,image/webp,image/apng,*/*;q=0.8,application/signed-exchange;v=b3;q=0.7 Sec-Fetch-Site: same-origin Sec-Fetch-Mode: navigate Sec-Fetch-User: ?1 Sec-Fetch-Dest: document Referer: https://pennydreadfulmagic.com/admin/archetypes/ Accept-Language: en-US,en;q=0.9 Cookie: hide_intro=True; session=.eJx1UV1rgzAU_S95dkVN60ff7MY2RmVdnTg6hgS9avyKJJFVS__7YqF2DPaWc-45OTcnJxR3wBvSQivRWvIeNETShrZXkEJDG5bOkIqE8TSmikGm4doYrxwb6zo2XBMv0U1Rs4TUoFTQ3oWBGvxvoW2c97SeQ35Z0YTyHNJpTcHaS7ChWxpqZM7iXgBvSTNpy7iBiig9I70szFhIIieebk0zzLvAeSh3UAlPT4vAdIPmecxew8NNL1ml4tYnRJIEhLhi9PKxt3xaezaMm_vhAEH1vfd53R2jvuyelB-OHeUgYqIKNFaOha2l7VoL3cW2YeLbfOrU0peOrmuIQ6aoYg5J0zDCgzf2UeR7Ufa43W2g9AsPj28We1chqtNOveZTtah-imaD4i6difmwKJmK-NLQ5dJYDpMBbYBw4Oisob8FSsJzUDu3fV2ffwDrUKg6.ZE8QBQ.L9SV7vchBRFRIRTB5P9SwRZfjYE; session=.eJx1UV1rgzAU_S95dkVN60ff7MY2RmVdnTg6hgS9avyKJJFVS__7YqF2DPaWc-45OTcnJxR3wBvSQivRWvIeNETShrZXkEJDG5bOkIqE8TSmikGm4doYrxwb6zo2XBMv0U1Rs4TUoFTQ3oWBGvxvoW2c97SeQ35Z0YTyHNJpTcHaS7ChWxpqZM7iXgBvSTNpy7iBiig9I70szFhIIieebk0zzLvAeSh3UAlPT4vAdIPmecxew8NNL1ml4tYnRJIEhLhi9PKxt3xaezaMm_vhAEH1vfd53R2jvuyelB-OHeUgYqIKNFaOha2l7VoL3cW2YeLbfOrU0peOrmuIQ6aoYg5J0zDCgzf2UeR7Ufa43W2g9AsPj28We1chqtNOveZTtah-imaD4i6difmwKJmK-NLQ5dJYDpMBbYBw4Oisob8FSsJzUDu3fV2ffwDrUKg6.ZGywFw.AvAXV4XbxoBLCDFsRF7F_8ZgR8Y; views=26 Priority: u=0, i Cf-Connecting-Ip: 97.103.253.204 Cf-Ipcountry: US Cdn-Loop: cloudflare ```

Labels: decksite

vorpal-buildbot commented 1 year ago

Exceeded slow_query limit (4.7 > 0.5) in mysql: ```

        SELECT
            deck_id,
            archetype_id,
            archetype_name
        FROM
            (
    WITH rule_card_count AS
    (
        SELECT
            rule.id, COUNT(card) AS card_count
        FROM
            rule
        JOIN
            rule_card
        ON
            rule.id = rule_card.rule_id
        WHERE
            rule_card.include AND TRUE
        GROUP BY
            rule.id
    ),
    candidates AS
    (
        SELECT
            deck.id AS deck_id,
            COUNT(DISTINCT REPLACE(deck_card.card, 'Snow-Covered ', '')) AS included_count,
            MAX(rule_card_count.card_count) AS required_count,-- fake MAX due to aggregate function
            rule.id AS rule_id
        FROM
            deck
        JOIN
            deck_card
        ON
            deck.id = deck_card.deck_id
        JOIN
            (
                SELECT
                    *
                FROM
                    rule_card
                WHERE
                    include
            ) AS inclusions
        ON
            REPLACE(deck_card.card, 'Snow-Covered ', '') = inclusions.card
        JOIN
            rule
        ON
            rule.id = inclusions.rule_id
        JOIN
            rule_card_count
        ON
            rule.id = rule_card_count.id
        WHERE
            NOT deck_card.sideboard AND deck_card.n >= inclusions.n AND deck_id IN (216673) AND TRUE
        GROUP BY
            deck.id, rule.id
        HAVING
            included_count = required_count
    )
    SELECT
        candidates.deck_id,
        rule.id AS rule_id,
        suggested_archetype.id AS archetype_id,
        suggested_archetype.name AS archetype_name
    FROM
        candidates
    INNER JOIN
        rule
    ON
        candidates.rule_id = rule.id
    JOIN
        archetype AS suggested_archetype
    ON
        rule.archetype_id = suggested_archetype.id
    LEFT JOIN
        (
            SELECT
                *
            FROM
                rule_card
            WHERE
                NOT include
        ) AS exclusions
    ON
        candidates.rule_id = exclusions.rule_id
    LEFT JOIN
        deck_card
    ON
        candidates.deck_id = deck_card.deck_id AND exclusions.card = REPLACE(deck_card.card, 'Snow-Covered ', '') AND deck_card.n >= exclusions.n
    GROUP BY
        candidates.deck_id, rule_id
    HAVING
        COUNT(REPLACE(deck_card.card, 'Snow-Covered ', '')) = 0
) AS applied_rules
        GROUP BY
            deck_id
        HAVING
            COUNT(DISTINCT archetype_id) = 1
    ```

[] (slow_query, 4.7, mysql)

Reported on decksite by mysql-perf

Location Hash: bbe8ef55f158357bcd7bbb39c4fa1ec73979acf1

Request Data ``` Request Method: POST Path: /admin/archetypes/? Cookies: ImmutableMultiDict([('hide_intro', 'True'), ('session', '.eJx1UV1vgjAU_S99ZgYoIOxNhwkuLi7xI5vLQipcaxFa1pYNZvzvKy5qsmRvveeec8-9p0eU1iArwoFrdK9lAxYiecX4pcihYpXIryVTmZB5ygyCXCcaYuyHQ2zb2Ilc7KEboxQZKcGwgN-tFqbxv4TxlDasvJqUglLI-8WU4Gcrxw4sVGkq0kaB5KTqBxdpBQdi9II0eu-mShPd456IJZ3DSBW02STRy3b23SXx03oZxq-dvPG1OIC584hIloFSlxq56xZYHCYrv31eLhaPU1yLOOHbYOZ_jvpLoK2ZBJUSE5kT-N4wcqIoHPhRcOv1CQa2F9q2hSTsDLS_GkymwhXaK9pJ8LXRH3iDg28ynq7pIZw3D8bAJFibS97QORZlkN_HoBBmbh-l-S6269C7hc5DU931AjQGIkGik4X-hqeJpGD25U1Znn4A3taihw.ZEtFRA.TSXLZDIxWZhyrgLHLMC4xqRUrJI'), ('session', '.eJx1UV1vgjAU_S99ZgYoIOxNhwkuLi7xI5vLQipcaxFa1pYNZvzvKy5qsmRvveeec8-9p0eU1iArwoFrdK9lAxYiecX4pcihYpXIryVTmZB5ygyCXCcaYuyHQ2zb2Ilc7KEboxQZKcGwgN-tFqbxv4TxlDasvJqUglLI-8WU4Gcrxw4sVGkq0kaB5KTqBxdpBQdi9II0eu-mShPd456IJZ3DSBW02STRy3b23SXx03oZxq-dvPG1OIC584hIloFSlxq56xZYHCYrv31eLhaPU1yLOOHbYOZ_jvpLoK2ZBJUSE5kT-N4wcqIoHPhRcOv1CQa2F9q2hSTsDLS_GkymwhXaK9pJ8LXRH3iDg28ynq7pIZw3D8bAJFibS97QORZlkN_HoBBmbh-l-S6269C7hc5DU931AjQGIkGik4X-hqeJpGD25U1Znn4A3taihw.ZGzpYQ.qC38wna_lPIPEIeprZdXlPy9GgU'), ('page_size', '100'), ('views', '535')]) Endpoint: post_archetypes View Args: {} Person: 219733587300319234 Referrer: https://pennydreadfulmagic.com/admin/archetypes/ Request Data: {'deck_id': '216673', 'archetype_id': ''} Content-Type: application/x-www-form-urlencoded Content-Length: 123 Host: pennydreadfulmagic.com Connection: Keep-Alive Accept-Encoding: gzip X-Forwarded-For: 2600:1006:b121:73d1:8895:2fbc:232d:d705 Cf-Ray: 7cbeaa654943b0a0-ATL X-Forwarded-Proto: https Cf-Visitor: {"scheme":"https"} Cache-Control: max-age=0 Sec-Ch-Ua: "Google Chrome";v="113", "Chromium";v="113", "Not-A.Brand";v="24" Sec-Ch-Ua-Mobile: ?1 Sec-Ch-Ua-Platform: "Android" Upgrade-Insecure-Requests: 1 Origin: https://pennydreadfulmagic.com User-Agent: Mozilla/5.0 (Linux; Android 10; K) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/113.0.0.0 Mobile Safari/537.36 Accept: text/html,application/xhtml+xml,application/xml;q=0.9,image/avif,image/webp,image/apng,*/*;q=0.8,application/signed-exchange;v=b3;q=0.7 Sec-Fetch-Site: same-origin Sec-Fetch-Mode: navigate Sec-Fetch-User: ?1 Sec-Fetch-Dest: document Referer: https://pennydreadfulmagic.com/admin/archetypes/ Accept-Language: en-US,en;q=0.9 Cookie: hide_intro=True; session=.eJx1UV1vgjAU_S99ZgYoIOxNhwkuLi7xI5vLQipcaxFa1pYNZvzvKy5qsmRvveeec8-9p0eU1iArwoFrdK9lAxYiecX4pcihYpXIryVTmZB5ygyCXCcaYuyHQ2zb2Ilc7KEboxQZKcGwgN-tFqbxv4TxlDasvJqUglLI-8WU4Gcrxw4sVGkq0kaB5KTqBxdpBQdi9II0eu-mShPd456IJZ3DSBW02STRy3b23SXx03oZxq-dvPG1OIC584hIloFSlxq56xZYHCYrv31eLhaPU1yLOOHbYOZ_jvpLoK2ZBJUSE5kT-N4wcqIoHPhRcOv1CQa2F9q2hSTsDLS_GkymwhXaK9pJ8LXRH3iDg28ynq7pIZw3D8bAJFibS97QORZlkN_HoBBmbh-l-S6269C7hc5DU931AjQGIkGik4X-hqeJpGD25U1Znn4A3taihw.ZEtFRA.TSXLZDIxWZhyrgLHLMC4xqRUrJI; page_size=100; views=535; session=.eJx1UV1vgjAU_S99ZgYoIOxNhwkuLi7xI5vLQipcaxFa1pYNZvzvKy5qsmRvveeec8-9p0eU1iArwoFrdK9lAxYiecX4pcihYpXIryVTmZB5ygyCXCcaYuyHQ2zb2Ilc7KEboxQZKcGwgN-tFqbxv4TxlDasvJqUglLI-8WU4Gcrxw4sVGkq0kaB5KTqBxdpBQdi9II0eu-mShPd456IJZ3DSBW02STRy3b23SXx03oZxq-dvPG1OIC584hIloFSlxq56xZYHCYrv31eLhaPU1yLOOHbYOZ_jvpLoK2ZBJUSE5kT-N4wcqIoHPhRcOv1CQa2F9q2hSTsDLS_GkymwhXaK9pJ8LXRH3iDg28ynq7pIZw3D8bAJFibS97QORZlkN_HoBBmbh-l-S6269C7hc5DU931AjQGIkGik4X-hqeJpGD25U1Znn4A3taihw.ZGzpYQ.qC38wna_lPIPEIeprZdXlPy9GgU Priority: u=0, i Cf-Connecting-Ip: 2600:1006:b121:73d1:8895:2fbc:232d:d705 Cf-Ipcountry: US Cdn-Loop: cloudflare ```

Labels: decksite

vorpal-buildbot commented 1 year ago

Exceeded slow_query limit (4.7 > 0.5) in mysql: ```

        SELECT
            deck_id,
            archetype_id,
            archetype_name
        FROM
            (
    WITH rule_card_count AS
    (
        SELECT
            rule.id, COUNT(card) AS card_count
        FROM
            rule
        JOIN
            rule_card
        ON
            rule.id = rule_card.rule_id
        WHERE
            rule_card.include AND TRUE
        GROUP BY
            rule.id
    ),
    candidates AS
    (
        SELECT
            deck.id AS deck_id,
            COUNT(DISTINCT REPLACE(deck_card.card, 'Snow-Covered ', '')) AS included_count,
            MAX(rule_card_count.card_count) AS required_count,-- fake MAX due to aggregate function
            rule.id AS rule_id
        FROM
            deck
        JOIN
            deck_card
        ON
            deck.id = deck_card.deck_id
        JOIN
            (
                SELECT
                    *
                FROM
                    rule_card
                WHERE
                    include
            ) AS inclusions
        ON
            REPLACE(deck_card.card, 'Snow-Covered ', '') = inclusions.card
        JOIN
            rule
        ON
            rule.id = inclusions.rule_id
        JOIN
            rule_card_count
        ON
            rule.id = rule_card_count.id
        WHERE
            NOT deck_card.sideboard AND deck_card.n >= inclusions.n AND deck_id IN (216679) AND TRUE
        GROUP BY
            deck.id, rule.id
        HAVING
            included_count = required_count
    )
    SELECT
        candidates.deck_id,
        rule.id AS rule_id,
        suggested_archetype.id AS archetype_id,
        suggested_archetype.name AS archetype_name
    FROM
        candidates
    INNER JOIN
        rule
    ON
        candidates.rule_id = rule.id
    JOIN
        archetype AS suggested_archetype
    ON
        rule.archetype_id = suggested_archetype.id
    LEFT JOIN
        (
            SELECT
                *
            FROM
                rule_card
            WHERE
                NOT include
        ) AS exclusions
    ON
        candidates.rule_id = exclusions.rule_id
    LEFT JOIN
        deck_card
    ON
        candidates.deck_id = deck_card.deck_id AND exclusions.card = REPLACE(deck_card.card, 'Snow-Covered ', '') AND deck_card.n >= exclusions.n
    GROUP BY
        candidates.deck_id, rule_id
    HAVING
        COUNT(REPLACE(deck_card.card, 'Snow-Covered ', '')) = 0
) AS applied_rules
        GROUP BY
            deck_id
        HAVING
            COUNT(DISTINCT archetype_id) = 1
    ```

[] (slow_query, 4.7, mysql)

Reported on decksite by mysql-perf

Location Hash: bbe8ef55f158357bcd7bbb39c4fa1ec73979acf1

Request Data ``` Request Method: POST Path: /admin/archetypes/? Cookies: ImmutableMultiDict([('hide_intro', 'True'), ('session', '.eJx1UV1rgzAU_S95dkVN60ff7MY2RmVdnTg6hgS9avyKJJFVS__7YqF2DPaWc-45OTcnJxR3wBvSQivRWvIeNETShrZXkEJDG5bOkIqE8TSmikGm4doYrxwb6zo2XBMv0U1Rs4TUoFTQ3oWBGvxvoW2c97SeQ35Z0YTyHNJpTcHaS7ChWxpqZM7iXgBvSTNpy7iBiig9I70szFhIIieebk0zzLvAeSh3UAlPT4vAdIPmecxew8NNL1ml4tYnRJIEhLhi9PKxt3xaezaMm_vhAEH1vfd53R2jvuyelB-OHeUgYqIKNFaOha2l7VoL3cW2YeLbfOrU0peOrmuIQ6aoYg5J0zDCgzf2UeR7Ufa43W2g9AsPj28We1chqtNOveZTtah-imaD4i6difmwKJmK-NLQ5dJYDpMBbYBw4Oisob8FSsJzUDu3fV2ffwDrUKg6.ZE8QBQ.L9SV7vchBRFRIRTB5P9SwRZfjYE'), ('session', '.eJx1UV1rgzAU_S95dkVN60ff7MY2RmVdnTg6hgS9avyKJJFVS__7YqF2DPaWc-45OTcnJxR3wBvSQivRWvIeNETShrZXkEJDG5bOkIqE8TSmikGm4doYrxwb6zo2XBMv0U1Rs4TUoFTQ3oWBGvxvoW2c97SeQ35Z0YTyHNJpTcHaS7ChWxpqZM7iXgBvSTNpy7iBiig9I70szFhIIieebk0zzLvAeSh3UAlPT4vAdIPmecxew8NNL1ml4tYnRJIEhLhi9PKxt3xaezaMm_vhAEH1vfd53R2jvuyelB-OHeUgYqIKNFaOha2l7VoL3cW2YeLbfOrU0peOrmuIQ6aoYg5J0zDCgzf2UeR7Ufa43W2g9AsPj28We1chqtNOveZTtah-imaD4i6difmwKJmK-NLQ5dJYDpMBbYBw4Oisob8FSsJzUDu3fV2ffwDrUKg6.ZG0T8Q.VhZQ-HyETLXkrWjDyjnzD9JJ0hU'), ('views', '9')]) Endpoint: post_archetypes View Args: {} Person: 219733587300319234 Referrer: https://pennydreadfulmagic.com/admin/archetypes/ Request Data: {'deck_id': '216678', 'archetype_id': '144'} Content-Type: application/x-www-form-urlencoded Content-Length: 188 Host: pennydreadfulmagic.com Connection: Keep-Alive Accept-Encoding: gzip X-Forwarded-For: 97.103.253.204 Cf-Ray: 7cbfb6146f000aa7-IAD X-Forwarded-Proto: https Cf-Visitor: {"scheme":"https"} Cache-Control: max-age=0 Sec-Ch-Ua: "Google Chrome";v="113", "Chromium";v="113", "Not-A.Brand";v="24" Sec-Ch-Ua-Mobile: ?0 Sec-Ch-Ua-Platform: "Windows" Upgrade-Insecure-Requests: 1 Origin: https://pennydreadfulmagic.com User-Agent: Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/113.0.0.0 Safari/537.36 Accept: text/html,application/xhtml+xml,application/xml;q=0.9,image/avif,image/webp,image/apng,*/*;q=0.8,application/signed-exchange;v=b3;q=0.7 Sec-Fetch-Site: same-origin Sec-Fetch-Mode: navigate Sec-Fetch-User: ?1 Sec-Fetch-Dest: document Referer: https://pennydreadfulmagic.com/admin/archetypes/ Accept-Language: en-US,en;q=0.9 Cookie: hide_intro=True; session=.eJx1UV1rgzAU_S95dkVN60ff7MY2RmVdnTg6hgS9avyKJJFVS__7YqF2DPaWc-45OTcnJxR3wBvSQivRWvIeNETShrZXkEJDG5bOkIqE8TSmikGm4doYrxwb6zo2XBMv0U1Rs4TUoFTQ3oWBGvxvoW2c97SeQ35Z0YTyHNJpTcHaS7ChWxpqZM7iXgBvSTNpy7iBiig9I70szFhIIieebk0zzLvAeSh3UAlPT4vAdIPmecxew8NNL1ml4tYnRJIEhLhi9PKxt3xaezaMm_vhAEH1vfd53R2jvuyelB-OHeUgYqIKNFaOha2l7VoL3cW2YeLbfOrU0peOrmuIQ6aoYg5J0zDCgzf2UeR7Ufa43W2g9AsPj28We1chqtNOveZTtah-imaD4i6difmwKJmK-NLQ5dJYDpMBbYBw4Oisob8FSsJzUDu3fV2ffwDrUKg6.ZE8QBQ.L9SV7vchBRFRIRTB5P9SwRZfjYE; views=9; session=.eJx1UV1rgzAU_S95dkVN60ff7MY2RmVdnTg6hgS9avyKJJFVS__7YqF2DPaWc-45OTcnJxR3wBvSQivRWvIeNETShrZXkEJDG5bOkIqE8TSmikGm4doYrxwb6zo2XBMv0U1Rs4TUoFTQ3oWBGvxvoW2c97SeQ35Z0YTyHNJpTcHaS7ChWxpqZM7iXgBvSTNpy7iBiig9I70szFhIIieebk0zzLvAeSh3UAlPT4vAdIPmecxew8NNL1ml4tYnRJIEhLhi9PKxt3xaezaMm_vhAEH1vfd53R2jvuyelB-OHeUgYqIKNFaOha2l7VoL3cW2YeLbfOrU0peOrmuIQ6aoYg5J0zDCgzf2UeR7Ufa43W2g9AsPj28We1chqtNOveZTtah-imaD4i6difmwKJmK-NLQ5dJYDpMBbYBw4Oisob8FSsJzUDu3fV2ffwDrUKg6.ZG0T8Q.VhZQ-HyETLXkrWjDyjnzD9JJ0hU Priority: u=0, i Cf-Connecting-Ip: 97.103.253.204 Cf-Ipcountry: US Cdn-Loop: cloudflare ```

Labels: decksite