{"action":"created","issue":{"url":"https://api.github.com/repos/PennyDreadfulMTG/Penny-Dreadful-Tools/issues/1843","repository_url":"https://api.github.com/repos/PennyDreadfulMTG/Penny-Dreadful-Tools","labels_url":"https://api.github.com/repos/PennyDreadfulMTG/Penny-Dreadful-Tools/issues/1843/labels{/name}","comments_url":"https://api.github.com/repos/PennyDreadfulMTG/Penny-Dreadful-Tools/issues/1843/comments","events_url":"https://api.github.com/repos/PennyDreadfulMTG/Penny-Dreadful-Tools/issues/1843/events","html_url":"https://github.com/PennyDreadfulMTG/Penny-Dreadful-Tools/issues/1843","id":274525765,"number":1843,"title":"Cards detail query consistently slow","user":{"login":"vorpal-buildbot","id":23072004,"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":4,"created_at":"2017-11-16T14:01:03Z","updated_at":"2017-11-16T23:40:34Z","closed_at":null,"author_association":"NONE","body":"        \r\n        SELECT\r\n            `u`.`id`, `u`.`layout`,\r\n            `u`.`id` AS face_id, \r\n        CASE\r\n        WHEN layout = 'double-faced' OR layout = 'flip' THEN\r\n            GROUP_CONCAT(CASE WHEN `u`.position = 1 THEN face_name ELSE '' END SEPARATOR '')\r\n        WHEN layout = 'meld' THEN\r\n            GROUP_CONCAT(CASE WHEN `u`.position = 1 OR `u`.position = 2 THEN face_name ELSE '' END SEPARATOR '')\r\n        ELSE\r\n            GROUP_CONCAT(face_name SEPARATOR ' // ' )\r\n        END\r\n     AS name, GROUP_CONCAT(`u`.`mana_cost` SEPARATOR '|') AS `mana_cost`, \r\n        CASE\r\n        WHEN layout = 'split' OR layout = 'aftermath' THEN\r\n            SUM(`u`.cmc)\r\n        ELSE\r\n            SUM(CASE WHEN `u`.position = 1 THEN `u`.cmc ELSE 0 END)\r\n        END\r\n     AS cmc, GROUP_CONCAT(CASE WHEN `u`.position = 1 THEN `u`.`power` ELSE '' END SEPARATOR '') AS `power`, GROUP_CONCAT(CASE WHEN `u`.position = 1 THEN `u`.`toughness` ELSE '' END SEPARATOR '') AS `toughness`, GROUP_CONCAT(CASE WHEN `u`.position = 1 THEN `u`.`loyalty` ELSE '' END SEPARATOR '') AS `loyalty`, GROUP_CONCAT(CASE WHEN `u`.position = 1 THEN `u`.`type` ELSE '' END SEPARATOR '') AS `type`, GROUP_CONCAT(`u`.`text` SEPARATOR \r\n    '\r\n    -----\r\n    ') AS `text`, GROUP_CONCAT(`u`.`search_text` SEPARATOR '\r\n    -----\r\n    ') AS `search_text`, GROUP_CONCAT(CASE WHEN `u`.position = 1 THEN `u`.`image_name` ELSE '' END SEPARATOR '') AS `image_name`, GROUP_CONCAT(CASE WHEN `u`.position = 1 THEN `u`.`hand` ELSE '' END SEPARATOR '') AS `hand`, GROUP_CONCAT(CASE WHEN `u`.position = 1 THEN `u`.`life` ELSE '' END SEPARATOR '') AS `life`, GROUP_CONCAT(CASE WHEN `u`.position = 1 THEN `u`.`starter` ELSE '' END SEPARATOR '') AS `starter`, GROUP_CONCAT(CASE WHEN `u`.position = 1 THEN `u`.`position` ELSE '' END SEPARATOR '') AS `position`, \r\n        CASE\r\n        WHEN layout = 'double-faced' OR layout = 'flip' THEN\r\n            GROUP_CONCAT(CASE WHEN `u`.position = 1 THEN name_ascii ELSE '' END SEPARATOR '')\r\n        WHEN layout = 'meld' THEN\r\n            GROUP_CONCAT(CASE WHEN `u`.position = 1 OR `u`.position = 2 THEN name_ascii ELSE '' END SEPARATOR '')\r\n        ELSE\r\n            GROUP_CONCAT(name_ascii SEPARATOR ' // ' )\r\n        END\r\n     AS name_ascii, GROUP_CONCAT(CASE WHEN `u`.position = 1 THEN `u`.`card_id` ELSE '' END SEPARATOR '') AS `card_id`,\r\n            GROUP_CONCAT(face_name SEPARATOR '|') AS names,\r\n            legalities,\r\n            pd_legal,\r\n            bug_desc,\r\n            bug_class,\r\n            bug_last_confirmed\r\n            FROM\r\n                (SELECT c.id, c.layout, f.mana_cost, f.cmc, f.power, f.toughness, f.loyalty, f.type, f.text, f.search_text, f.image_name, f.hand, f.life, f.starter, f.position, f.name_ascii, f.card_id, f.name AS face_name,\r\n                SUM(CASE WHEN cl.format_id = 33 THEN 1 ELSE 0 END) > 0 AS pd_legal,\r\n                GROUP_CONCAT(CONCAT(fo.name, ':', cl.legality)) AS legalities,\r\n                bugs.description AS bug_desc,\r\n                bugs.classification AS bug_class,\r\n                bugs.last_confirmed AS bug_last_confirmed\r\n                FROM card AS c\r\n                INNER JOIN face AS f ON c.id = f.card_id\r\n                LEFT OUTER JOIN card_legality AS cl ON c.id = cl.card_id\r\n                LEFT OUTER JOIN format AS fo ON cl.format_id = fo.id\r\n                LEFT OUTER JOIN card_bugs AS bugs ON c.id = bugs.card_id\r\n                GROUP BY f.id\r\n                ORDER BY f.card_id, f.position)\r\n            AS u\r\n            WHERE u.id IN (SELECT c.id FROM card AS c INNER JOIN face AS f ON c.id = f.card_id WHERE (1 = 1))\r\n            GROUP BY u.id\r\n    \r\n        \r\n    ; [] (slow_query, 2.3, mysql)\r\n\r\nReported on mysql by perf"},"comment":{"url":"https://api.github.com/repos/PennyDreadfulMTG/Penny-Dreadful-Tools/issues/comments/345098759","html_url":"https://github.com/PennyDreadfulMTG/Penny-Dreadful-Tools/issues/1843#issuecomment-345098759","issue_url":"https://api.github.com/repos/PennyDreadfulMTG/Penny-Dreadful-Tools/issues/1843","id":345098759,"user":{"login":"bakert","id":1541,"avatar_url":"https://avatars2.githubusercontent.com/u/1541?v=4","gravatar_id":"","url":"https://api.github.com/users/bakert","html_url":"https://github.com/bakert","followers_url":"https://api.github.com/users/bakert/followers","following_url":"https://api.github.com/users/bakert/following{/other_user}","gists_url":"https://api.github.com/users/bakert/gists{/gist_id}","starred_url":"https://api.github.com/users/bakert/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/bakert/subscriptions","organizations_url":"https://api.github.com/users/bakert/orgs","repos_url":"https://api.github.com/users/bakert/repos","events_url":"https://api.github.com/users/bakert/events{/privacy}","received_events_url":"https://api.github.com/users/bakert/received_events","type":"User","site_admin":false},"created_at":"2017-11-16T23:40:34Z","updated_at":"2017-11-16T23:40:34Z","author_association":"COLLABORATOR","body":"Pushing the pd_legal check into the JOIN instead of doing it post hoc on the gigantic joined table drops this from 1.8s to 0.2s on local. But this means the only legality available to the outer query is PD and not GROUP_CONCAT(CONCAT(fo.name, ':', cl.legality)) AS legalities.\r\n\r\nFetching legalities in a completely separate query by card_id and mashing the two together in memory makes more sense though.\r\n"}}
{"actor":{"display_login":"bakert"}}