{"action":"opened","issue":{"url":"https://api.github.com/repos/PennyDreadfulMTG/perf-reports/issues/37831","repository_url":"https://api.github.com/repos/PennyDreadfulMTG/perf-reports","labels_url":"https://api.github.com/repos/PennyDreadfulMTG/perf-reports/issues/37831/labels{/name}","comments_url":"https://api.github.com/repos/PennyDreadfulMTG/perf-reports/issues/37831/comments","events_url":"https://api.github.com/repos/PennyDreadfulMTG/perf-reports/issues/37831/events","html_url":"https://github.com/PennyDreadfulMTG/perf-reports/issues/37831","id":360499516,"node_id":"MDU6SXNzdWUzNjA0OTk1MTY=","number":37831,"title":"Exceeded slow_query limit (3.0 > 3.0) in mysql: ```","user":{"login":"vorpal-buildbot","id":23072004,"node_id":"MDQ6VXNlcjIzMDcyMDA0","avatar_url":"https://avatars2.githubusercontent.com/u/23072004?v=4","gravatar_id":"","url":"https://api.github.com/users/vorpal-buildbot","html_url":"https://github.com/vorpal-buildbot","followers_url":"https://api.github.com/users/vorpal-buildbot/followers","following_url":"https://api.github.com/users/vorpal-buildbot/following{/other_user}","gists_url":"https://api.github.com/users/vorpal-buildbot/gists{/gist_id}","starred_url":"https://api.github.com/users/vorpal-buildbot/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/vorpal-buildbot/subscriptions","organizations_url":"https://api.github.com/users/vorpal-buildbot/orgs","repos_url":"https://api.github.com/users/vorpal-buildbot/repos","events_url":"https://api.github.com/users/vorpal-buildbot/events{/privacy}","received_events_url":"https://api.github.com/users/vorpal-buildbot/received_events","type":"User","site_admin":false},"labels":[],"state":"open","locked":false,"assignee":null,"assignees":[],"milestone":null,"comments":0,"created_at":"2018-09-15T02:33:03Z","updated_at":"2018-09-15T02:33:03Z","closed_at":null,"author_association":"NONE","body":"        SELECT\n            p.id,\n            COUNT(DISTINCT CASE WHEN ct.name = 'Gatherling' THEN d.id ELSE NULL END) AS tournament_entries,\n            COUNT(DISTINCT CASE WHEN d.finish = 1 AND ct.name = 'Gatherling' THEN d.id ELSE NULL END) AS tournament_wins,\n            COUNT(DISTINCT CASE WHEN ct.name = 'League' THEN d.id ELSE NULL END) AS league_entries,\n            CASE WHEN COUNT(CASE WHEN d.retired = 1 THEN 1 ELSE NULL END) = 0 THEN True ELSE False END AS completionist,\n            SUM(\n                CASE WHEN d.id IN\n                    (\n                        SELECT\n                            d.id\n                        FROM\n                            deck AS d\n                        \n        LEFT JOIN\n            competition AS c ON d.competition_id = c.id\n        LEFT JOIN\n            competition_series AS cs ON cs.id = c.competition_series_id\n        LEFT JOIN\n            competition_type AS ct ON ct.id = cs.competition_type_id\n    \n                        LEFT JOIN\n                            deck_match AS dm ON dm.deck_id = d.id\n                        LEFT JOIN\n                            deck_match AS odm ON odm.match_id = dm.match_id AND odm.deck_id <> d.id\n                        WHERE\n                            ct.name = 'League'\n                        GROUP BY\n                            d.id\n                        HAVING\n                            SUM(CASE WHEN dm.games > odm.games THEN 1 ELSE 0 END) >= 5\n                        AND\n                            SUM(CASE WHEN dm.games < odm.games THEN 1 ELSE 0 END) = 0\n                    )\n                THEN 1 ELSE 0 END\n            ) AS perfect_runs,\n            SUM(\n                CASE WHEN d.id IN\n                    (\n                        SELECT\n                            -- MAX here is just to fool MySQL to give us the id of the deck that crushed the perfect run from an aggregate function. There is only one value to MAX.\n                            MAX(CASE WHEN dm.games < odm.games AND dm.match_id IN (SELECT MAX(match_id) FROM deck_match WHERE deck_id = d.id) THEN odm.deck_id ELSE NULL END) AS deck_id\n                        FROM\n                            deck AS d\n                        INNER JOIN\n                            deck_match AS dm\n                        ON\n                            dm.deck_id = d.id\n                        INNER JOIN\n                            deck_match AS odm\n                        ON\n                            dm.match_id = odm.match_id AND odm.deck_id <> d.id\n                        WHERE\n                            d.competition_id IN (\n        SELECT\n            id\n        FROM\n            competition\n        WHERE\n            competition_series_id IN\n                (\n                    SELECT\n                        id\n                    FROM\n                        competition_series\n                    WHERE\n                        competition_type_id\n                    IN (\n        SELECT\n            id\n        FROM\n            competition_type\n        WHERE\n            name = 'League'\n    )\n                )\n        )\n                        GROUP BY d.id\n                        HAVING\n                            SUM(CASE WHEN dm.games > odm.games THEN 1 ELSE 0 END) >=4\n                        AND\n                            SUM(CASE WHEN dm.games < odm.games THEN 1 ELSE 0 END) = 1\n                        AND\n                            SUM(CASE WHEN dm.games < odm.games AND dm.match_id IN (SELECT MAX(match_id) FROM deck_match WHERE deck_id = d.id) THEN 1 ELSE 0 END) = 1\n                    )\n                THEN 1 ELSE 0 END\n            ) AS perfect_run_crushes\n        FROM\n            person AS p\n        LEFT JOIN\n            deck AS d ON d.person_id = p.id\n        \n        LEFT JOIN\n            (\n        SELECT\n            `start`.id,\n            `start`.code,\n            `start`.start_date AS start_date,\n            `end`.start_date AS end_date\n        FROM\n            season AS `start`\n        LEFT JOIN\n            season AS `end` ON `end`.id = `start`.id + 1\n    ) AS season ON season.start_date <= d.created_date AND (season.end_date IS NULL OR season.end_date > d.created_date)\n    \n        \n        LEFT JOIN\n            competition AS c ON d.competition_id = c.id\n        LEFT JOIN\n            competition_series AS cs ON cs.id = c.competition_series_id\n        LEFT JOIN\n            competition_type AS ct ON ct.id = cs.competition_type_id\n    \n        WHERE\n            p.id IN (2061) AND (TRUE)\n        GROUP BY\n            p.id\n    ```\n\n```[]``` (slow_query, 3.0, mysql)\n\nReported on decksite by mysql-perf```\n            --------------------------------------------------------------------------------\n            Request Method: GET\n            Path: /api/status/?\n            Cookies: {'__cfduid': 'd3f0965ce54775ab5a9e992d14d27fe7a1528390746', '_ga': 'GA1.2.585258135.1528390749', 'deck_id': '23380', '_gid': 'GA1.2.1974875045.1536861498', '_gat_gtag_UA_109131120_1': '1', 'session': '.eJx1UMtOwzAQ_BefI-QmxEl6o_QFLVFVUQRCKHLjjesmsVPbQTRV_x2nEvSAOO7O7MzOnFDWgK6pBGnR0OoWPMSEyZVmmWBoiAJMSIRDEgY4iKIk8GPkof-RSuW0AoeCvEycA-sdjJIXPR-TgYdqy1XWGtCS1j25FJKzloE7UbS1Oz8zltoemcMDWWNxWJRrweNk3VnWQLedKpDpfnPlW1U6x-EJ0TwHY35mtFCzZUtxHBXxzJhVfaCTTs3Ho_F03y2Iu4evRmgwGXXxB6Gf-GEUReFNQEgSDYIrLpwawbcxxh7SULjV7tekeknvn5-qdJNPulWRPr5tj7xMl3eva1vST2fi-mxcmndXnOtZFEe3462omEEfHrrIZPbYU9AIqAaNzh7605qlmoN7U7ZVdf4GVz-New.Dn4A0w.MCcZFU7F89eevJ9mOtI1_C2SOz8'}\n            Endpoint: person_status\n            View Args: {}\n            Person: 306670565303779328\n            Referrer: https://pennydreadfulmagic.com/league/\n            Request Data: {}\nHost: pennydreadfulmagic.com\nAccept-Encoding: gzip\nCf-Ipcountry: US\nX-Forwarded-For: 68.104.123.240, 162.158.246.96\nCf-Ray: 45a7af8e5f9d5f51-LAS\nX-Forwarded-Proto: https\nCf-Visitor: {\"scheme\":\"https\"}\nAccept: */*\nDnt: 1\nX-Requested-With: XMLHttpRequest\nUser-Agent: Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/68.0.3440.106 Safari/537.36\nReferer: https://pennydreadfulmagic.com/league/\nAccept-Language: en-US,en;q=0.9\nCookie: __cfduid=d3f0965ce54775ab5a9e992d14d27fe7a1528390746; _ga=GA1.2.585258135.1528390749; deck_id=23380; _gid=GA1.2.1974875045.1536861498; _gat_gtag_UA_109131120_1=1; session=.eJx1UMtOwzAQ_BefI-QmxEl6o_QFLVFVUQRCKHLjjesmsVPbQTRV_x2nEvSAOO7O7MzOnFDWgK6pBGnR0OoWPMSEyZVmmWBoiAJMSIRDEgY4iKIk8GPkof-RSuW0AoeCvEycA-sdjJIXPR-TgYdqy1XWGtCS1j25FJKzloE7UbS1Oz8zltoemcMDWWNxWJRrweNk3VnWQLedKpDpfnPlW1U6x-EJ0TwHY35mtFCzZUtxHBXxzJhVfaCTTs3Ho_F03y2Iu4evRmgwGXXxB6Gf-GEUReFNQEgSDYIrLpwawbcxxh7SULjV7tekeknvn5-qdJNPulWRPr5tj7xMl3eva1vST2fi-mxcmndXnOtZFEe3462omEEfHrrIZPbYU9AIqAaNzh7605qlmoN7U7ZVdf4GVz-New.Dn4A0w.MCcZFU7F89eevJ9mOtI1_C2SOz8\nCf-Connecting-Ip: 68.104.123.240\nX-Forwarded-Host: pennydreadfulmagic.com\nX-Forwarded-Server: pennydreadfulmagic.com\nConnection: Keep-Alive\n```\n"}}
{"actor":{"display_login":"vorpal-buildbot"}}