search

PennyDreadfulMTG / perf-reports

2 stars 2 forks source link

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

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 (216906, 216905, 216904, 216903, 216902, 216901, 216900, 216899, 216898, 216895, 216897, 216893, 216896, 216887, 216894, 216889, 216892, 216891, 216890, 216888, 216881, 216884, 216886, 216885, 216862, 216883, 216864, 216882, 216880, 216879, 216878, 216877, 216876, 216875, 216874, 216870, 216871, 216873, 216872, 216869, 216868, 216867, 216866, 216865, 216860, 216863, 216861, 216858, 216859, 216857, 216856, 216855) 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: e6a2dcf07412409bd46dd1beec603d168789cc32

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.ZHGD7Q.cfIIbv-X991M45248aMr73KkVxc'), ('views', '3')]) 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: 7cdb70851c318e00-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.ZHGD7Q.cfIIbv-X991M45248aMr73KkVxc; views=3 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 (1.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 (216991, 216990, 216989, 216985, 216984, 216988, 216987, 216986, 216982, 216983, 216981, 216980, 216901, 216979, 216978, 216977, 216953, 216976, 216882, 216975, 216974, 216973, 216972, 216971, 216970, 216969, 216956, 216965, 216960, 216964, 216959, 216968, 216963, 216958, 216967, 216962, 216957, 216966, 216961, 216955, 216954, 216892, 216897, 216952, 216951, 216950, 216863, 216865, 216949, 216947, 216948, 216943, 216946, 216945, 216944, 216939, 216942, 216941, 216940, 216938, 216937, 216932, 216936, 216935, 216934, 216933, 216931, 216930, 216929, 216928, 216924, 216919, 216923, 216918, 216927, 216922, 216926, 216921, 216925, 216920, 216915, 216910, 216914, 216909, 216913, 216908, 216917, 216912, 216907, 216916, 216911, 216904, 216903, 216893, 216896, 216887, 216889, 216891, 216890, 216884, 216864, 216879, 216878, 216877, 216876, 216875, 216874, 216870, 216871, 216873, 216867, 216866, 216860, 216858, 216857, 216856, 216855) 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, 1.7, mysql)

Reported on decksite by mysql-perf

Location Hash: e6a2dcf07412409bd46dd1beec603d168789cc32

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.ZHLq9Q.3cnGan6qci5fiCmSNvB7Ae9yZzg'), ('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: 7ce434e39bd43358-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=6; session=.eJx1UV1rgzAU_S95dkVN60ff7MY2RmVdnTg6hgS9avyKJJFVS__7YqF2DPaWc-45OTcnJxR3wBvSQivRWvIeNETShrZXkEJDG5bOkIqE8TSmikGm4doYrxwb6zo2XBMv0U1Rs4TUoFTQ3oWBGvxvoW2c97SeQ35Z0YTyHNJpTcHaS7ChWxpqZM7iXgBvSTNpy7iBiig9I70szFhIIieebk0zzLvAeSh3UAlPT4vAdIPmecxew8NNL1ml4tYnRJIEhLhi9PKxt3xaezaMm_vhAEH1vfd53R2jvuyelB-OHeUgYqIKNFaOha2l7VoL3cW2YeLbfOrU0peOrmuIQ6aoYg5J0zDCgzf2UeR7Ufa43W2g9AsPj28We1chqtNOveZTtah-imaD4i6difmwKJmK-NLQ5dJYDpMBbYBw4Oisob8FSsJzUDu3fV2ffwDrUKg6.ZHLq9Q.3cnGan6qci5fiCmSNvB7Ae9yZzg 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.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 (217065, 217064, 217063, 217059, 217062, 217060, 217061, 217056, 217058, 217057, 217055, 217054, 217051, 217053, 217052, 217050, 217030, 217039, 217048, 217034, 217043, 217038, 217047, 217042, 217037, 217046, 217041, 217036, 217045, 217040, 217049, 217035, 217044, 217033, 217032, 217031, 217029, 217028, 217027, 217026, 217025, 217024, 217023, 217022, 217021, 217020, 217019, 217018, 217017, 217016, 217015, 217014, 217013, 217012, 217009, 217010, 217011, 217008, 216874, 217007, 216944, 217006, 216998, 217002, 217001, 217005, 217000, 217004, 216999, 217003, 216991, 216992, 216997, 216996, 216989, 216995, 216994, 216993, 216984, 216980, 216901, 216953, 216976, 216882, 216973, 216971, 216967, 216959, 216964, 216962, 216961, 216960, 216892, 216897, 216952, 216951, 216950, 216863, 216865, 216943, 216940, 216937, 216932, 216935, 216934, 216930, 216927, 216926, 216922, 216920, 216918, 216912, 216911, 216908, 216907, 216904, 216893, 216896, 216887, 216889, 216891, 216890, 216884, 216864, 216879, 216878, 216877, 216876, 216875, 216870, 216871, 216873, 216867, 216866, 216860, 216858, 216857, 216856, 216855) 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.0, mysql)

Reported on decksite by mysql-perf

Location Hash: e6a2dcf07412409bd46dd1beec603d168789cc32

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.ZHQkLw.ofi-uefsDn5qQJrF744bP1T2F3k'), ('views', '3')]) 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: 7cebd9d44dab9ac0-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=3; session=.eJx1UV1rgzAU_S95dkVN60ff7MY2RmVdnTg6hgS9avyKJJFVS__7YqF2DPaWc-45OTcnJxR3wBvSQivRWvIeNETShrZXkEJDG5bOkIqE8TSmikGm4doYrxwb6zo2XBMv0U1Rs4TUoFTQ3oWBGvxvoW2c97SeQ35Z0YTyHNJpTcHaS7ChWxpqZM7iXgBvSTNpy7iBiig9I70szFhIIieebk0zzLvAeSh3UAlPT4vAdIPmecxew8NNL1ml4tYnRJIEhLhi9PKxt3xaezaMm_vhAEH1vfd53R2jvuyelB-OHeUgYqIKNFaOha2l7VoL3cW2YeLbfOrU0peOrmuIQ6aoYg5J0zDCgzf2UeR7Ufa43W2g9AsPj28We1chqtNOveZTtah-imaD4i6difmwKJmK-NLQ5dJYDpMBbYBw4Oisob8FSsJzUDu3fV2ffwDrUKg6.ZHQkLw.ofi-uefsDn5qQJrF744bP1T2F3k 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 (1.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 (217068, 217067, 217066, 217063, 217059, 217056, 217058, 217057, 217055, 217043, 217042, 217049, 217036, 217048, 217045, 217032, 217029, 217027, 217026, 217025, 217021, 217019, 217017, 217015, 217014, 217010, 216874, 217007, 216944, 217006, 217002, 217000, 216999, 217004, 217003, 216991, 216996, 216989, 216995, 216984, 216980, 216901, 216953, 216976, 216882, 216973, 216971, 216962, 216961, 216960, 216967, 216959, 216964, 216892, 216897, 216952, 216951, 216950, 216863, 216865, 216943, 216940, 216937, 216932, 216935, 216934, 216930, 216922, 216920, 216918, 216927, 216926, 216908, 216907, 216912, 216911, 216904, 216893, 216896, 216887, 216889, 216891, 216890, 216884, 216864, 216879, 216878, 216877, 216876, 216875, 216870, 216871, 216873, 216867, 216866, 216860, 216858, 216857, 216856, 216855) 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, 1.5, mysql)

Reported on decksite by mysql-perf

Location Hash: e6a2dcf07412409bd46dd1beec603d168789cc32

Request Data ``` Request Method: GET Path: /admin/archetypes/? Cookies: ImmutableMultiDict([('hide_intro', 'True'), ('hide_intro', 'True'), ('views', '792'), ('views', '812'), ('session', '.eJx1UV1rwkAQ_C_3nMolXuKlbw0VbaFQqlhrKeHIXeLV-wj3YRPF_96LoEKhj7M7O7M7ewRly4wkiikH7p3xLAKESq4ugDLJpaZXyG2lDS15qIA4ReNsjFGC0ngyRmmCwY0hdEUECyym7mZFaPw_wlXZeC6uJkI3DaPDYlars1WcZxGQrtGlt8woIgdhywWxxPRBQRPvtklpHXFDZ1Y5ipbZ7oC7QrTWr3Kxn__kHX_N91je-E7vWLj0CEhVMWsvGKyk7d-eHusXOu3qqc8L0W2p2dDFJMUfJsyzruWG2ZKE0OIsQynKMwxHEKUYw_GtP-SYQYQhjIBhdShtrybL5838sLG6bfsU0hhqKx8k8otkt27f18Ek5NiGaz5DcuE7vB4OPedkR986KF8Q-IrAWbR0_TAACkYMM-AUgb8ROmIaFnZWXojTL_36pfA.ZHL5Xg.Ml-LBplpan429xEhKWggT06PfKY'), ('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: edit_archetypes View Args: {} Person: 154363842451734528 Referrer: https://pennydreadfulmagic.com/ Request Data: {} Host: pennydreadfulmagic.com Connection: Keep-Alive Accept-Encoding: gzip X-Forwarded-For: 119.17.133.163 Cf-Ray: 7ced395e5fba17c8-MEL X-Forwarded-Proto: https Cf-Visitor: {"scheme":"https"} 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 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: 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.ZHL5Xg.Ml-LBplpan429xEhKWggT06PfKY; 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=812 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 (1.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 (217082, 217081, 217080, 216873, 217079, 217078, 217015, 217077, 217076, 217075, 217074, 217073, 217072, 217071, 217070, 217069, 217067, 217066, 217063, 217059, 217056, 217058, 217057, 217055, 217049, 217036, 217048, 217045, 217043, 217042, 217032, 217029, 217027, 217026, 217025, 217021, 217019, 217017, 217014, 217010, 216874, 217007, 216944, 217006, 216999, 217004, 217003, 217002, 217000, 216991, 216996, 216989, 216995, 216984, 216980, 216901, 216953, 216976, 216882, 216973, 216971, 216960, 216967, 216959, 216964, 216962, 216961, 216892, 216897, 216952, 216951, 216950, 216863, 216865, 216943, 216940, 216937, 216932, 216935, 216934, 216930, 216918, 216927, 216926, 216922, 216920, 216907, 216912, 216911, 216908, 216904, 216893, 216896, 216887, 216889, 216891, 216890, 216884, 216864, 216879, 216878, 216877, 216876, 216875, 216870, 216871, 216867, 216866, 216860, 216858, 216857, 216856, 216855) 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, 1.6, mysql)

Reported on decksite by mysql-perf

Location Hash: e6a2dcf07412409bd46dd1beec603d168789cc32

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.ZHTZ-g.uIdnd7ajZWr3u9pk4fqop6LNsuo'), ('views', '3')]) 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: 7cf04ada3a6f036a-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=3; session=.eJx1UV1rgzAU_S95dkVN60ff7MY2RmVdnTg6hgS9avyKJJFVS__7YqF2DPaWc-45OTcnJxR3wBvSQivRWvIeNETShrZXkEJDG5bOkIqE8TSmikGm4doYrxwb6zo2XBMv0U1Rs4TUoFTQ3oWBGvxvoW2c97SeQ35Z0YTyHNJpTcHaS7ChWxpqZM7iXgBvSTNpy7iBiig9I70szFhIIieebk0zzLvAeSh3UAlPT4vAdIPmecxew8NNL1ml4tYnRJIEhLhi9PKxt3xaezaMm_vhAEH1vfd53R2jvuyelB-OHeUgYqIKNFaOha2l7VoL3cW2YeLbfOrU0peOrmuIQ6aoYg5J0zDCgzf2UeR7Ufa43W2g9AsPj28We1chqtNOveZTtah-imaD4i6difmwKJmK-NLQ5dJYDpMBbYBw4Oisob8FSsJzUDu3fV2ffwDrUKg6.ZHTZ-g.uIdnd7ajZWr3u9pk4fqop6LNsuo 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.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 (217152, 217146, 217066, 217151, 217117, 217150, 217137, 217149, 217148, 217147, 217142, 217145, 217144, 217143, 217141, 217138, 217140, 217139, 217136, 217135, 217134, 217084, 217130, 217133, 217132, 217021, 217131, 217099, 217129, 217128, 217127, 217126, 217125, 216875, 217124, 217123, 217120, 217119, 217122, 217121, 217118, 217116, 216904, 217115, 217114, 217113, 217105, 217109, 217104, 217108, 217103, 217107, 217112, 217111, 217106, 217110, 217102, 217101, 217100, 217096, 217098, 217097, 217094, 217095, 217093, 217092, 217091, 217090, 217089, 217088, 217087, 217086, 217081, 217085, 217083, 217078, 217080, 216873, 217015, 217077, 217072, 217071, 217067, 217063, 217059, 217056, 217058, 217057, 217055, 217049, 217036, 217048, 217045, 217043, 217042, 217032, 217029, 217027, 217025, 217019, 217017, 217014, 217010, 216874, 217007, 216944, 217006, 217004, 217003, 217002, 217000, 216999, 216991, 216996, 216989, 216995, 216984, 216980, 216901, 216976, 216882, 216973, 216971, 216967, 216959, 216964, 216962, 216961, 216960, 216892, 216897, 216952, 216951, 216950, 216863, 216865, 216943, 216940, 216937, 216932, 216935, 216934, 216930, 216926, 216922, 216920, 216918, 216927, 216911, 216908, 216907, 216912, 216893, 216896, 216887, 216889, 216891, 216890, 216884, 216864, 216879, 216878, 216877, 216876, 216870, 216871, 216867, 216866, 216860, 216858, 216857, 216856, 216855) 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.5, mysql)

Reported on decksite by mysql-perf

Location Hash: e6a2dcf07412409bd46dd1beec603d168789cc32

Request Data ``` Request Method: GET Path: /admin/archetypes/? Cookies: ImmutableMultiDict([('session', '.eJx1UctuwjAQ_BefU5SEvMyRioIQpSCKVFFVkRWvE5fEDrbTEhD_XicqcKh63N3ZmZ3ZM0prUBURIAwaGdWAgwituEAjRkptKwoVryS9DinXmVQ05baDsI_9cBi7iR-6SRJ5OER3RCkzUoJFgXjYbuzg_xUu0rzh5U2klHkOtLtMS9FL2TXPQZXJZdpoUIJUHXMrG0W-QJEcOOXSWCpJGlP4qTbEdIjJ-sACd7mUoeBveDjZsS0uZmujN9H0ML3jjdyD9XxGJMtA62uN6pkiu-D1xMa4Ok3jdiLm-6Mmh4zx03dnCo41V6BTYuPzosSPgjjy4gGOk6Hn38ddoJEbJK7rIAXMtoqbRrlePR32efgcrXTBHrFaxHPmLRYvIWQxtRo2z9qaeUd9SHrwKS2d81v1wdrvcdaiDwf1pKlpuwU0BqJAoYuD_kRpiMrB3iyasrz8AI4uqL4.ZHaLiA.V1GhCu6ZOfRak7vmfmb-WmRyvuU'), ('hide_intro', 'True'), ('views', '8')]) Endpoint: edit_archetypes View Args: {} Person: 929253708250886195 Referrer: https://pennydreadfulmagic.com/decks/217109/ Request Data: {} Host: pennydreadfulmagic.com Connection: Keep-Alive Accept-Encoding: gzip X-Forwarded-For: 2607:fea8:2dde:5d00:d974:91c1:5ffd:f364 Cf-Ray: 7cfae1cc7de05992-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/decks/217109/ 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.ZHaLiA.V1GhCu6ZOfRak7vmfmb-WmRyvuU; hide_intro=True; views=8 Cf-Connecting-Ip: 2607:fea8:2dde:5d00:d974:91c1:5ffd:f364 Cf-Ipcountry: CA Cdn-Loop: cloudflare ```

Labels: decksite

vorpal-buildbot commented 1 year ago

Exceeded slow_query limit (2.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 (217155, 217154, 217153, 217152, 217146, 217066, 217151, 217117, 217150, 217137, 217149, 217148, 217147, 217142, 217145, 217144, 217143, 217141, 217138, 217140, 217139, 217136, 217135, 217134, 217084, 217130, 217133, 217132, 217021, 217131, 217099, 217129, 217128, 217127, 217126, 217125, 216875, 217124, 217123, 217120, 217119, 217122, 217121, 217118, 217116, 216904, 217115, 217114, 217113, 217109, 217104, 217108, 217103, 217107, 217111, 217112, 217106, 217110, 217105, 217102, 217101, 217100, 217096, 217098, 217097, 217094, 217095, 217093, 217092, 217091, 217090, 217089, 217088, 217087, 217086, 217081, 217085, 217083, 217078, 217080, 216873, 217015, 217077, 217072, 217071, 217067, 217063, 217059, 217056, 217058, 217057, 217055, 217036, 217048, 217045, 217043, 217042, 217049, 217032, 217029, 217027, 217025, 217019, 217017, 217014, 217010, 216874, 217007, 216944, 217006, 217003, 217002, 217000, 216999, 217004, 216991, 216996, 216989, 216995, 216984, 216980, 216901, 216976, 216882, 216973, 216971, 216959, 216964, 216962, 216961, 216960, 216967, 216892, 216897, 216952, 216951, 216950, 216863, 216865, 216943, 216940, 216937, 216932, 216935, 216934, 216930, 216922, 216920, 216918, 216927, 216926, 216911, 216908, 216907, 216912, 216893, 216896, 216887, 216889, 216891, 216890, 216884, 216864, 216879, 216878, 216877, 216876, 216870, 216871, 216867, 216866, 216860, 216858, 216857, 216856, 216855) 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.6, mysql)

Reported on decksite by mysql-perf

Location Hash: e6a2dcf07412409bd46dd1beec603d168789cc32

Request Data ``` Request Method: GET Path: /admin/archetypes/? Cookies: ImmutableMultiDict([('session', '.eJx1UctuwjAQ_BefU5SEvMyRioIQpSCKVFFVkRWvE5fEDrbTEhD_XicqcKh63N3ZmZ3ZM0prUBURIAwaGdWAgwituEAjRkptKwoVryS9DinXmVQ05baDsI_9cBi7iR-6SRJ5OER3RCkzUoJFgXjYbuzg_xUu0rzh5U2klHkOtLtMS9FL2TXPQZXJZdpoUIJUHXMrG0W-QJEcOOXSWCpJGlP4qTbEdIjJ-sACd7mUoeBveDjZsS0uZmujN9H0ML3jjdyD9XxGJMtA62uN6pkiu-D1xMa4Ok3jdiLm-6Mmh4zx03dnCo41V6BTYuPzosSPgjjy4gGOk6Hn38ddoJEbJK7rIAXMtoqbRrlePR32efgcrXTBHrFaxHPmLRYvIWQxtRo2z9qaeUd9SHrwKS2d81v1wdrvcdaiDwf1pKlpuwU0BqJAoYuD_kRpiMrB3iyasrz8AI4uqL4.ZHaTEw.sx_1ZfsuaCYefHetjlBYE1MaKUQ'), ('hide_intro', 'True'), ('views', '88'), ('page_size', '20'), ('deck_id', '217155')]) Endpoint: edit_archetypes View Args: {} Person: 929253708250886195 Referrer: https://pennydreadfulmagic.com/people/wizzardofoz/admin Request Data: {} Host: pennydreadfulmagic.com Connection: Keep-Alive Accept-Encoding: gzip X-Forwarded-For: 2607:fea8:2dde:5d00:d974:91c1:5ffd:f364 Cf-Ray: 7cfb0ee488885ae0-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/people/wizzardofoz/admin 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.ZHaTEw.sx_1ZfsuaCYefHetjlBYE1MaKUQ; hide_intro=True; views=88; page_size=20; deck_id=217155 Cf-Connecting-Ip: 2607:fea8:2dde:5d00:d974:91c1:5ffd:f364 Cf-Ipcountry: CA Cdn-Loop: cloudflare ```

Labels: decksite

vorpal-buildbot commented 1 year ago

Exceeded slow_query limit (3.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 (217188, 217187, 217185, 217186, 217147, 217184, 217181, 217183, 217182, 217179, 217180, 217135, 217178, 217176, 217171, 217177, 217175, 217174, 217173, 217172, 217170, 217169, 217167, 217168, 217164, 217166, 217126, 217165, 217163, 217159, 217162, 217160, 217161, 217158, 217157, 216930, 217155, 217156, 217154, 217153, 217152, 217146, 217066, 217151, 217117, 217150, 217137, 217149, 217148, 217142, 217145, 217144, 217143, 217141, 217138, 217140, 217139, 217136, 217134, 217084, 217130, 217133, 217132, 217021, 217131, 217099, 217129, 217128, 217127, 217125, 216875, 217124, 217123, 217120, 217119, 217122, 217121, 217118, 217116, 216904, 217115, 217114, 217113, 217111, 217106, 217110, 217105, 217109, 217112, 217104, 217108, 217103, 217107, 217102, 217101, 217100, 217096, 217098, 217097, 217094, 217095, 217093, 217092, 217091, 217090, 217089, 217088, 217087, 217086, 217081, 217085, 217083, 217078, 217080, 216873, 217015, 217077, 217072, 217071, 217067, 217063, 217059, 217056, 217058, 217057, 217055, 217043, 217042, 217049, 217036, 217048, 217045, 217032, 217029, 217027, 217025, 217019, 217017, 217014, 217010, 216874, 217007, 216944, 217006, 217000, 216999, 217004, 217003, 217002, 216991, 216996, 216989, 216995, 216984, 216980, 216901, 216976, 216882, 216973, 216971, 216961, 216960, 216967, 216959, 216964, 216962, 216892, 216897, 216952, 216951, 216950, 216863, 216865, 216943, 216940, 216937, 216932, 216935, 216934, 216918, 216927, 216926, 216922, 216920, 216912, 216911, 216908, 216907, 216893, 216896, 216887, 216889, 216891, 216890, 216884, 216864, 216879, 216878, 216877, 216876, 216870, 216871, 216867, 216866, 216860, 216858, 216857, 216856, 216855) 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.1, mysql)

Reported on decksite by mysql-perf

Location Hash: e6a2dcf07412409bd46dd1beec603d168789cc32

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.ZHd3Lw.dS8ZUczbM49z8GMezxRbVk_YXcE'), ('views', '3')]) 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: 7d00a0f13fd7032d-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=3; session=.eJx1UV1rgzAU_S95dkVN60ff7MY2RmVdnTg6hgS9avyKJJFVS__7YqF2DPaWc-45OTcnJxR3wBvSQivRWvIeNETShrZXkEJDG5bOkIqE8TSmikGm4doYrxwb6zo2XBMv0U1Rs4TUoFTQ3oWBGvxvoW2c97SeQ35Z0YTyHNJpTcHaS7ChWxpqZM7iXgBvSTNpy7iBiig9I70szFhIIieebk0zzLvAeSh3UAlPT4vAdIPmecxew8NNL1ml4tYnRJIEhLhi9PKxt3xaezaMm_vhAEH1vfd53R2jvuyelB-OHeUgYqIKNFaOha2l7VoL3cW2YeLbfOrU0peOrmuIQ6aoYg5J0zDCgzf2UeR7Ufa43W2g9AsPj28We1chqtNOveZTtah-imaD4i6difmwKJmK-NLQ5dJYDpMBbYBw4Oisob8FSsJzUDu3fV2ffwDrUKg6.ZHd3Lw.dS8ZUczbM49z8GMezxRbVk_YXcE 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.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 (217225, 217224, 217223, 217208, 217222, 217221, 217220, 217219, 217218, 217217, 217212, 217216, 217215, 217214, 217213, 217209, 217211, 217210, 217207, 217169, 217206, 217205, 217204, 217162, 217203, 217202, 217201, 217200, 217199, 217198, 217193, 217197, 217196, 217166, 217195, 217194, 217027, 217190, 217182, 217192, 217189, 217191, 217188, 217187, 217185, 217181, 217183, 217180, 217177, 217174, 217172, 217167, 217168, 217126, 217160, 217157, 216930, 217156, 217066, 217150, 217137, 217149, 217148, 217145, 217140, 217136, 217134, 217084, 217021, 217131, 217099, 217125, 216875, 217120, 217121, 216904, 217115, 217111, 217109, 217107, 217112, 217102, 217100, 217094, 217093, 217092, 217081, 217085, 217083, 217078, 217080, 216873, 217015, 217077, 217072, 217071, 217067, 217063, 217059, 217056, 217058, 217057, 217055, 217045, 217043, 217042, 217049, 217036, 217048, 217032, 217029, 217025, 217019, 217017, 217014, 217010, 216874, 217007, 216944, 217006, 217002, 217000, 216999, 217004, 217003, 216991, 216996, 216989, 216995, 216984, 216980, 216901, 216976, 216882, 216973, 216971, 216962, 216961, 216960, 216967, 216959, 216964, 216892, 216897, 216952, 216951, 216950, 216863, 216865, 216943, 216940, 216937, 216932, 216935, 216934, 216920, 216918, 216927, 216926, 216922, 216907, 216912, 216911, 216908, 216893, 216896, 216887, 216889, 216891, 216890, 216884, 216864, 216879, 216878, 216877, 216876, 216870, 216871, 216867, 216866, 216860, 216858, 216857, 216856, 216855) 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.8, mysql)

Reported on decksite by mysql-perf

Location Hash: e6a2dcf07412409bd46dd1beec603d168789cc32

Request Data ``` Request Method: GET Path: /admin/archetypes/? Cookies: ImmutableMultiDict([('hide_intro', 'True'), ('hide_intro', 'True'), ('views', '792'), ('views', '812'), ('session', '.eJx1UV1rwkAQ_C_3nMolXuKlbw0VbaFQqlhrKeHIXeLV-wj3YRPF_96LoEKhj7M7O7M7ewRly4wkiikH7p3xLAKESq4ugDLJpaZXyG2lDS15qIA4ReNsjFGC0ngyRmmCwY0hdEUECyym7mZFaPw_wlXZeC6uJkI3DaPDYlars1WcZxGQrtGlt8woIgdhywWxxPRBQRPvtklpHXFDZ1Y5ipbZ7oC7QrTWr3Kxn__kHX_N91je-E7vWLj0CEhVMWsvGKyk7d-eHusXOu3qqc8L0W2p2dDFJMUfJsyzruWG2ZKE0OIsQynKMwxHEKUYw_GtP-SYQYQhjIBhdShtrybL5838sLG6bfsU0hhqKx8k8otkt27f18Ek5NiGaz5DcuE7vB4OPedkR986KF8Q-IrAWbR0_TAACkYMM-AUgb8ROmIaFnZWXojTL_36pfA.ZHRcjQ.9g65_DrjLGFkZvli9-o3xjSO7Zk'), ('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: edit_archetypes View Args: {} Person: 154363842451734528 Referrer: https://pennydreadfulmagic.com/admin/archetypes/ Request Data: {} Host: pennydreadfulmagic.com Connection: Keep-Alive Accept-Encoding: gzip X-Forwarded-For: 119.17.133.163 Cf-Ray: 7d0797cd5d4e29a6-MEL X-Forwarded-Proto: https Cf-Visitor: {"scheme":"https"} 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 Referer: https://pennydreadfulmagic.com/admin/archetypes/ Dnt: 1 Upgrade-Insecure-Requests: 1 Sec-Fetch-Dest: document Sec-Fetch-Mode: navigate Sec-Fetch-Site: same-origin 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.ZHRcjQ.9g65_DrjLGFkZvli9-o3xjSO7Zk; 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=812 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 (5.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 (217407, 217406, 217385, 217383, 217405, 217404, 217399, 217403, 217402, 217401, 217400, 217395, 217394, 217398, 217393, 217397, 217392, 217396, 217390, 217391, 217389, 217388, 217387, 217248, 217386, 217384, 217382, 217381, 217379, 217380, 217378, 217377, 217376, 217375, 217372, 217374, 217373, 217371, 217370, 217369, 217368, 217367, 217361, 217337, 217366, 217365, 217364, 217341, 217363, 217362, 217351, 217347, 217360, 217359, 217358, 217357, 217356, 217355, 217353, 217354, 217298, 217352, 217350, 217269, 217343, 217349, 217348, 217263, 217346, 217342, 217345, 217344, 217340, 217339, 217335, 217338, 217251, 217336, 217334, 217260, 217333, 217332, 217331, 217330, 217329, 217327, 217328, 217322, 217326, 217325, 217324, 217323, 217216, 217321, 217320, 217318, 217319, 217313, 217317, 217315, 217316, 217310, 217314, 217312, 217311, 217309, 217308, 217259, 217301, 217307, 217304, 217306, 217305, 217303, 217302, 217299, 217279, 217300, 217297, 217294, 217289, 217293, 217288, 217292, 217287, 217296, 217291, 217286, 217295, 217290, 217285, 217284, 217283, 217282, 217281, 217280, 217278, 217277, 217276, 217273, 217275, 217274, 217272, 217271, 217266, 217270, 217213, 217268, 217265, 217264, 217258, 217257, 217256, 217255, 217254, 217253, 217252, 217249, 217245, 217247, 217242, 217246, 217244, 217243, 217241, 217240, 217239, 217238, 217237, 217236, 217235, 217229, 217234, 217233, 217230, 217210, 217232, 217231, 217228, 217227, 217226, 217225, 217223, 217208, 217221, 217218, 217215, 217207, 217206, 217205, 217204, 217162, 217203, 217199, 217198, 217197, 217196, 217194, 217027, 217191, 217188, 217187, 217185, 217181, 217183, 217180, 217177, 217174, 217172, 217167, 217168, 217126, 217160, 217157, 216930, 217156, 217066, 217150, 217137, 217149, 217148, 217145, 217140, 217136, 217134, 217084, 217021, 217131, 217099, 217125, 216875, 217120, 217121, 216904, 217115, 217111, 217109, 217107, 217112, 217102, 217100, 217094, 217093, 217092, 217081, 217085, 217083, 217078, 217080, 216873, 217015, 217077, 217072, 217071, 217067, 217063, 217059, 217056, 217058, 217057, 217055, 217045, 217043, 217042, 217049, 217036, 217048, 217032, 217029, 217025, 217019, 217017, 217014, 217010, 216874, 217007, 216944, 217006, 217002, 217000, 216999, 217004, 217003, 216991, 216996, 216989, 216995, 216984, 216980, 216901, 216976, 216882, 216973, 216971, 216962, 216961, 216960, 216967, 216959, 216964, 216892, 216897, 216952, 216951, 216950, 216863, 216865, 216943, 216940, 216937, 216932, 216935, 216934, 216920, 216918, 216927, 216926, 216922, 216907, 216912, 216911, 216908, 216893, 216896, 216887, 216889, 216891, 216890, 216884, 216864, 216879, 216878, 216877, 216876, 216870, 216871, 216867, 216866, 216860, 216858, 216857, 216856, 216855) 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.1, mysql)

Reported on decksite by mysql-perf

Location Hash: e6a2dcf07412409bd46dd1beec603d168789cc32

Request Data ``` Request Method: GET Path: /admin/archetypes/? Cookies: ImmutableMultiDict([('hide_intro', 'True'), ('session', '.eJx1UV1rgzAU_S95dkVN60ff7MY2RmVdnTg6hgS9avyKJJFVS__7YqF2DPaWc-45OTcnJxR3wBvSQivRWvIeNETShrZXkEJDG5bOkIqE8TSmikGm4doYrxwb6zo2XBMv0U1Rs4TUoFTQ3oWBGvxvoW2c97SeQ35Z0YTyHNJpTcHaS7ChWxpqZM7iXgBvSTNpy7iBiig9I70szFhIIieebk0zzLvAeSh3UAlPT4vAdIPmecxew8NNL1ml4tYnRJIEhLhi9PKxt3xaezaMm_vhAEH1vfd53R2jvuyelB-OHeUgYqIKNFaOha2l7VoL3cW2YeLbfOrU0peOrmuIQ6aoYg5J0zDCgzf2UeR7Ufa43W2g9AsPj28We1chqtNOveZTtah-imaD4i6difmwKJmK-NLQ5dJYDpMBbYBw4Oisob8FSsJzUDu3fV2ffwDrUKg6.ZHrGag.whL0F3IiXa_fD0YV8-pGRTS-0-w'), ('views', '3')]) 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: 7d154fc9af008dbe-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.ZHrGag.whL0F3IiXa_fD0YV8-pGRTS-0-w; views=3 Priority: u=0, i Cdn-Loop: cloudflare Cf-Connecting-Ip: 97.103.253.204 Cf-Ipcountry: US ```

Labels: decksite

vorpal-buildbot commented 1 year ago

Exceeded slow_query limit (3.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 (217410, 217409, 217408, 217406, 217385, 217405, 217400, 217399, 217394, 217392, 217391, 217389, 217388, 217248, 217380, 217377, 217373, 217371, 217366, 217365, 217364, 217341, 217362, 217358, 217356, 217355, 217353, 217354, 217298, 217343, 217345, 217344, 217339, 217338, 217251, 217331, 217329, 217327, 217322, 217326, 217325, 217216, 217320, 217319, 217313, 217316, 217314, 217312, 217307, 217306, 217305, 217303, 217302, 217279, 217297, 217290, 217287, 217286, 217296, 217285, 217292, 217278, 217276, 217273, 217275, 217271, 217270, 217213, 217258, 217257, 217255, 217253, 217242, 217246, 217241, 217240, 217238, 217237, 217236, 217229, 217210, 217232, 217226, 217225, 217223, 217208, 217221, 217218, 217215, 217207, 217206, 217205, 217204, 217162, 217203, 217199, 217198, 217197, 217196, 217194, 217027, 217191, 217188, 217187, 217185, 217181, 217183, 217180, 217177, 217174, 217172, 217167, 217168, 217126, 217160, 217157, 216930, 217156, 217066, 217150, 217137, 217149, 217148, 217145, 217140, 217136, 217134, 217084, 217021, 217131, 217099, 217125, 216875, 217120, 217121, 216904, 217115, 217109, 217107, 217112, 217111, 217102, 217100, 217094, 217093, 217092, 217081, 217085, 217083, 217078, 217080, 216873, 217015, 217077, 217072, 217071, 217067, 217063, 217059, 217056, 217058, 217057, 217055, 217045, 217043, 217042, 217049, 217036, 217048, 217032, 217029, 217025, 217019, 217017, 217014, 217010, 216874, 217007, 216944, 217006, 217000, 216999, 217004, 217003, 217002, 216991, 216996, 216989, 216995, 216984, 216980, 216901, 216976, 216882, 216973, 216971, 216962, 216961, 216960, 216967, 216959, 216964, 216892, 216897, 216952, 216951, 216950, 216863, 216865, 216943, 216940, 216937, 216932, 216935, 216934, 216918, 216927, 216926, 216922, 216920, 216907, 216912, 216911, 216908, 216893, 216896, 216887, 216889, 216891, 216890, 216884, 216864, 216879, 216878, 216877, 216876, 216870, 216871, 216867, 216866, 216860, 216858, 216857, 216856, 216855) 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.7, mysql)

Reported on decksite by mysql-perf

Location Hash: e6a2dcf07412409bd46dd1beec603d168789cc32

Request Data ``` Request Method: GET Path: /admin/archetypes/? Cookies: ImmutableMultiDict([('hide_intro', 'True'), ('deck_id', '217409'), ('page_size', '20'), ('session', '.eJx1UV1rgzAU_S95dkVN60ff7MY2RmVdnTg6hgS9avyKJJFVS__7YqF2DPaWc-45OTcnJxR3wBvSQivRWvIeNETShrZXkEJDG5bOkIqE8TSmikGm4doYrxwb6zo2XBMv0U1Rs4TUoFTQ3oWBGvxvoW2c97SeQ35Z0YTyHNJpTcHaS7ChWxpqZM7iXgBvSTNpy7iBiig9I70szFhIIieebk0zzLvAeSh3UAlPT4vAdIPmecxew8NNL1ml4tYnRJIEhLhi9PKxt3xaezaMm_vhAEH1vfd53R2jvuyelB-OHeUgYqIKNFaOha2l7VoL3cW2YeLbfOrU0peOrmuIQ6aoYg5J0zDCgzf2UeR7Ufa43W2g9AsPj28We1chqtNOveZTtah-imaD4i6difmwKJmK-NLQ5dJYDpMBbYBw4Oisob8FSsJzUDu3fV2ffwDrUKg6.ZHrO4Q.qa2MTHQTqkhjgSyns8YjznXZM3k'), ('views', '49')]) Endpoint: edit_archetypes View Args: {} Person: 219733587300319234 Referrer: https://pennydreadfulmagic.com/decks/217409/ Request Data: {} Host: pennydreadfulmagic.com Connection: Keep-Alive Accept-Encoding: gzip X-Forwarded-For: 97.103.253.204 Cf-Ray: 7d158589191b2227-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/217409/ Accept-Language: en-US,en;q=0.9 Cookie: hide_intro=True; deck_id=217409; page_size=20; session=.eJx1UV1rgzAU_S95dkVN60ff7MY2RmVdnTg6hgS9avyKJJFVS__7YqF2DPaWc-45OTcnJxR3wBvSQivRWvIeNETShrZXkEJDG5bOkIqE8TSmikGm4doYrxwb6zo2XBMv0U1Rs4TUoFTQ3oWBGvxvoW2c97SeQ35Z0YTyHNJpTcHaS7ChWxpqZM7iXgBvSTNpy7iBiig9I70szFhIIieebk0zzLvAeSh3UAlPT4vAdIPmecxew8NNL1ml4tYnRJIEhLhi9PKxt3xaezaMm_vhAEH1vfd53R2jvuyelB-OHeUgYqIKNFaOha2l7VoL3cW2YeLbfOrU0peOrmuIQ6aoYg5J0zDCgzf2UeR7Ufa43W2g9AsPj28We1chqtNOveZTtah-imaD4i6difmwKJmK-NLQ5dJYDpMBbYBw4Oisob8FSsJzUDu3fV2ffwDrUKg6.ZHrO4Q.qa2MTHQTqkhjgSyns8YjznXZM3k; views=49 Priority: u=0, i Cdn-Loop: cloudflare Cf-Connecting-Ip: 97.103.253.204 Cf-Ipcountry: US ```

Labels: decksite

vorpal-buildbot commented 1 year ago

Exceeded slow_query limit (3.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 (217307, 217306, 217305, 217303, 217302, 217279, 217297, 217287, 217286, 217296, 217285, 217292, 217290, 217278, 217276, 217273, 217275, 217271, 217270, 217213, 217258, 217257, 217255, 217253, 217242, 217246, 217241, 217240, 217238, 217237, 217236, 217229, 217210, 217232, 217226, 217225, 217223, 217208, 217221, 217218, 217215, 217207, 217206, 217205, 217204, 217162, 217203, 217199, 217198, 217197, 217196, 217194, 217027, 217191, 217188, 217187, 217185, 217181, 217183, 217180, 217177, 217174, 217172, 217167, 217168, 217126, 217160, 217157, 216930, 217156, 217066, 217150, 217137, 217149, 217148, 217145, 217140, 217136, 217134, 217084, 217021, 217131, 217099, 217125, 216875, 217120, 217121, 216904, 217115, 217111, 217109, 217107, 217112, 217102, 217100, 217094, 217093, 217092, 217081, 217085, 217083, 217078, 217080, 216873, 217015, 217077, 217072, 217071, 217067, 217063, 217059, 217056, 217058, 217057, 217055, 217045, 217043, 217042, 217049, 217036, 217048, 217032, 217029, 217025, 217019, 217017, 217014, 217010, 216874, 217007, 216944, 217006, 217002, 217000, 216999, 217004, 217003, 216991, 216996, 216989, 216995, 216984, 216980, 216901, 216976, 216882, 216973, 216971, 216962, 216961, 216960, 216967, 216959, 216964, 216892, 216897, 216952, 216951, 216950, 216863, 216865, 216943, 216940, 216937, 216932, 216935, 216934, 216920, 216918, 216927, 216926, 216922, 216907, 216912, 216911, 216908, 216893, 216896, 216887, 216889, 216891, 216890, 216884, 216864, 216879, 216878, 216877, 216876, 216870, 216871, 216867, 216866, 216860, 216858, 216857, 216856, 216855) 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.0, mysql)

Reported on decksite by mysql-perf

Location Hash: e6a2dcf07412409bd46dd1beec603d168789cc32

Request Data ``` Request Method: GET Path: /admin/archetypes/? Cookies: ImmutableMultiDict([('hide_intro', 'True'), ('deck_id', '217409'), ('page_size', '20'), ('views', '95'), ('session', '.eJx1UV1rgzAU_S95dkVN60ff7MY2RmVdnTg6hgS9avyKJJFVS__7YqF2DPaWc-45OTcnJxR3wBvSQivRWvIeNETShrZXkEJDG5bOkIqE8TSmikGm4doYrxwb6zo2XBMv0U1Rs4TUoFTQ3oWBGvxvoW2c97SeQ35Z0YTyHNJpTcHaS7ChWxpqZM7iXgBvSTNpy7iBiig9I70szFhIIieebk0zzLvAeSh3UAlPT4vAdIPmecxew8NNL1ml4tYnRJIEhLhi9PKxt3xaezaMm_vhAEH1vfd53R2jvuyelB-OHeUgYqIKNFaOha2l7VoL3cW2YeLbfOrU0peOrmuIQ6aoYg5J0zDCgzf2UeR7Ufa43W2g9AsPj28We1chqtNOveZTtah-imaD4i6difmwKJmK-NLQ5dJYDpMBbYBw4Oisob8FSsJzUDu3fV2ffwDrUKg6.ZHrT5A.GT11EKcwONvluyHLIKnetHAO3fI')]) 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: 7d15a44e98d28db2-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; deck_id=217409; page_size=20; views=95; session=.eJx1UV1rgzAU_S95dkVN60ff7MY2RmVdnTg6hgS9avyKJJFVS__7YqF2DPaWc-45OTcnJxR3wBvSQivRWvIeNETShrZXkEJDG5bOkIqE8TSmikGm4doYrxwb6zo2XBMv0U1Rs4TUoFTQ3oWBGvxvoW2c97SeQ35Z0YTyHNJpTcHaS7ChWxpqZM7iXgBvSTNpy7iBiig9I70szFhIIieebk0zzLvAeSh3UAlPT4vAdIPmecxew8NNL1ml4tYnRJIEhLhi9PKxt3xaezaMm_vhAEH1vfd53R2jvuyelB-OHeUgYqIKNFaOha2l7VoL3cW2YeLbfOrU0peOrmuIQ6aoYg5J0zDCgzf2UeR7Ufa43W2g9AsPj28We1chqtNOveZTtah-imaD4i6difmwKJmK-NLQ5dJYDpMBbYBw4Oisob8FSsJzUDu3fV2ffwDrUKg6.ZHrT5A.GT11EKcwONvluyHLIKnetHAO3fI Priority: u=0, i Cdn-Loop: cloudflare Cf-Connecting-Ip: 97.103.253.204 Cf-Ipcountry: US ```

Labels: decksite

vorpal-buildbot commented 1 year ago

Exceeded slow_query limit (1.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 (217501, 217500, 217497, 217499, 217498, 217494, 217496, 217495, 217492, 217493, 217482, 217491, 217490, 217489, 217488, 217487, 217486, 217485, 217461, 217484, 217483, 217428, 217477, 217481, 217476, 217480, 217479, 217478, 217473, 217468, 217472, 217467, 217471, 217466, 217475, 217470, 217465, 217474, 217469, 217464, 217429, 217463, 217462, 217458, 217460, 217459, 217455, 217457, 217456, 217454, 217453, 217452, 217451, 217450, 217442, 217449, 217444, 217448, 217447, 217446, 217445, 217443, 217441, 217440, 217439, 217438, 217437, 217436, 217435, 217434, 217433, 217430, 217432, 217431, 217427, 217424, 217425, 217426, 217416, 217423, 217422, 217421, 217420, 217415, 217419, 217418, 217417, 217414) 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, 1.3, mysql)

Reported on decksite by mysql-perf

Location Hash: e6a2dcf07412409bd46dd1beec603d168789cc32

Request Data ``` Request Method: GET Path: /admin/archetypes/? Cookies: ImmutableMultiDict([('hide_intro', 'True'), ('views', '11'), ('session', '.eJx1UV1rgzAU_S95dkVN60ff7MY2RmVdnTg6hgS9avyKJJFVS__7YqF2DPaWc-45OTcnJxR3wBvSQivRWvIeNETShrZXkEJDG5bOkIqE8TSmikGm4doYrxwb6zo2XBMv0U1Rs4TUoFTQ3oWBGvxvoW2c97SeQ35Z0YTyHNJpTcHaS7ChWxpqZM7iXgBvSTNpy7iBiig9I70szFhIIieebk0zzLvAeSh3UAlPT4vAdIPmecxew8NNL1ml4tYnRJIEhLhi9PKxt3xaezaMm_vhAEH1vfd53R2jvuyelB-OHeUgYqIKNFaOha2l7VoL3cW2YeLbfOrU0peOrmuIQ6aoYg5J0zDCgzf2UeR7Ufa43W2g9AsPj28We1chqtNOveZTtah-imaD4i6difmwKJmK-NLQ5dJYDpMBbYBw4Oisob8FSsJzUDu3fV2ffwDrUKg6.ZHwewQ.1qc9K_Fw-c1aWOfQPfxCyGb5SNw')]) 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: 7d1db9066f189af2-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; views=11; session=.eJx1UV1rgzAU_S95dkVN60ff7MY2RmVdnTg6hgS9avyKJJFVS__7YqF2DPaWc-45OTcnJxR3wBvSQivRWvIeNETShrZXkEJDG5bOkIqE8TSmikGm4doYrxwb6zo2XBMv0U1Rs4TUoFTQ3oWBGvxvoW2c97SeQ35Z0YTyHNJpTcHaS7ChWxpqZM7iXgBvSTNpy7iBiig9I70szFhIIieebk0zzLvAeSh3UAlPT4vAdIPmecxew8NNL1ml4tYnRJIEhLhi9PKxt3xaezaMm_vhAEH1vfd53R2jvuyelB-OHeUgYqIKNFaOha2l7VoL3cW2YeLbfOrU0peOrmuIQ6aoYg5J0zDCgzf2UeR7Ufa43W2g9AsPj28We1chqtNOveZTtah-imaD4i6difmwKJmK-NLQ5dJYDpMBbYBw4Oisob8FSsJzUDu3fV2ffwDrUKg6.ZHwewQ.1qc9K_Fw-c1aWOfQPfxCyGb5SNw Priority: u=0, i Cdn-Loop: cloudflare Cf-Connecting-Ip: 97.103.253.204 Cf-Ipcountry: US ```

Labels: decksite

vorpal-buildbot commented 1 year ago

Exceeded slow_query limit (1.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 (217581, 217580, 217579, 217578, 217575, 217577, 217576, 217574, 217567, 217573, 217570, 217572, 217569, 217571, 217555, 217557, 217568, 217565, 217560, 217564, 217563, 217562, 217566, 217561, 217559, 217558, 217556, 217554, 217553, 217552, 217551, 217550, 217464, 217549, 217544, 217548, 217547, 217546, 217543, 217545, 217542, 217541, 217539, 217540, 217538, 217537, 217536, 217535, 217533, 217534, 217531, 217532, 217441, 217530, 217516, 217529, 217528, 217527, 217526, 217525, 217519, 217523, 217518, 217522, 217517, 217521, 217520, 217524, 217515, 217514, 217513, 217512, 217511, 217510, 217509, 217506, 217508, 217507, 217505, 217504, 217501, 217503, 217502, 217499, 217494, 217492, 217493, 217482, 217489, 217486, 217485, 217461, 217483, 217481, 217479, 217468, 217467, 217466, 217470, 217469, 217429, 217463, 217459, 217447, 217440, 217438, 217431, 217424, 217425, 217426, 217416, 217421, 217420, 217415, 217419, 217418) 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, 1.6, mysql)

Reported on decksite by mysql-perf

Location Hash: e6a2dcf07412409bd46dd1beec603d168789cc32

Request Data ``` Request Method: GET Path: /admin/archetypes/? Cookies: ImmutableMultiDict([('hide_intro', 'True'), ('session', '.eJx1UVFvgjAY_C99ZoYiVPHNmeh0RjMdzmxZSIUPqEBL2uLmjP99hUVdluyt991d7-v1hMIKZEk5cI0GWtZgIRqXjF9ADCUrRXyFTEVCxiEzE4R9j7iuj3t2H7uu7RAP3RSFiGgBRgX8Llgb4n8L42Fas-Ia8suKGpSmEDdrKsHbYOx2LVTqVIS1Aslp2Wh3NAepfd84BK115oRKU90wy6dsOx0eJrN5vl8Eo9Uwg49sVKvdJmV6ftNrkZvAwQnRKAKlLhhNvxI7yqTzGsjAe3E263Fve8gevAInc_ls_PBZMQkqpKZCTHo-8buEOB1sTg6-0U2pxHb7tm0hCYkZZdcMm1SRG894PJG5GO9klWESL_ePKztYrI4mw5Ramce8obYp1fZp_owlDfkz6-yFiXi3UHtpqI-NAd0DlSDR2UJ_G9RUpmBW5nVRnL8Bj0-oYA.ZHPgHQ.Kd65t8KB8mUdUpIzmD3CxaYf73Y'), ('session', '.eJx1UVFvgjAY_C99ZoYiVPHNmeh0RjMdzmxZSIUPqEBL2uLmjP99hUVdluyt991d7-v1hMIKZEk5cI0GWtZgIRqXjF9ADCUrRXyFTEVCxiEzE4R9j7iuj3t2H7uu7RAP3RSFiGgBRgX8Llgb4n8L42Fas-Ia8suKGpSmEDdrKsHbYOx2LVTqVIS1Aslp2Wh3NAepfd84BK115oRKU90wy6dsOx0eJrN5vl8Eo9Uwg49sVKvdJmV6ftNrkZvAwQnRKAKlLhhNvxI7yqTzGsjAe3E263Fve8gevAInc_ls_PBZMQkqpKZCTHo-8buEOB1sTg6-0U2pxHb7tm0hCYkZZdcMm1SRG894PJG5GO9klWESL_ePKztYrI4mw5Ramce8obYp1fZp_owlDfkz6-yFiXi3UHtpqI-NAd0DlSDR2UJ_G9RUpmBW5nVRnL8Bj0-oYA.ZH02EA.yY5QAG14m57kfGrftJ3hc5Vp1E4'), ('page_size', '20'), ('deck_id', '217559'), ('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: 7d24899a4e8f96b9-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; session=.eJx1UVFvgjAY_C99ZoYiVPHNmeh0RjMdzmxZSIUPqEBL2uLmjP99hUVdluyt991d7-v1hMIKZEk5cI0GWtZgIRqXjF9ADCUrRXyFTEVCxiEzE4R9j7iuj3t2H7uu7RAP3RSFiGgBRgX8Llgb4n8L42Fas-Ia8suKGpSmEDdrKsHbYOx2LVTqVIS1Aslp2Wh3NAepfd84BK115oRKU90wy6dsOx0eJrN5vl8Eo9Uwg49sVKvdJmV6ftNrkZvAwQnRKAKlLhhNvxI7yqTzGsjAe3E263Fve8gevAInc_ls_PBZMQkqpKZCTHo-8buEOB1sTg6-0U2pxHb7tm0hCYkZZdcMm1SRG894PJG5GO9klWESL_ePKztYrI4mw5Ramce8obYp1fZp_owlDfkz6-yFiXi3UHtpqI-NAd0DlSDR2UJ_G9RUpmBW5nVRnL8Bj0-oYA.ZHPgHQ.Kd65t8KB8mUdUpIzmD3CxaYf73Y; page_size=20; deck_id=217559; session=.eJx1UVFvgjAY_C99ZoYiVPHNmeh0RjMdzmxZSIUPqEBL2uLmjP99hUVdluyt991d7-v1hMIKZEk5cI0GWtZgIRqXjF9ADCUrRXyFTEVCxiEzE4R9j7iuj3t2H7uu7RAP3RSFiGgBRgX8Llgb4n8L42Fas-Ia8suKGpSmEDdrKsHbYOx2LVTqVIS1Aslp2Wh3NAepfd84BK115oRKU90wy6dsOx0eJrN5vl8Eo9Uwg49sVKvdJmV6ftNrkZvAwQnRKAKlLhhNvxI7yqTzGsjAe3E263Fve8gevAInc_ls_PBZMQkqpKZCTHo-8buEOB1sTg6-0U2pxHb7tm0hCYkZZdcMm1SRG894PJG5GO9klWESL_ePKztYrI4mw5Ramce8obYp1fZp_owlDfkz6-yFiXi3UHtpqI-NAd0DlSDR2UJ_G9RUpmBW5nVRnL8Bj0-oYA.ZH02EA.yY5QAG14m57kfGrftJ3hc5Vp1E4; views=10058 Priority: u=0, i Cdn-Loop: cloudflare Cf-Connecting-Ip: 136.25.106.188 Cf-Ipcountry: US ```

Labels: decksite

vorpal-buildbot commented 1 year ago

Exceeded slow_query limit (2.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 (217633, 217632, 217631, 217507, 217626, 217630, 217629, 217628, 217627, 217621, 217625, 217624, 217623, 217622, 217619, 217612, 217620, 217614, 217617, 217618, 217616, 217615, 217610, 217613, 217600, 217611, 217609, 217607, 217608, 217606, 217603, 217605, 217604, 217602, 217601, 217599, 217598, 217594, 217597, 217530, 217596, 217595, 217588, 217593, 217489, 217592, 217546, 217591, 217590, 217589, 217586, 217580, 217587, 217585, 217584, 217577, 217581, 217583, 217582, 217579, 217578, 217575, 217576, 217574, 217567, 217573, 217570, 217572, 217569, 217571, 217555, 217557, 217568, 217563, 217562, 217566, 217561, 217565, 217560, 217564, 217559, 217558, 217556, 217554, 217553, 217552, 217551, 217550, 217464, 217549, 217544, 217548, 217547, 217543, 217545, 217542, 217541, 217539, 217540, 217538, 217537, 217536, 217535, 217533, 217534, 217531, 217532, 217441, 217516, 217529, 217526, 217525, 217528, 217527, 217522, 217517, 217521, 217520, 217524, 217519, 217523, 217518, 217515, 217514, 217513, 217512, 217511, 217510, 217509, 217506, 217508, 217505, 217504, 217501, 217503, 217502, 217499, 217494, 217492, 217493, 217482, 217486, 217485, 217461, 217483, 217479, 217481, 217466, 217470, 217469, 217468, 217467, 217429, 217463, 217459, 217447, 217440, 217438, 217431, 217424, 217425, 217426, 217416, 217421, 217420, 217415, 217419, 217418) 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.3, mysql)

Reported on decksite by mysql-perf

Location Hash: e6a2dcf07412409bd46dd1beec603d168789cc32

Request Data ``` Request Method: GET Path: /admin/archetypes/? Cookies: ImmutableMultiDict([('hide_intro', 'True'), ('views', '14'), ('session', '.eJx1UV1rgzAU_S95dkVN60ff7MY2RmVdnTg6hgS9avyKJJFVS__7YqF2DPaWc-45OTcnJxR3wBvSQivRWvIeNETShrZXkEJDG5bOkIqE8TSmikGm4doYrxwb6zo2XBMv0U1Rs4TUoFTQ3oWBGvxvoW2c97SeQ35Z0YTyHNJpTcHaS7ChWxpqZM7iXgBvSTNpy7iBiig9I70szFhIIieebk0zzLvAeSh3UAlPT4vAdIPmecxew8NNL1ml4tYnRJIEhLhi9PKxt3xaezaMm_vhAEH1vfd53R2jvuyelB-OHeUgYqIKNFaOha2l7VoL3cW2YeLbfOrU0peOrmuIQ6aoYg5J0zDCgzf2UeR7Ufa43W2g9AsPj28We1chqtNOveZTtah-imaD4i6difmwKJmK-NLQ5dJYDpMBbYBw4Oisob8FSsJzUDu3fV2ffwDrUKg6.ZH4SVA.AOqaHh2D_VQrKMs7Vl509i_bxH8')]) 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: 7d29f2648dfd3707-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; views=14; session=.eJx1UV1rgzAU_S95dkVN60ff7MY2RmVdnTg6hgS9avyKJJFVS__7YqF2DPaWc-45OTcnJxR3wBvSQivRWvIeNETShrZXkEJDG5bOkIqE8TSmikGm4doYrxwb6zo2XBMv0U1Rs4TUoFTQ3oWBGvxvoW2c97SeQ35Z0YTyHNJpTcHaS7ChWxpqZM7iXgBvSTNpy7iBiig9I70szFhIIieebk0zzLvAeSh3UAlPT4vAdIPmecxew8NNL1ml4tYnRJIEhLhi9PKxt3xaezaMm_vhAEH1vfd53R2jvuyelB-OHeUgYqIKNFaOha2l7VoL3cW2YeLbfOrU0peOrmuIQ6aoYg5J0zDCgzf2UeR7Ufa43W2g9AsPj28We1chqtNOveZTtah-imaD4i6difmwKJmK-NLQ5dJYDpMBbYBw4Oisob8FSsJzUDu3fV2ffwDrUKg6.ZH4SVA.AOqaHh2D_VQrKMs7Vl509i_bxH8 Priority: u=0, i Cdn-Loop: cloudflare Cf-Connecting-Ip: 97.103.253.204 Cf-Ipcountry: US ```

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 (217705, 217704, 217703, 217702, 217701, 217682, 217700, 217699, 217694, 217698, 217697, 217696, 217695, 217690, 217685, 217689, 217684, 217693, 217688, 217692, 217687, 217691, 217686, 217683, 217681, 217680, 217670, 217679, 217675, 217678, 217677, 217673, 217676, 217674, 217485, 217672, 217611, 217671, 217669, 217668, 217661, 217667, 217656, 217666, 217665, 217664, 217663, 217662, 217660, 217659, 217658, 217647, 217657, 217620, 217652, 217655, 217654, 217653, 217641, 217651, 217649, 217650, 217593, 217645, 217648, 217646, 217643, 217644, 217642, 217639, 217638, 217640, 217629, 217637, 217635, 217636, 217634, 217632, 217631, 217507, 217623, 217622, 217619, 217612, 217614, 217617, 217618, 217616, 217610, 217613, 217600, 217606, 217605, 217604, 217601, 217599, 217598, 217594, 217597, 217530, 217596, 217595, 217489, 217592, 217546, 217591, 217590, 217589, 217586, 217580, 217587, 217585, 217579, 217576, 217567, 217570, 217569, 217571, 217555, 217568, 217563, 217566, 217558, 217556, 217554, 217464, 217549, 217544, 217543, 217545, 217540, 217538, 217537, 217536, 217535, 217533, 217531, 217532, 217516, 217529, 217525, 217528, 217527, 217526, 217522, 217519, 217515, 217514, 217511, 217510, 217509, 217506, 217508, 217501, 217502, 217499, 217494, 217492, 217493, 217482, 217486, 217461, 217483, 217481, 217479, 217467, 217466, 217470, 217469, 217468, 217429, 217463, 217459, 217447, 217440, 217438, 217431, 217424, 217425, 217426, 217416, 217421, 217420, 217415, 217419, 217418) 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: e6a2dcf07412409bd46dd1beec603d168789cc32

Request Data ``` Request Method: GET Path: /admin/archetypes/? Cookies: ImmutableMultiDict([('hide_intro', 'True'), ('views', '4'), ('session', '.eJx1UV1rgzAU_S95dkVN60ff7MY2RmVdnTg6hgS9avyKJJFVS__7YqF2DPaWc-45OTcnJxR3wBvSQivRWvIeNETShrZXkEJDG5bOkIqE8TSmikGm4doYrxwb6zo2XBMv0U1Rs4TUoFTQ3oWBGvxvoW2c97SeQ35Z0YTyHNJpTcHaS7ChWxpqZM7iXgBvSTNpy7iBiig9I70szFhIIieebk0zzLvAeSh3UAlPT4vAdIPmecxew8NNL1ml4tYnRJIEhLhi9PKxt3xaezaMm_vhAEH1vfd53R2jvuyelB-OHeUgYqIKNFaOha2l7VoL3cW2YeLbfOrU0peOrmuIQ6aoYg5J0zDCgzf2UeR7Ufa43W2g9AsPj28We1chqtNOveZTtah-imaD4i6difmwKJmK-NLQ5dJYDpMBbYBw4Oisob8FSsJzUDu3fV2ffwDrUKg6.ZH7jwg.OWe_nsYFU9BOsm-5IzupLFLUNX0')]) 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: 7d2f07266dab8d9c-MIA X-Forwarded-Proto: https Cf-Visitor: {"scheme":"https"} Sec-Ch-Ua: "Not.A/Brand";v="8", "Chromium";v="114", "Google Chrome";v="114" 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/114.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; views=4; session=.eJx1UV1rgzAU_S95dkVN60ff7MY2RmVdnTg6hgS9avyKJJFVS__7YqF2DPaWc-45OTcnJxR3wBvSQivRWvIeNETShrZXkEJDG5bOkIqE8TSmikGm4doYrxwb6zo2XBMv0U1Rs4TUoFTQ3oWBGvxvoW2c97SeQ35Z0YTyHNJpTcHaS7ChWxpqZM7iXgBvSTNpy7iBiig9I70szFhIIieebk0zzLvAeSh3UAlPT4vAdIPmecxew8NNL1ml4tYnRJIEhLhi9PKxt3xaezaMm_vhAEH1vfd53R2jvuyelB-OHeUgYqIKNFaOha2l7VoL3cW2YeLbfOrU0peOrmuIQ6aoYg5J0zDCgzf2UeR7Ufa43W2g9AsPj28We1chqtNOveZTtah-imaD4i6difmwKJmK-NLQ5dJYDpMBbYBw4Oisob8FSsJzUDu3fV2ffwDrUKg6.ZH7jwg.OWe_nsYFU9BOsm-5IzupLFLUNX0 Priority: u=0, i Cdn-Loop: cloudflare Cf-Connecting-Ip: 97.103.253.204 Cf-Ipcountry: US ```

Labels: decksite

vorpal-buildbot commented 1 year ago

Exceeded slow_query limit (2.9 > 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 (217747, 217746, 217745, 217738, 217744, 217743, 217742, 217741, 217740, 217739, 217737, 217736, 217735, 217734, 217733, 217722, 217732, 217723, 217731, 217730, 217729, 217725, 217728, 217655, 217727, 217726, 217724, 217721, 217720, 217719, 217718, 217672, 217717, 217712, 217716, 217715, 217714, 217710, 217713, 217637, 217711, 217709, 217597, 217708, 217707, 217706, 217705, 217703, 217702, 217701, 217682, 217700, 217698, 217696, 217695, 217694, 217699, 217689, 217688, 217684, 217691, 217683, 217681, 217680, 217670, 217676, 217674, 217485, 217611, 217669, 217668, 217656, 217665, 217664, 217660, 217659, 217657, 217620, 217652, 217654, 217641, 217649, 217593, 217645, 217644, 217639, 217640, 217629, 217636, 217632, 217631, 217507, 217623, 217622, 217619, 217612, 217614, 217617, 217618, 217616, 217610, 217613, 217600, 217606, 217605, 217604, 217601, 217599, 217598, 217594, 217530, 217596, 217595, 217489, 217592, 217546, 217591, 217590, 217589, 217586, 217580, 217587, 217585, 217579, 217576, 217567, 217570, 217569, 217571, 217555, 217568, 217566, 217563, 217558, 217556, 217554, 217464, 217549, 217544, 217543, 217545, 217540, 217538, 217537, 217536, 217535, 217533, 217531, 217532, 217516, 217529, 217525, 217528, 217527, 217526, 217522, 217519, 217515, 217514, 217511, 217510, 217509, 217506, 217508, 217501, 217502, 217499, 217494, 217492, 217493, 217482, 217486, 217461, 217483, 217481, 217479, 217467, 217466, 217470, 217469, 217468, 217429, 217463, 217459, 217447, 217440, 217438, 217431, 217424, 217425, 217426, 217416, 217421, 217420, 217415, 217419, 217418) 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.9, mysql)

Reported on decksite by mysql-perf

Location Hash: e6a2dcf07412409bd46dd1beec603d168789cc32

Request Data ``` Request Method: GET Path: /admin/archetypes/? Cookies: ImmutableMultiDict([('session', '.eJx1UctuwjAQ_BefUxSMCXZvhdIHpKC-UNWqiixnMYbEBtspUMS_16ECDlVvntndmd3xDmVLsCXXoD269LaCCPG8VPoIcihVafITVE4Ym2cqMKjJEoYppqQTt2g7JixB547CCF5A6AJ9cdsNhf9HlM5kpYqTSWGkhLxezBl9sKKUtSJUemmyyoHVvKyV_VZavggChld-hjPnua_5FX4zdrSw_XUfp5RdD1JDysdh3tzwyUqc-71ZQDh0h7gQ4NwRo_T9Rqx7Ty9kde9SW7FRS018Tz8k4tv5YZiHzVJZcBkPmTWTDiaEtEncYLgTJ7h9rtcxJjGhcRwhC9NAzU4mMn-U_m4Mkl0Nknl3O1q_9r8cHYueesYsmIQYl-GaD3TIxjXmJsjVMYavUtNteP4W0GeEDqKZ39YDqAvcgkX7CP1J0HMrISytq6LY_wCxo6RY.ZH-fZA.Pc0rAw_b6HedHgRKos5ysPXfpC4'), ('session', '.eJx1UctuwjAQ_BefUxSMCXZvhdIHpKC-UNWqiixnMYbEBtspUMS_16ECDlVvntndmd3xDmVLsCXXoD269LaCCPG8VPoIcihVafITVE4Ym2cqMKjJEoYppqQTt2g7JixB547CCF5A6AJ9cdsNhf9HlM5kpYqTSWGkhLxezBl9sKKUtSJUemmyyoHVvKyV_VZavggChld-hjPnua_5FX4zdrSw_XUfp5RdD1JDysdh3tzwyUqc-71ZQDh0h7gQ4NwRo_T9Rqx7Ty9kde9SW7FRS018Tz8k4tv5YZiHzVJZcBkPmTWTDiaEtEncYLgTJ7h9rtcxJjGhcRwhC9NAzU4mMn-U_m4Mkl0Nknl3O1q_9r8cHYueesYsmIQYl-GaD3TIxjXmJsjVMYavUtNteP4W0GeEDqKZ39YDqAvcgkX7CP1J0HMrISytq6LY_wCxo6RY.ZGCBgA.gYxTK_sM36vR9XstLyUt9xjAFuk'), ('hide_intro', 'True'), ('views', '26'), ('page_size', '20')]) Endpoint: edit_archetypes View Args: {} Person: 196928284703850496 Referrer: https://pennydreadfulmagic.com/ Request Data: {} Host: pennydreadfulmagic.com Connection: Keep-Alive Accept-Encoding: gzip X-Forwarded-For: 82.144.140.135 Cf-Ray: 7d339be6aeb0b36b-PRG 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: cs,sk;q=0.8,en-US;q=0.5,en;q=0.3 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_BefUxSMCXZvhdIHpKC-UNWqiixnMYbEBtspUMS_16ECDlVvntndmd3xDmVLsCXXoD269LaCCPG8VPoIcihVafITVE4Ym2cqMKjJEoYppqQTt2g7JixB547CCF5A6AJ9cdsNhf9HlM5kpYqTSWGkhLxezBl9sKKUtSJUemmyyoHVvKyV_VZavggChld-hjPnua_5FX4zdrSw_XUfp5RdD1JDysdh3tzwyUqc-71ZQDh0h7gQ4NwRo_T9Rqx7Ty9kde9SW7FRS018Tz8k4tv5YZiHzVJZcBkPmTWTDiaEtEncYLgTJ7h9rtcxJjGhcRwhC9NAzU4mMn-U_m4Mkl0Nknl3O1q_9r8cHYueesYsmIQYl-GaD3TIxjXmJsjVMYavUtNteP4W0GeEDqKZ39YDqAvcgkX7CP1J0HMrISytq6LY_wCxo6RY.ZH-fZA.Pc0rAw_b6HedHgRKos5ysPXfpC4; hide_intro=True; session=.eJx1UctuwjAQ_BefUxSMCXZvhdIHpKC-UNWqiixnMYbEBtspUMS_16ECDlVvntndmd3xDmVLsCXXoD269LaCCPG8VPoIcihVafITVE4Ym2cqMKjJEoYppqQTt2g7JixB547CCF5A6AJ9cdsNhf9HlM5kpYqTSWGkhLxezBl9sKKUtSJUemmyyoHVvKyV_VZavggChld-hjPnua_5FX4zdrSw_XUfp5RdD1JDysdh3tzwyUqc-71ZQDh0h7gQ4NwRo_T9Rqx7Ty9kde9SW7FRS018Tz8k4tv5YZiHzVJZcBkPmTWTDiaEtEncYLgTJ7h9rtcxJjGhcRwhC9NAzU4mMn-U_m4Mkl0Nknl3O1q_9r8cHYueesYsmIQYl-GaD3TIxjXmJsjVMYavUtNteP4W0GeEDqKZ39YDqAvcgkX7CP1J0HMrISytq6LY_wCxo6RY.ZGCBgA.gYxTK_sM36vR9XstLyUt9xjAFuk; views=26; page_size=20 Cdn-Loop: cloudflare Cf-Connecting-Ip: 82.144.140.135 Cf-Ipcountry: CZ ```

Labels: decksite