vorpal-buildbot
commented
2 years ago Exceeded slow_query limit (63.5 > 60.0) in mysql: ```INSERT INTO _applied_rules (deck_id, rule_id, archetype_id, archetype_name)
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 rule.id = 334
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 TRUE AND rule.id = 334
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
```[] (slow_query, 63.5, mysql)
Reported on decksite by mysql-perf
Location Hash: 5205657cd573ec8c4f388dfebbb192e19cbb6642
Request Data
``` Request Method: POST Path: /api/rule/update? Cookies: ImmutableMultiDict([('hide_intro', 'True'), ('page_size', '20'), ('deck_id', '175300'), ('views', '2061'), ('session', '.eJx1UctuwjAQ_BefU5SkJsS9FfqAitADSoFWVWSSxTE4dmQ7pYD49zpUgFSpN-_O7Mzu-ICyGnRFJUiL7qxuwEO0qLg8FwVUvFLFpeQmV7rIuOuggHQjjEnQ8-MAYz-MuujKECqnAhwL5E06dcD_I1xmrOHiYiIUY1C0ixklT1YBvvVQZZnKGgNa0qoVXtINaEuIU1C0sWWYGUtti4zYqKlnhrOQhaEIxFaS8aTmZBGnyZpe-VZtwF16QDTPwZhzjfRDUYtkEO19MpztJslLkjSPyzmkevX1fu_m4bvmGkxGXWhBhGPiTiG40-3FOAyvcBtj5OPY9z2kYeVa5cWjXJTNNh2q_nyy3Afydb9-6r09s8G4VvFUOQ8XY-2O-UCnbIzr_D46a-V02zzdn_HVDn166CSa2V07gPpANWh09NDfBC3VDNzKshHi-AM1o6SX.Yk3vzA.H6-wD2saifG5F0bK6xKcNO_h6ek')]) Endpoint: post_rule_update View Args: {} Person: 195644917081440265 Referrer: https://pennydreadfulmagic.com/admin/rules/ Request Data: {'rule_id': '334', 'include': '1 Condemn\r\n1 Mana Leak', 'exclude': "1 Forest\r\n1 Swamp\r\n1 Mountain\r\n1 Battle of Wits\r\n1 Conqueror's Galleon\r\n4 Curse of the Bloody Tome\r\n1 Needle Spires\r\n2 Mass Polymorph\r\n4 Shared Fate\r\n1 Battle of Wits\r\n1 Thunderclap Wyvern\r\n3 Ephemerate\r\n3 Mind Sculpt\r\n1 Utter End\r\n1 Daru Spiritualist\r\n1 Enduring Renewal\r\n1 Mystical Teachings\r\n1 Cloudpost\r\n1 Vindicate\r\n1 Zur's Weirding\r\n1 Astral Slide\r\n1 Arcum Dagsson\r\n1 Drake Haven\r\n1 Naban, Dean of Iteration\r\n1 Phyrexian Dreadnought\r\n1 Hermit Druid\r\n1 Shape Anew\r\n1 Hive Mind\r\n1 Sanctum of All\r\n4 Squadron Hawk\r\n1 Eureka Moment"} Host: pennydreadfulmagic.com Accept-Encoding: gzip Cf-Ipcountry: US X-Forwarded-For: 64.125.252.70, 172.68.132.10 Cf-Ray: 6f7d13200fb00939-SJC X-Forwarded-Proto: https Cf-Visitor: {"scheme":"https"} Sec-Ch-Ua: " Not A;Brand";v="99", "Chromium";v="100", "Google Chrome";v="100" Accept: */* Content-Type: application/x-www-form-urlencoded; charset=UTF-8 X-Requested-With: XMLHttpRequest Sec-Ch-Ua-Mobile: ?0 User-Agent: Mozilla/5.0 (Macintosh; Intel Mac OS X 10_15_7) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/100.0.4896.60 Safari/537.36 Sec-Ch-Ua-Platform: "macOS" Origin: https://pennydreadfulmagic.com Sec-Fetch-Site: same-origin Sec-Fetch-Mode: cors Sec-Fetch-Dest: empty Referer: https://pennydreadfulmagic.com/admin/rules/ Accept-Language: en-US,en;q=0.9,es;q=0.8 Cookie: hide_intro=True; page_size=20; deck_id=175300; views=2061; session=.eJx1UctuwjAQ_BefU5SkJsS9FfqAitADSoFWVWSSxTE4dmQ7pYD49zpUgFSpN-_O7Mzu-ICyGnRFJUiL7qxuwEO0qLg8FwVUvFLFpeQmV7rIuOuggHQjjEnQ8-MAYz-MuujKECqnAhwL5E06dcD_I1xmrOHiYiIUY1C0ixklT1YBvvVQZZnKGgNa0qoVXtINaEuIU1C0sWWYGUtti4zYqKlnhrOQhaEIxFaS8aTmZBGnyZpe-VZtwF16QDTPwZhzjfRDUYtkEO19MpztJslLkjSPyzmkevX1fu_m4bvmGkxGXWhBhGPiTiG40-3FOAyvcBtj5OPY9z2kYeVa5cWjXJTNNh2q_nyy3Afydb9-6r09s8G4VvFUOQ8XY-2O-UCnbIzr_D46a-V02zzdn_HVDn166CSa2V07gPpANWh09NDfBC3VDNzKshHi-AM1o6SX.Yk3vzA.H6-wD2saifG5F0bK6xKcNO_h6ek Cf-Connecting-Ip: 64.125.252.70 Cdn-Loop: cloudflare X-Forwarded-Host: pennydreadfulmagic.com X-Forwarded-Server: pennydreadfulmagic.com Content-Length: 710 Connection: Keep-Alive ```Labels: decksite
[](slow_query, 75.4, mysql)Reported on decksite by mysql-perf
Location Hash: 5205657cd573ec8c4f388dfebbb192e19cbb6642
Request Data
``` Request Method: POST Path: /api/rule/update? Cookies: ImmutableMultiDict([('hide_intro', 'True'), ('page_size', '20'), ('deck_id', '175105'), ('views', '1465'), ('session', '.eJx1UctuwjAQ_BefU5SkJsS9FfqAitADSoFWVWSSxTE4dmQ7pYD49zpUgFSpN-_O7Mzu-ICyGnRFJUiL7qxuwEO0qLg8FwVUvFLFpeQmV7rIuOuggHQjjEnQ8-MAYz-MuujKECqnAhwL5E06dcD_I1xmrOHiYiIUY1C0ixklT1YBvvVQZZnKGgNa0qoVXtINaEuIU1C0sWWYGUtti4zYqKlnhrOQhaEIxFaS8aTmZBGnyZpe-VZtwF16QDTPwZhzjfRDUYtkEO19MpztJslLkjSPyzmkevX1fu_m4bvmGkxGXWhBhGPiTiG40-3FOAyvcBtj5OPY9z2kYeVa5cWjXJTNNh2q_nyy3Afydb9-6r09s8G4VvFUOQ8XY-2O-UCnbIzr_D46a-V02zzdn_HVDn166CSa2V07gPpANWh09NDfBC3VDNzKshHi-AM1o6SX.YktJ2Q.V64ZFRmJileJRvpHtxek0CUl0sM')]) Endpoint: post_rule_update View Args: {} Person: 195644917081440265 Referrer: https://pennydreadfulmagic.com/admin/rules/ Request Data: {'rule_id': '630', 'include': '3 Memory Lapse\r\n3 Day of Judgment', 'exclude': '4 Squadron Hawk\r\n1 Swamp\r\n1 Mountain\r\n1 Forest\r\n1 Hive Mind\r\n1 Shared Fate\r\n1 Sylvan Library\r\n1 Radiant Flames\r\n1 Ephemerate\r\n1 Drake Haven\r\n1 Cloudpost\r\n1 Isolated Chapel\r\n1 Battle of Wits'} Host: pennydreadfulmagic.com Accept-Encoding: gzip Cf-Ipcountry: US X-Forwarded-For: 2601:643:8880:36c0:297c:2e7f:eb3d:be7f, 172.68.143.211 Cf-Ray: 6f6c8775da3a964a-SJC X-Forwarded-Proto: https Cf-Visitor: {"scheme":"https"} Sec-Ch-Ua: " Not A;Brand";v="99", "Chromium";v="100", "Google Chrome";v="100" Accept: */* Content-Type: application/x-www-form-urlencoded; charset=UTF-8 X-Requested-With: XMLHttpRequest Sec-Ch-Ua-Mobile: ?0 User-Agent: Mozilla/5.0 (Macintosh; Intel Mac OS X 10_15_7) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/100.0.4896.60 Safari/537.36 Sec-Ch-Ua-Platform: "macOS" Origin: https://pennydreadfulmagic.com Sec-Fetch-Site: same-origin Sec-Fetch-Mode: cors Sec-Fetch-Dest: empty Referer: https://pennydreadfulmagic.com/admin/rules/ Accept-Language: en-US,en;q=0.9,es;q=0.8 Cookie: hide_intro=True; page_size=20; deck_id=175105; views=1465; session=.eJx1UctuwjAQ_BefU5SkJsS9FfqAitADSoFWVWSSxTE4dmQ7pYD49zpUgFSpN-_O7Mzu-ICyGnRFJUiL7qxuwEO0qLg8FwVUvFLFpeQmV7rIuOuggHQjjEnQ8-MAYz-MuujKECqnAhwL5E06dcD_I1xmrOHiYiIUY1C0ixklT1YBvvVQZZnKGgNa0qoVXtINaEuIU1C0sWWYGUtti4zYqKlnhrOQhaEIxFaS8aTmZBGnyZpe-VZtwF16QDTPwZhzjfRDUYtkEO19MpztJslLkjSPyzmkevX1fu_m4bvmGkxGXWhBhGPiTiG40-3FOAyvcBtj5OPY9z2kYeVa5cWjXJTNNh2q_nyy3Afydb9-6r09s8G4VvFUOQ8XY-2O-UCnbIzr_D46a-V02zzdn_HVDn166CSa2V07gPpANWh09NDfBC3VDNzKshHi-AM1o6SX.YktJ2Q.V64ZFRmJileJRvpHtxek0CUl0sM Cf-Connecting-Ip: 2601:643:8880:36c0:297c:2e7f:eb3d:be7f Cdn-Loop: cloudflare X-Forwarded-Host: pennydreadfulmagic.com X-Forwarded-Server: pennydreadfulmagic.com Content-Length: 303 Connection: Keep-Alive ```