vorpal-buildbot
commented
6 years ago Exceeded slow_query limit (5.1 > 5.0) in mysql: ```
SELECT
deck_id,
rule_archetype.name AS rule_archetype_name,
tagged_archetype.name AS tagged_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 = TRUE
GROUP BY
rule.id
),
candidates AS
(
SELECT
deck.id AS deck_id,
COUNT(DISTINCT deck_card.card) 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 = TRUE
) AS inclusions
ON
deck_card.card = inclusions.card
JOIN
rule
ON
rule.id = inclusions.rule_id
JOIN
rule_card_count
ON
rule.id = rule_card_count.id
WHERE
deck_card.sideboard = FALSE AND deck_card.n >= inclusions.n AND 1 = 1
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
include = FALSE
) AS exclusions
ON
candidates.rule_id = exclusions.rule_id
LEFT JOIN
deck_card
ON
candidates.deck_id = deck_card.deck_id AND exclusions.card = deck_card.card AND deck_card.n >= exclusions.n
GROUP BY
candidates.deck_id, rule_id
HAVING
COUNT(deck_card.card) = 0
) AS applied_rules
INNER JOIN
deck
ON
applied_rules.deck_id = deck.id
INNER JOIN
archetype AS rule_archetype
ON
rule_archetype.id = applied_rules.archetype_id
INNER JOIN
archetype AS tagged_archetype
ON
tagged_archetype.id = deck.archetype_id
WHERE
rule_archetype.id != tagged_archetype.id
```[] (slow_query, 5.1, mysql)
Reported on decksite by mysql-perf
--------------------------------------------------------------------------------
Request Method: POST
Path: /admin/rules/?
Cookies: {'__cfduid': 'dbbaeaf004b46114da480ca1ea67b1fe41519487476', 'hide_intro': 'True', '_ga': 'GA1.2.336853490.1519750417', 'session': '.eJx1kNtuwjAQRP9ln6PKTpxAeGvpDQqiVxCtqsiNN8bCuch2Wiji3-sgUaRKfZyd2R377CBr0JS8wsrBwJkWA-CiVNVRCGXz2ohMCRgATeOEsZT2SJ8yRsIkhgD-d3Sdc43exeqgpETR1dm6OtyjLAqgdLLOWoum4mWX_eBrNC5N_UbNW7cKM-u465wmvp7fr75H00cqeKhuinEyuzyPWjXXxbw85V299oWDHfA8R2uPGoSdbtjLWGrFGjV5kKPnoS0mS_IaueXC-H3cNMqgzbhHQeMwpmGapNEZSdJe1Kcnv6OTENYnJACDhR-tfkvC5OnudjIbkofPq5EsvsajHInuLejMyM3Ql3icjf_Nm-fmmati62eyVVpYeA_gcCZz2y4CF8gNGtgH8Bea40aif2XVar3_Abpoj4o.Dsj6aQ.XGMSQwbr_CRwdoHhxirMnjMcFpc', '_gid': 'GA1.2.929041383.1541262584', 'deck_id': '28642'}
Endpoint: post_rules
View Args: {}
Person: 195644917081440265
Referrer: https://pennydreadfulmagic.com/admin/rules/
Request Data: {'rule_id': '136', 'include': '1 Grenzo, Dungeon Warden\r\n', 'exclude': "1 Urza's Ruinous Blast\r\n1 Necrotic Ooze\r\n1 Goblin Warchief\r\n1 Peregrine Drake"}
Host: pennydreadfulmagic.com
Accept-Encoding: gzip
Cf-Ipcountry: US
X-Forwarded-For: 136.25.151.189, 172.68.189.141
Cf-Ray: 477c49281b486e08-SJC
X-Forwarded-Proto: https
Cf-Visitor: {"scheme":"https"}
Cache-Control: max-age=0
Origin: https://pennydreadfulmagic.com
Upgrade-Insecure-Requests: 1
Content-Type: application/x-www-form-urlencoded
User-Agent: Mozilla/5.0 (Macintosh; Intel Mac OS X 10_14_1) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/70.0.3538.77 Safari/537.36
Accept: text/html,application/xhtml+xml,application/xml;q=0.9,image/webp,image/apng,*/*;q=0.8
Referer: https://pennydreadfulmagic.com/admin/rules/
Accept-Language: en-US,en;q=0.9,es;q=0.8
Cookie: __cfduid=dbbaeaf004b46114da480ca1ea67b1fe41519487476; hide_intro=True; _ga=GA1.2.336853490.1519750417; session=.eJx1UMtuwjAQ_Jc9R5WdOIFwa-krFETpA0SrKnLjjbEwSWQ7LRTx73WQWqRKPc7OzM7u7IGLjapg4EyLAQhli9qIXAkYAE3jhLGU9kifMkbCJIYA_md0XXCNnsXqiKREkTdobF0d91EWBbBxss5bi6bim077ztdoXJp6R81btwpz67jrmCa-nt-vvrLJAxU8VDflKJlenketmutyvjnpXb32gYM98KJAa38wCDvZsueR1Io1ajyT2dPQluMleYnccmG8H7eNMmhz7vxtcRjTME3S6IwkaS_q0xPftZMQ1ickAIOlH61-Q8Lk8e52PB2S2cdVJsvPUVYg0b0FnRq5HfoQX2fjv3n1vWHlVLnzM9kqLSy8BXBck7tdJ4EL5AYNHAL4W5rjRqK_smq1PnwDU6SI9w.Dfj1kQ.6zIYF2Bw9YqqBtAVtkhDIFSf-AA; _gid=GA1.2.929041383.1541262584; deck_id=28642; session=.eJx1kNtuwjAQRP9ln6PKTpxAeGvpDQqiVxCtqsiNN8bCuch2Wiji3-sgUaRKfZyd2R377CBr0JS8wsrBwJkWA-CiVNVRCGXz2ohMCRgATeOEsZT2SJ8yRsIkhgD-d3Sdc43exeqgpETR1dm6OtyjLAqgdLLOWoum4mWX_eBrNC5N_UbNW7cKM-u465wmvp7fr75H00cqeKhuinEyuzyPWjXXxbw85V299oWDHfA8R2uPGoSdbtjLWGrFGjV5kKPnoS0mS_IaueXC-H3cNMqgzbhHQeMwpmGapNEZSdJe1Kcnv6OTENYnJACDhR-tfkvC5OnudjIbkofPq5EsvsajHInuLejMyM3Ql3icjf_Nm-fmmati62eyVVpYeA_gcCZz2y4CF8gNGtgH8Bea40aif2XVar3_Abpoj4o.Dsj6aQ.XGMSQwbr_CRwdoHhxirMnjMcFpc
Cf-Connecting-Ip: 136.25.151.189
X-Forwarded-Host: pennydreadfulmagic.com
X-Forwarded-Server: pennydreadfulmagic.com
Connection: Keep-Alive
Content-Length: 152Location Hash: 08fad211c3a921f55ebeef9e568657effa5bdad2
Labels: decksite
[](slow_query, 5.1, mysql)Reported on decksite by mysql-perf
Location Hash: 08fad211c3a921f55ebeef9e568657effa5bdad2