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

PennyDreadfulMTG / perf-reports

2 stars 2 forks source link

Exceeded slow_query limit (5.0 > 1.0) in mysql: ``` #60984

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 (215901, 215900, 215897, 215899, 215898, 215894, 215896, 215895, 215893, 215892, 215891, 215890, 215889, 215869, 215888, 215855, 215887, 215886, 215885, 215854, 215882, 215881, 215884, 215883, 215877, 215872, 215876, 215871, 215880, 215875, 215870, 215879, 215874, 215878, 215873, 215807, 215868, 215865, 215867, 215400, 215862, 215864, 215866, 215863, 215861, 215860, 215859, 215858, 215857, 215856, 215808, 215601, 215853, 215850, 215852, 215841, 215851, 215849, 215848, 215847, 215846, 215845, 215844, 215843, 215842, 215840, 215837, 215839, 215838, 215836, 215824, 215834, 215823, 215817, 215835, 215833, 215831, 215819, 215832, 215830, 215829, 215828, 215827, 215748, 215825, 215826, 215821, 215812, 215822, 215820, 215816, 215814, 215815, 215818, 215813, 215762, 215811, 215750, 215810, 215809, 215460, 215806, 215802, 215805, 215804, 215803, 215801, 215799, 215780, 215798, 215755, 215796, 215794, 215785, 215784, 215793, 215788, 215782, 215779, 215776, 215775, 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, 215643, 215638, 215637, 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, 215493, 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, 215373, 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, 215114, 215122, 215119, 215118, 215115, 215126, 215109, 215106, 215104, 215071, 215090, 215093, 215086, 215091, 215074, 215087, 215085, 215081, 215078, 215056, 215077, 215057, 215067, 215064, 215060, 215049, 214973, 215055, 215053, 215047, 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, 5.0, mysql)

Reported on decksite by mysql-perf

Location Hash: 49c22f249477248027da531d36290244ce352786

Request Data

``` Request Method: GET 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.ZF8u6g.ZZxvzXYfewvYmPiIOEdyzxopuVY'), ('views', '6')]) Endpoint: edit_archetypes View Args: {} Person: 219733587300319234 Referrer: https://pennydreadfulmagic.com/ Request Data: {} Host: pennydreadfulmagic.com Connection: Keep-Alive Accept-Encoding: gzip X-Forwarded-For: 97.103.253.204 Cf-Ray: 7c68dd08dcd10a1a-MIA X-Forwarded-Proto: https Cf-Visitor: {"scheme":"https"} 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 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/ 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.ZF8u6g.ZZxvzXYfewvYmPiIOEdyzxopuVY; views=6 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 (5.2 > 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 (215981, 215980, 215979, 215978, 215976, 215977, 215974, 215975, 215945, 215973, 215970, 215972, 215931, 215971, 215969, 215964, 215959, 215968, 215963, 215958, 215967, 215962, 215957, 215966, 215961, 215965, 215960, 215946, 215955, 215950, 215954, 215949, 215953, 215948, 215952, 215947, 215956, 215951, 215942, 215944, 215943, 215938, 215935, 215941, 215940, 215939, 215937, 215929, 215936, 215934, 215933, 215925, 215932, 215913, 215930, 215928, 215927, 215926, 215921, 215924, 215373, 215923, 215922, 215920, 215919, 215918, 215917, 215912, 215916, 215915, 215914, 215643, 215911, 215848, 215910, 215909, 215906, 215908, 215905, 215907, 215904, 215903, 215902, 215901, 215899, 215890, 215888, 215886, 215882, 215879, 215874, 215868, 215867, 215400, 215866, 215860, 215859, 215808, 215601, 215853, 215850, 215852, 215841, 215851, 215846, 215842, 215838, 215824, 215823, 215817, 215830, 215829, 215827, 215748, 215812, 215822, 215820, 215818, 215813, 215762, 215811, 215750, 215460, 215805, 215801, 215799, 215780, 215798, 215755, 215796, 215794, 215785, 215784, 215793, 215788, 215782, 215779, 215776, 215775, 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, 215637, 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, 215493, 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, 215047, 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, 5.2, mysql)

Reported on decksite by mysql-perf

Location Hash: 49c22f249477248027da531d36290244ce352786

Request Data

``` Request Method: GET 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.ZGAaww.7aWUDSohd87t8OJYYLqRT3j77A8'), ('page_size', '20'), ('views', '18')]) Endpoint: edit_archetypes View Args: {} Person: 219733587300319234 Referrer: https://pennydreadfulmagic.com/ Request Data: {} Host: pennydreadfulmagic.com Connection: Keep-Alive Accept-Encoding: gzip X-Forwarded-For: 2600:1006:b040:a27:75a1:d587:3041:c441 Cf-Ray: 7c6e9ef44b84b121-ATL X-Forwarded-Proto: https Cf-Visitor: {"scheme":"https"} 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 User-Agent: Mozilla/5.0 (Linux; Android 13; SM-F711U) 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/ 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=20; session=.eJx1UV1vgjAU_S99ZgYoIOxNhwkuLi7xI5vLQipcaxFa1pYNZvzvKy5qsmRvveeec8-9p0eU1iArwoFrdK9lAxYiecX4pcihYpXIryVTmZB5ygyCXCcaYuyHQ2zb2Ilc7KEboxQZKcGwgN-tFqbxv4TxlDasvJqUglLI-8WU4Gcrxw4sVGkq0kaB5KTqBxdpBQdi9II0eu-mShPd456IJZ3DSBW02STRy3b23SXx03oZxq-dvPG1OIC584hIloFSlxq56xZYHCYrv31eLhaPU1yLOOHbYOZ_jvpLoK2ZBJUSE5kT-N4wcqIoHPhRcOv1CQa2F9q2hSTsDLS_GkymwhXaK9pJ8LXRH3iDg28ynq7pIZw3D8bAJFibS97QORZlkN_HoBBmbh-l-S6269C7hc5DU931AjQGIkGik4X-hqeJpGD25U1Znn4A3taihw.ZGAaww.7aWUDSohd87t8OJYYLqRT3j77A8; views=18 Priority: u=0, i Cf-Connecting-Ip: 2600:1006:b040:a27:75a1:d587:3041:c441 Cf-Ipcountry: US Cdn-Loop: cloudflare ```

Labels: decksite

vorpal-buildbot commented 1 year ago

Exceeded slow_query limit (4.5 > 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 (215982, 215979, 215974, 215945, 215931, 215969, 215958, 215957, 215968, 215963, 215959, 215952, 215956, 215955, 215953, 215944, 215938, 215941, 215937, 215929, 215925, 215930, 215928, 215921, 215373, 215923, 215920, 215919, 215918, 215916, 215915, 215914, 215643, 215911, 215848, 215908, 215905, 215907, 215902, 215901, 215899, 215890, 215888, 215886, 215882, 215879, 215874, 215868, 215867, 215400, 215866, 215860, 215859, 215808, 215601, 215853, 215850, 215852, 215841, 215851, 215846, 215842, 215838, 215824, 215823, 215817, 215830, 215829, 215827, 215748, 215812, 215822, 215820, 215818, 215813, 215762, 215811, 215750, 215460, 215805, 215801, 215799, 215780, 215798, 215755, 215796, 215794, 215793, 215788, 215785, 215784, 215782, 215779, 215776, 215775, 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, 215637, 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, 215493, 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, 215217, 215223, 215216, 215222, 215215, 215220, 215214, 215219, 215208, 215207, 215198, 215169, 215195, 215188, 215187, 215180, 215149, 215170, 215175, 215159, 215154, 215084, 215161, 215158, 215156, 215153, 215145, 215138, 215136, 215135, 215128, 215129, 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, 215047, 215046, 215042, 215040, 215038, 214968, 215036, 215010, 215034, 215008, 215030, 215026, 215017, 215023, 215018, 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.5, mysql)

Reported on decksite by mysql-perf

Location Hash: 49c22f249477248027da531d36290244ce352786

Request Data

``` Request Method: GET Path: /admin/archetypes/? Cookies: ImmutableMultiDict([('hide_intro', 'True'), ('page_size', '20'), ('session', '.eJx1UVFvgjAY_C99ZoYiVPHNmeh0RjMdzmxZSIUPqEBL2uLmjP99hUVdluyt991d7-v1hMIKZEk5cI0GWtZgIRqXjF9ADCUrRXyFTEVCxiEzE4R9j7iuj3t2H7uu7RAP3RSFiGgBRgX8Llgb4n8L42Fas-Ia8suKGpSmEDdrKsHbYOx2LVTqVIS1Aslp2Wh3NAepfd84BK115oRKU90wy6dsOx0eJrN5vl8Eo9Uwg49sVKvdJmV6ftNrkZvAwQnRKAKlLhhNvxI7yqTzGsjAe3E263Fve8gevAInc_ls_PBZMQkqpKZCTHo-8buEOB1sTg6-0U2pxHb7tm0hCYkZZdcMm1SRG894PJG5GO9klWESL_ePKztYrI4mw5Ramce8obYp1fZp_owlDfkz6-yFiXi3UHtpqI-NAd0DlSDR2UJ_G9RUpmBW5nVRnL8Bj0-oYA.ZF2VkQ.PVNyIpxDj5xaHb0c2oZ_Xh2EmGA'), ('session', '.eJx1UVFvgjAY_C99ZoYiVPHNmeh0RjMdzmxZSIUPqEBL2uLmjP99hUVdluyt991d7-v1hMIKZEk5cI0GWtZgIRqXjF9ADCUrRXyFTEVCxiEzE4R9j7iuj3t2H7uu7RAP3RSFiGgBRgX8Llgb4n8L42Fas-Ia8suKGpSmEDdrKsHbYOx2LVTqVIS1Aslp2Wh3NAepfd84BK115oRKU90wy6dsOx0eJrN5vl8Eo9Uwg49sVKvdJmV6ftNrkZvAwQnRKAKlLhhNvxI7yqTzGsjAe3E263Fve8gevAInc_ls_PBZMQkqpKZCTHo-8buEOB1sTg6-0U2pxHb7tm0hCYkZZdcMm1SRG894PJG5GO9klWESL_ePKztYrI4mw5Ramce8obYp1fZp_owlDfkz6-yFiXi3UHtpqI-NAd0DlSDR2UJ_G9RUpmBW5nVRnL8Bj0-oYA.ZGAgdA.Tw5eskguReHK7a4djJtKqRnitn8'), ('deck_id', '215803'), ('views', '10058')]) Endpoint: edit_archetypes View Args: {} Person: 195644917081440265 Referrer: https://pennydreadfulmagic.com/ Request Data: {} Host: pennydreadfulmagic.com Connection: Keep-Alive Accept-Encoding: gzip X-Forwarded-For: 136.25.106.188 Cf-Ray: 7c6ec27b0cb4964b-SJC X-Forwarded-Proto: https Cf-Visitor: {"scheme":"https"} Sec-Ch-Ua: "Google Chrome";v="113", "Chromium";v="113", "Not-A.Brand";v="24" Sec-Ch-Ua-Mobile: ?0 Sec-Ch-Ua-Platform: "macOS" Upgrade-Insecure-Requests: 1 User-Agent: Mozilla/5.0 (Macintosh; Intel Mac OS X 10_15_7) 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/ Accept-Language: en-US,en;q=0.9,es;q=0.8 Cookie: hide_intro=True; page_size=20; session=.eJx1UVFvgjAY_C99ZoYiVPHNmeh0RjMdzmxZSIUPqEBL2uLmjP99hUVdluyt991d7-v1hMIKZEk5cI0GWtZgIRqXjF9ADCUrRXyFTEVCxiEzE4R9j7iuj3t2H7uu7RAP3RSFiGgBRgX8Llgb4n8L42Fas-Ia8suKGpSmEDdrKsHbYOx2LVTqVIS1Aslp2Wh3NAepfd84BK115oRKU90wy6dsOx0eJrN5vl8Eo9Uwg49sVKvdJmV6ftNrkZvAwQnRKAKlLhhNvxI7yqTzGsjAe3E263Fve8gevAInc_ls_PBZMQkqpKZCTHo-8buEOB1sTg6-0U2pxHb7tm0hCYkZZdcMm1SRG894PJG5GO9klWESL_ePKztYrI4mw5Ramce8obYp1fZp_owlDfkz6-yFiXi3UHtpqI-NAd0DlSDR2UJ_G9RUpmBW5nVRnL8Bj0-oYA.ZF2VkQ.PVNyIpxDj5xaHb0c2oZ_Xh2EmGA; deck_id=215803; views=10058; session=.eJx1UVFvgjAY_C99ZoYiVPHNmeh0RjMdzmxZSIUPqEBL2uLmjP99hUVdluyt991d7-v1hMIKZEk5cI0GWtZgIRqXjF9ADCUrRXyFTEVCxiEzE4R9j7iuj3t2H7uu7RAP3RSFiGgBRgX8Llgb4n8L42Fas-Ia8suKGpSmEDdrKsHbYOx2LVTqVIS1Aslp2Wh3NAepfd84BK115oRKU90wy6dsOx0eJrN5vl8Eo9Uwg49sVKvdJmV6ftNrkZvAwQnRKAKlLhhNvxI7yqTzGsjAe3E263Fve8gevAInc_ls_PBZMQkqpKZCTHo-8buEOB1sTg6-0U2pxHb7tm0hCYkZZdcMm1SRG894PJG5GO9klWESL_ePKztYrI4mw5Ramce8obYp1fZp_owlDfkz6-yFiXi3UHtpqI-NAd0DlSDR2UJ_G9RUpmBW5nVRnL8Bj0-oYA.ZGAgdA.Tw5eskguReHK7a4djJtKqRnitn8 Priority: u=0, i Cf-Connecting-Ip: 136.25.106.188 Cf-Ipcountry: US Cdn-Loop: cloudflare ```

Labels: decksite

vorpal-buildbot commented 1 year ago

Exceeded slow_query limit (5.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 (216017, 216015, 216016, 216010, 215902, 216014, 216013, 216012, 215637, 216009, 216005, 216008, 216007, 216006, 216004, 216003, 216002, 216001, 216000, 215937, 215999, 215998, 215493, 215997, 215996, 215995, 215989, 215994, 215993, 215992, 215886, 215991, 215990, 215988, 215987, 215986, 215985, 215984, 215983, 215982, 215979, 215974, 215945, 215931, 215969, 215968, 215963, 215959, 215958, 215957, 215956, 215955, 215953, 215952, 215944, 215938, 215941, 215929, 215925, 215930, 215928, 215921, 215373, 215923, 215920, 215919, 215918, 215916, 215915, 215914, 215643, 215911, 215848, 215908, 215905, 215907, 215901, 215899, 215890, 215888, 215882, 215879, 215874, 215868, 215867, 215400, 215866, 215860, 215601, 215853, 215850, 215852, 215841, 215851, 215846, 215842, 215838, 215824, 215823, 215817, 215830, 215829, 215827, 215748, 215812, 215822, 215820, 215818, 215813, 215762, 215750, 215460, 215805, 215801, 215799, 215780, 215798, 215755, 215794, 215796, 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, 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, 215047, 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, 5.0, mysql)

Reported on decksite by mysql-perf

Location Hash: 49c22f249477248027da531d36290244ce352786

Request Data

``` Request Method: GET 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.ZGD9Rg.I60uY2_u75HVAb2pi-hsKZQjh6Y'), ('views', '4')]) Endpoint: edit_archetypes View Args: {} Person: 219733587300319234 Referrer: https://pennydreadfulmagic.com/ Request Data: {} Host: pennydreadfulmagic.com Connection: Keep-Alive Accept-Encoding: gzip X-Forwarded-For: 97.103.253.204 Cf-Ray: 7c7426dabf8bd9f9-MIA X-Forwarded-Proto: https Cf-Visitor: {"scheme":"https"} 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 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/ Accept-Language: en-US,en;q=0.9 Cookie: hide_intro=True; session=.eJx1UV1rgzAU_S95dkVN60ff7MY2RmVdnTg6hgS9avyKJJFVS__7YqF2DPaWc-45OTcnJxR3wBvSQivRWvIeNETShrZXkEJDG5bOkIqE8TSmikGm4doYrxwb6zo2XBMv0U1Rs4TUoFTQ3oWBGvxvoW2c97SeQ35Z0YTyHNJpTcHaS7ChWxpqZM7iXgBvSTNpy7iBiig9I70szFhIIieebk0zzLvAeSh3UAlPT4vAdIPmecxew8NNL1ml4tYnRJIEhLhi9PKxt3xaezaMm_vhAEH1vfd53R2jvuyelB-OHeUgYqIKNFaOha2l7VoL3cW2YeLbfOrU0peOrmuIQ6aoYg5J0zDCgzf2UeR7Ufa43W2g9AsPj28We1chqtNOveZTtah-imaD4i6difmwKJmK-NLQ5dJYDpMBbYBw4Oisob8FSsJzUDu3fV2ffwDrUKg6.ZE8QBQ.L9SV7vchBRFRIRTB5P9SwRZfjYE; views=4; session=.eJx1UV1rgzAU_S95dkVN60ff7MY2RmVdnTg6hgS9avyKJJFVS__7YqF2DPaWc-45OTcnJxR3wBvSQivRWvIeNETShrZXkEJDG5bOkIqE8TSmikGm4doYrxwb6zo2XBMv0U1Rs4TUoFTQ3oWBGvxvoW2c97SeQ35Z0YTyHNJpTcHaS7ChWxpqZM7iXgBvSTNpy7iBiig9I70szFhIIieebk0zzLvAeSh3UAlPT4vAdIPmecxew8NNL1ml4tYnRJIEhLhi9PKxt3xaezaMm_vhAEH1vfd53R2jvuyelB-OHeUgYqIKNFaOha2l7VoL3cW2YeLbfOrU0peOrmuIQ6aoYg5J0zDCgzf2UeR7Ufa43W2g9AsPj28We1chqtNOveZTtah-imaD4i6difmwKJmK-NLQ5dJYDpMBbYBw4Oisob8FSsJzUDu3fV2ffwDrUKg6.ZGD9Rg.I60uY2_u75HVAb2pi-hsKZQjh6Y 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 (5.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 (216071, 216070, 216069, 216068, 216064, 216067, 216066, 216063, 216065, 216035, 216037, 216058, 216062, 216057, 216061, 216060, 216059, 216045, 216054, 216049, 216044, 216053, 216048, 216043, 216052, 216047, 216056, 216042, 216051, 216046, 216055, 216050, 216040, 216041, 216039, 216038, 216036, 216028, 216034, 216033, 216032, 216031, 216030, 216029, 216021, 216000, 216027, 216026, 216025, 216024, 216014, 216023, 216022, 216017, 216012, 216020, 216019, 216016, 216018, 215827, 216010, 215902, 216013, 215637, 216009, 216002, 216004, 216003, 215937, 215999, 215998, 215493, 215997, 215996, 215989, 215994, 215993, 215992, 215886, 215991, 215984, 215979, 215974, 215945, 215931, 215969, 215958, 215957, 215968, 215963, 215959, 215952, 215956, 215955, 215953, 215938, 215941, 215929, 215925, 215930, 215928, 215921, 215373, 215923, 215918, 215916, 215915, 215914, 215643, 215911, 215848, 215908, 215905, 215907, 215901, 215899, 215890, 215888, 215882, 215879, 215874, 215868, 215867, 215400, 215866, 215860, 215601, 215853, 215850, 215852, 215841, 215851, 215846, 215842, 215838, 215824, 215823, 215817, 215830, 215829, 215748, 215812, 215822, 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, 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, 215047, 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, 5.7, mysql)

Reported on decksite by mysql-perf

Location Hash: 49c22f249477248027da531d36290244ce352786

Request Data

``` Request Method: GET Path: /admin/archetypes/? Cookies: ImmutableMultiDict([('session', '.eJx1UctuwjAQ_BefU5SEvMyRioIQpSCKVFFVkRWvE5fEDrbTEhD_XicqcKh63N3ZmZ3ZM0prUBURIAwaGdWAgwituEAjRkptKwoVryS9DinXmVQ05baDsI_9cBi7iR-6SRJ5OER3RCkzUoJFgXjYbuzg_xUu0rzh5U2klHkOtLtMS9FL2TXPQZXJZdpoUIJUHXMrG0W-QJEcOOXSWCpJGlP4qTbEdIjJ-sACd7mUoeBveDjZsS0uZmujN9H0ML3jjdyD9XxGJMtA62uN6pkiu-D1xMa4Ok3jdiLm-6Mmh4zx03dnCo41V6BTYuPzosSPgjjy4gGOk6Hn38ddoJEbJK7rIAXMtoqbRrlePR32efgcrXTBHrFaxHPmLRYvIWQxtRo2z9qaeUd9SHrwKS2d81v1wdrvcdaiDwf1pKlpuwU0BqJAoYuD_kRpiMrB3iyasrz8AI4uqL4.ZGGIZA.vXvQmskOWhsFUcmC7dJJdqFLR64'), ('session', '.eJx1UctuwjAQ_BefU5SEvMyRioIQpSCKVFFVkRWvE5fEDrbTEhD_XicqcKh63N3ZmZ3ZM0prUBURIAwaGdWAgwituEAjRkptKwoVryS9DinXmVQ05baDsI_9cBi7iR-6SRJ5OER3RCkzUoJFgXjYbuzg_xUu0rzh5U2klHkOtLtMS9FL2TXPQZXJZdpoUIJUHXMrG0W-QJEcOOXSWCpJGlP4qTbEdIjJ-sACd7mUoeBveDjZsS0uZmujN9H0ML3jjdyD9XxGJMtA62uN6pkiu-D1xMa4Ok3jdiLm-6Mmh4zx03dnCo41V6BTYuPzosSPgjjy4gGOk6Hn38ddoJEbJK7rIAXMtoqbRrlePR32efgcrXTBHrFaxHPmLRYvIWQxtRo2z9qaeUd9SHrwKS2d81v1wdrvcdaiDwf1pKlpuwU0BqJAoYuD_kRpiMrB3iyasrz8AI4uqL4.ZEpiag.F3COp8V-ROvx7SO-Nkk9aTlB9UY'), ('hide_intro', 'True'), ('views', '15'), ('page_size', '20')]) Endpoint: edit_archetypes View Args: {} Person: 929253708250886195 Referrer: https://pennydreadfulmagic.com/ Request Data: {} Host: pennydreadfulmagic.com Connection: Keep-Alive Accept-Encoding: gzip X-Forwarded-For: 2607:fea8:2dde:5d00:29ad:52c5:c5ae:5ab6 Cf-Ray: 7c778c16cee35b3a-IAD 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-CA,en-US;q=0.7,en;q=0.3 Dnt: 1 Referer: https://pennydreadfulmagic.com/ Upgrade-Insecure-Requests: 1 Sec-Fetch-Dest: document Sec-Fetch-Mode: navigate Sec-Fetch-Site: same-origin Sec-Fetch-User: ?1 Cookie: session=.eJx1UctuwjAQ_BefU5SEvMyRioIQpSCKVFFVkRWvE5fEDrbTEhD_XicqcKh63N3ZmZ3ZM0prUBURIAwaGdWAgwituEAjRkptKwoVryS9DinXmVQ05baDsI_9cBi7iR-6SRJ5OER3RCkzUoJFgXjYbuzg_xUu0rzh5U2klHkOtLtMS9FL2TXPQZXJZdpoUIJUHXMrG0W-QJEcOOXSWCpJGlP4qTbEdIjJ-sACd7mUoeBveDjZsS0uZmujN9H0ML3jjdyD9XxGJMtA62uN6pkiu-D1xMa4Ok3jdiLm-6Mmh4zx03dnCo41V6BTYuPzosSPgjjy4gGOk6Hn38ddoJEbJK7rIAXMtoqbRrlePR32efgcrXTBHrFaxHPmLRYvIWQxtRo2z9qaeUd9SHrwKS2d81v1wdrvcdaiDwf1pKlpuwU0BqJAoYuD_kRpiMrB3iyasrz8AI4uqL4.ZGGIZA.vXvQmskOWhsFUcmC7dJJdqFLR64; hide_intro=True; session=.eJx1UctuwjAQ_BefU5SEvMyRioIQpSCKVFFVkRWvE5fEDrbTEhD_XicqcKh63N3ZmZ3ZM0prUBURIAwaGdWAgwituEAjRkptKwoVryS9DinXmVQ05baDsI_9cBi7iR-6SRJ5OER3RCkzUoJFgXjYbuzg_xUu0rzh5U2klHkOtLtMS9FL2TXPQZXJZdpoUIJUHXMrG0W-QJEcOOXSWCpJGlP4qTbEdIjJ-sACd7mUoeBveDjZsS0uZmujN9H0ML3jjdyD9XxGJMtA62uN6pkiu-D1xMa4Ok3jdiLm-6Mmh4zx03dnCo41V6BTYuPzosSPgjjy4gGOk6Hn38ddoJEbJK7rIAXMtoqbRrlePR32efgcrXTBHrFaxHPmLRYvIWQxtRo2z9qaeUd9SHrwKS2d81v1wdrvcdaiDwf1pKlpuwU0BqJAoYuD_kRpiMrB3iyasrz8AI4uqL4.ZEpiag.F3COp8V-ROvx7SO-Nkk9aTlB9UY; views=15; page_size=20 Cf-Connecting-Ip: 2607:fea8:2dde:5d00:29ad:52c5:c5ae:5ab6 Cf-Ipcountry: CA Cdn-Loop: cloudflare ```

Labels: decksite

vorpal-buildbot commented 1 year ago

Exceeded slow_query limit (5.5 > 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 (216104, 216103, 216094, 216102, 216101, 216098, 216100, 216082, 216099, 215047, 216096, 216097, 216091, 216095, 216087, 216093, 216092, 216022, 216090, 216089, 215888, 216088, 215908, 216086, 215822, 216085, 216084, 216083, 216081, 216080, 216075, 216079, 216078, 216077, 216076, 215996, 216074, 216073, 216072, 216013, 216070, 216064, 216067, 216066, 216063, 216037, 216052, 216042, 216051, 216050, 216049, 216053, 216046, 216041, 216039, 216028, 216000, 216027, 216026, 216014, 216023, 216017, 216012, 216016, 215827, 216010, 215902, 215637, 216009, 216002, 216004, 216003, 215937, 215999, 215998, 215493, 215997, 215989, 215994, 215993, 215992, 215886, 215991, 215984, 215979, 215974, 215945, 215931, 215969, 215958, 215957, 215968, 215963, 215959, 215952, 215956, 215955, 215953, 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, 215852, 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, 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, 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, 215114, 215122, 215119, 215118, 215115, 215126, 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, 5.5, mysql)

Reported on decksite by mysql-perf

Location Hash: 49c22f249477248027da531d36290244ce352786

Request Data

``` Request Method: GET 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.ZGJk8g.pTJJqZeIkGBd82PMtB0dfGTo-lQ'), ('page_size', '20'), ('views', '14')]) Endpoint: edit_archetypes View Args: {} Person: 219733587300319234 Referrer: https://pennydreadfulmagic.com/ Request Data: {} Host: pennydreadfulmagic.com Connection: Keep-Alive Accept-Encoding: gzip X-Forwarded-For: 97.103.253.204 Cf-Ray: 7c7ceed8ce25db2d-MIA X-Forwarded-Proto: https Cf-Visitor: {"scheme":"https"} 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 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/ 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=14; session=.eJx1UV1rgzAU_S95dkVN60ff7MY2RmVdnTg6hgS9avyKJJFVS__7YqF2DPaWc-45OTcnJxR3wBvSQivRWvIeNETShrZXkEJDG5bOkIqE8TSmikGm4doYrxwb6zo2XBMv0U1Rs4TUoFTQ3oWBGvxvoW2c97SeQ35Z0YTyHNJpTcHaS7ChWxpqZM7iXgBvSTNpy7iBiig9I70szFhIIieebk0zzLvAeSh3UAlPT4vAdIPmecxew8NNL1ml4tYnRJIEhLhi9PKxt3xaezaMm_vhAEH1vfd53R2jvuyelB-OHeUgYqIKNFaOha2l7VoL3cW2YeLbfOrU0peOrmuIQ6aoYg5J0zDCgzf2UeR7Ufa43W2g9AsPj28We1chqtNOveZTtah-imaD4i6difmwKJmK-NLQ5dJYDpMBbYBw4Oisob8FSsJzUDu3fV2ffwDrUKg6.ZGJk8g.pTJJqZeIkGBd82PMtB0dfGTo-lQ 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 (5.5 > 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 (216104, 216103, 216094, 216102, 216101, 216098, 216100, 216082, 216099, 215047, 216096, 216097, 216091, 216095, 216087, 216093, 216092, 216022, 216090, 216089, 215888, 216088, 215908, 216086, 215822, 216085, 216084, 216083, 216081, 216080, 216075, 216079, 216078, 216077, 216076, 215996, 216074, 216073, 216072, 216013, 216070, 216064, 216067, 216066, 216063, 216037, 216052, 216042, 216051, 216050, 216049, 216053, 216046, 216041, 216039, 216028, 216000, 216027, 216026, 216014, 216023, 216017, 216012, 216016, 215827, 216010, 215902, 215637, 216009, 216002, 216004, 216003, 215937, 215999, 215998, 215493, 215997, 215989, 215994, 215993, 215992, 215886, 215991, 215984, 215979, 215974, 215945, 215931, 215969, 215958, 215957, 215968, 215963, 215959, 215952, 215956, 215955, 215953, 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, 215852, 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, 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, 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, 215114, 215122, 215119, 215118, 215115, 215126, 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, 5.5, mysql)

Reported on decksite by mysql-perf

Location Hash: 49c22f249477248027da531d36290244ce352786

Request Data

``` Request Method: GET 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.ZGJpWw.U-VQ3_viZSh7mGn_ESsiuQ4OhwA'), ('page_size', '20'), ('views', '27')]) Endpoint: edit_archetypes View Args: {} Person: 219733587300319234 Referrer: https://pennydreadfulmagic.com/decks/215700/ Request Data: {} Host: pennydreadfulmagic.com Connection: Keep-Alive Accept-Encoding: gzip X-Forwarded-For: 97.103.253.204 Cf-Ray: 7c7d0a6e0f200351-MIA X-Forwarded-Proto: https Cf-Visitor: {"scheme":"https"} 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 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/decks/215700/ 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=27; session=.eJx1UV1rgzAU_S95dkVN60ff7MY2RmVdnTg6hgS9avyKJJFVS__7YqF2DPaWc-45OTcnJxR3wBvSQivRWvIeNETShrZXkEJDG5bOkIqE8TSmikGm4doYrxwb6zo2XBMv0U1Rs4TUoFTQ3oWBGvxvoW2c97SeQ35Z0YTyHNJpTcHaS7ChWxpqZM7iXgBvSTNpy7iBiig9I70szFhIIieebk0zzLvAeSh3UAlPT4vAdIPmecxew8NNL1ml4tYnRJIEhLhi9PKxt3xaezaMm_vhAEH1vfd53R2jvuyelB-OHeUgYqIKNFaOha2l7VoL3cW2YeLbfOrU0peOrmuIQ6aoYg5J0zDCgzf2UeR7Ufa43W2g9AsPj28We1chqtNOveZTtah-imaD4i6difmwKJmK-NLQ5dJYDpMBbYBw4Oisob8FSsJzUDu3fV2ffwDrUKg6.ZGJpWw.U-VQ3_viZSh7mGn_ESsiuQ4OhwA Priority: u=0, i Cf-Connecting-Ip: 97.103.253.204 Cf-Ipcountry: US Cdn-Loop: cloudflare ```

Labels: decksite