{"action":"closed","issue":{"url":"https://api.github.com/repos/PennyDreadfulMTG/perf-reports/issues/35108","repository_url":"https://api.github.com/repos/PennyDreadfulMTG/perf-reports","labels_url":"https://api.github.com/repos/PennyDreadfulMTG/perf-reports/issues/35108/labels{/name}","comments_url":"https://api.github.com/repos/PennyDreadfulMTG/perf-reports/issues/35108/comments","events_url":"https://api.github.com/repos/PennyDreadfulMTG/perf-reports/issues/35108/events","html_url":"https://github.com/PennyDreadfulMTG/perf-reports/issues/35108","id":358509712,"node_id":"MDU6SXNzdWUzNTg1MDk3MTI=","number":35108,"title":"Exceeded slow_query limit (15.2 > 10.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":[{"id":958792189,"node_id":"MDU6TGFiZWw5NTg3OTIxODk=","url":"https://api.github.com/repos/PennyDreadfulMTG/perf-reports/labels/decksite","name":"decksite","color":"bfd4f2","default":false},{"id":763867617,"node_id":"MDU6TGFiZWw3NjM4Njc2MTc=","url":"https://api.github.com/repos/PennyDreadfulMTG/perf-reports/labels/wontfix","name":"wontfix","color":"ffffff","default":true}],"state":"closed","locked":false,"assignee":null,"assignees":[],"milestone":null,"comments":0,"created_at":"2018-09-10T08:01:58Z","updated_at":"2018-11-16T09:33:26Z","closed_at":"2018-11-16T09:33:26Z","author_association":"NONE","body":"        SELECT\n            card AS name,\n            \n        SUM(CASE WHEN TRUE AND d.id IS NOT NULL THEN 1 ELSE 0 END) AS `all_num_decks`,\n        SUM(CASE WHEN TRUE THEN wins ELSE 0 END) AS `all_wins`,\n        SUM(CASE WHEN TRUE THEN losses ELSE 0 END) AS `all_losses`,\n        SUM(CASE WHEN TRUE THEN draws ELSE 0 END) AS `all_draws`,\n        SUM(CASE WHEN TRUE AND wins >= 5 AND losses = 0 AND d.source_id IN (SELECT id FROM source WHERE name = 'League') THEN 1 ELSE 0 END) AS all_perfect_runs,\n        SUM(CASE WHEN TRUE AND dsum.finish = 1 THEN 1 ELSE 0 END) AS `all_tournament_wins`,\n        SUM(CASE WHEN TRUE AND dsum.finish <= 8 THEN 1 ELSE 0 END) AS `all_tournament_top8s`,\n        IFNULL(ROUND((SUM(CASE WHEN TRUE THEN wins ELSE 0 END) / NULLIF(SUM(CASE WHEN TRUE THEN wins + losses ELSE 0 END), 0)) * 100, 1), '') AS `all_win_percent`\n    ,\n            \n        SUM(CASE WHEN dsum.created_date >= 1531465200 AND d.id IS NOT NULL THEN 1 ELSE 0 END) AS `season_num_decks`,\n        SUM(CASE WHEN dsum.created_date >= 1531465200 THEN wins ELSE 0 END) AS `season_wins`,\n        SUM(CASE WHEN dsum.created_date >= 1531465200 THEN losses ELSE 0 END) AS `season_losses`,\n        SUM(CASE WHEN dsum.created_date >= 1531465200 THEN draws ELSE 0 END) AS `season_draws`,\n        SUM(CASE WHEN dsum.created_date >= 1531465200 AND wins >= 5 AND losses = 0 AND d.source_id IN (SELECT id FROM source WHERE name = 'League') THEN 1 ELSE 0 END) AS season_perfect_runs,\n        SUM(CASE WHEN dsum.created_date >= 1531465200 AND dsum.finish = 1 THEN 1 ELSE 0 END) AS `season_tournament_wins`,\n        SUM(CASE WHEN dsum.created_date >= 1531465200 AND dsum.finish <= 8 THEN 1 ELSE 0 END) AS `season_tournament_top8s`,\n        IFNULL(ROUND((SUM(CASE WHEN dsum.created_date >= 1531465200 THEN wins ELSE 0 END) / NULLIF(SUM(CASE WHEN dsum.created_date >= 1531465200 THEN wins + losses ELSE 0 END), 0)) * 100, 1), '') AS `season_win_percent`\n    , -- We use the season data on the homepage to calculate movement, even though we no longer use it on /cards/.\n            \n        SUM(CASE WHEN dsum.created_date >= UNIX_TIMESTAMP(NOW() - INTERVAL 1 WEEK) AND d.id IS NOT NULL THEN 1 ELSE 0 END) AS `week_num_decks`,\n        SUM(CASE WHEN dsum.created_date >= UNIX_TIMESTAMP(NOW() - INTERVAL 1 WEEK) THEN wins ELSE 0 END) AS `week_wins`,\n        SUM(CASE WHEN dsum.created_date >= UNIX_TIMESTAMP(NOW() - INTERVAL 1 WEEK) THEN losses ELSE 0 END) AS `week_losses`,\n        SUM(CASE WHEN dsum.created_date >= UNIX_TIMESTAMP(NOW() - INTERVAL 1 WEEK) THEN draws ELSE 0 END) AS `week_draws`,\n        SUM(CASE WHEN dsum.created_date >= UNIX_TIMESTAMP(NOW() - INTERVAL 1 WEEK) AND wins >= 5 AND losses = 0 AND d.source_id IN (SELECT id FROM source WHERE name = 'League') THEN 1 ELSE 0 END) AS week_perfect_runs,\n        SUM(CASE WHEN dsum.created_date >= UNIX_TIMESTAMP(NOW() - INTERVAL 1 WEEK) AND dsum.finish = 1 THEN 1 ELSE 0 END) AS `week_tournament_wins`,\n        SUM(CASE WHEN dsum.created_date >= UNIX_TIMESTAMP(NOW() - INTERVAL 1 WEEK) AND dsum.finish <= 8 THEN 1 ELSE 0 END) AS `week_tournament_top8s`,\n        IFNULL(ROUND((SUM(CASE WHEN dsum.created_date >= UNIX_TIMESTAMP(NOW() - INTERVAL 1 WEEK) THEN wins ELSE 0 END) / NULLIF(SUM(CASE WHEN dsum.created_date >= UNIX_TIMESTAMP(NOW() - INTERVAL 1 WEEK) THEN wins + losses ELSE 0 END), 0)) * 100, 1), '') AS `week_win_percent`\n    \n        FROM\n            (SELECT card, deck_id FROM deck_card GROUP BY card, deck_id) AS dc -- Don't make sideboard cards \"count double\".\n        INNER JOIN\n            deck AS d ON dc.deck_id = d.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            (\n                SELECT\n                    d.id,\n                    d.created_date,\n                    d.finish,\n                    SUM(CASE WHEN dm.games > IFNULL(odm.games, 0) THEN 1 ELSE 0 END) AS wins, -- IFNULL so we still count byes as wins.\n                    SUM(CASE WHEN dm.games < odm.games THEN 1 ELSE 0 END) AS losses,\n                    SUM(CASE WHEN dm.games = odm.games THEN 1 ELSE 0 END) AS draws\n                FROM\n                    deck_match AS dm\n                INNER JOIN\n                    deck_match AS odm ON dm.match_id = odm.match_id AND dm.deck_id <> odm.deck_id\n                INNER JOIN\n                    deck AS d ON d.id = dm.deck_id\n                GROUP BY\n                    d.id\n            ) AS dsum ON d.id = dsum.id\n    \n        WHERE (d.person_id = 106) AND (season.id = 9)\n        GROUP BY\n            dc.card\n        ORDER BY\n            all_num_decks DESC,\n            SUM(dsum.wins - dsum.losses) DESC,\n            name\n    ```\n\n```[]``` (slow_query, 15.2, mysql)\n\nReported on decksite by mysql-perf```\n            --------------------------------------------------------------------------------\n            Request Method: GET\n            Path: /people/j_meka/?\n            Cookies: {'__cfduid': 'd1091fd8a89f7fcb0eaf2722eb6c733381509777174', '_ga': 'GA1.2.541409585.1509639551', 'hide_intro': 'True', '_gid': 'GA1.2.1208313384.1533835152', 'deck_id': '23767', '_gat_gtag_UA_109131120_1': '1', 'session': '.eJx1kN1ugkAQhd9lrkmzsKDgnVapqf1JWw2xTUNWdwQq7NLdxYjGd-9iYk2a9HLOnJkz8x0hrVFVTKAwMDCqQQcYrwpxKXih11LxtOAwAM-N-pQGYZ8SQt3Ioz448H-nlFmGvAvQUpw3uKTnQGUymTYalWAV2tmvtMIts37JGpN7qTbMdLpoN2XyKMZ3t8Od8u8Xh_0wkBMyi0WizfDqN3KL9t4jsPUatb7UsKQrvswT4cvXw_xJeWG78CeV4nlbJ_nSzuO-LhTqlNnX3YCSvheFLrkJeyQI6LXdwegRPyTEAYUbK-W_GTs_iKPDdBVvV2P25mYP36NnNy7eR9PFy2xsMyy92j7zYTFZxMWmtVrWFCXX8OnAeU1q2s4CI2QKFZwc-EvMMJWhPVI0ZXn6Abmbiyg.Dne2ZA.eLq-ywQxul7ZoZdqQgGcaOCeUoU'}\n            Endpoint: person\n            View Args: {'person_id': 'j_meka'}\n            Person: 219733587300319234\n            Referrer: https://pennydreadfulmagic.com/admin/archetypes/\n            Request Data: {}\nHost: pennydreadfulmagic.com\nAccept-Encoding: gzip\nCf-Ipcountry: US\nX-Forwarded-For: 2601:880:c000:1a7d:ec41:a128:3013:48fe, 108.162.210.156\nCf-Ray: 45805e344efb2e93-MIA\nX-Forwarded-Proto: https\nCf-Visitor: {\"scheme\":\"https\"}\nUpgrade-Insecure-Requests: 1\nUser-Agent: Mozilla/5.0 (Windows NT 6.3; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/68.0.3440.106 Safari/537.36\nAccept: text/html,application/xhtml+xml,application/xml;q=0.9,image/webp,image/apng,*/*;q=0.8\nReferer: https://pennydreadfulmagic.com/admin/archetypes/\nAccept-Language: en-US,en;q=0.9\nCookie: __cfduid=d1091fd8a89f7fcb0eaf2722eb6c733381509777174; _ga=GA1.2.541409585.1509639551; hide_intro=True; _gid=GA1.2.1208313384.1533835152; deck_id=23767; _gat_gtag_UA_109131120_1=1; session=.eJx1kN1ugkAQhd9lrkmzsKDgnVapqf1JWw2xTUNWdwQq7NLdxYjGd-9iYk2a9HLOnJkz8x0hrVFVTKAwMDCqQQcYrwpxKXih11LxtOAwAM-N-pQGYZ8SQt3Ioz448H-nlFmGvAvQUpw3uKTnQGUymTYalWAV2tmvtMIts37JGpN7qTbMdLpoN2XyKMZ3t8Od8u8Xh_0wkBMyi0WizfDqN3KL9t4jsPUatb7UsKQrvswT4cvXw_xJeWG78CeV4nlbJ_nSzuO-LhTqlNnX3YCSvheFLrkJeyQI6LXdwegRPyTEAYUbK-W_GTs_iKPDdBVvV2P25mYP36NnNy7eR9PFy2xsMyy92j7zYTFZxMWmtVrWFCXX8OnAeU1q2s4CI2QKFZwc-EvMMJWhPVI0ZXn6Abmbiyg.Dne2ZA.eLq-ywQxul7ZoZdqQgGcaOCeUoU\nCf-Connecting-Ip: 2601:880:c000:1a7d:ec41:a128:3013:48fe\nX-Forwarded-Host: pennydreadfulmagic.com\nX-Forwarded-Server: pennydreadfulmagic.com\nConnection: Keep-Alive\n```\n"}}
{"actor":{"display_login":"stale"}}