vorpal-buildbot
commented
6 months ago Exceeded slow_query limit (5.7 > 5.0) in mysql: ``CREATE TABLE IF NOT EXISTS_new_cache_card(idINTEGER NOT NULL PRIMARY KEY AUTO_INCREMENT ,nameVARCHAR(190) NOT NULL ,mana_costVARCHAR(190) ,cmcREAL ,powerVARCHAR(190) ,toughnessVARCHAR(190) ,loyaltyVARCHAR(190) ,type_lineVARCHAR(190) NOT NULL ,oracle_textLONGTEXT NOT NULL ,handINTEGER ,lifeINTEGER ,positionVARCHAR(190) NOT NULL ,card_idINTEGER NOT NULL ,layoutVARCHAR(190) NOT NULL ,legalitiesLONGTEXT ,namesLONGTEXT ,pd_legalBOOLEAN ,bugsLONGTEXT ,flavor_namesLONGTEXT , FOREIGN KEY (card_id) REFERENCEScard(id`)) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci AS
SELECT
`u`.`id` AS `id`,
CASE
WHEN layout IN ('split') THEN
GROUP_CONCAT(face_name ORDER BY position SEPARATOR ' // ' )
ELSE
GROUP_CONCAT(CASE WHEN `u`.position = 1 THEN face_name ELSE '' END SEPARATOR '')
END
AS name,
CASE
WHEN layout IN ('adventure', 'modal_dfc', 'split') THEN
GROUP_CONCAT(`u`.`mana_cost` SEPARATOR '|')
ELSE
GROUP_CONCAT(CASE WHEN `u`.position = 1 THEN mana_cost ELSE '' END SEPARATOR '')
END
AS mana_cost,
CASE
WHEN layout IN ('split') THEN
SUM(CASE WHEN `u`.cmc IS NOT NULL THEN `u`.cmc ELSE 0 END)
ELSE
SUM(CASE WHEN `u`.position = 1 THEN `u`.cmc ELSE 0 END)
END
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 type_line ELSE '' END SEPARATOR '')
AS type_line, GROUP_CONCAT(`u`.`oracle_text` SEPARATOR '') AS oracle_text, SUM(CASE WHEN u.position = 1 THEN u.hand ELSE 0 END) AS hand, SUM(CASE WHEN u.position = 1 THEN u.life ELSE 0 END) AS life, GROUP_CONCAT(CASE WHEN u.position = 1 THEN u.position ELSE '' END SEPARATOR '') AS position, SUM(CASE WHEN u.position = 1 THEN u.card_id ELSE 0 END) AS card_id, u.layout AS layout, legalities, GROUP_CONCAT(face_name ORDER BY position SEPARATOR '|') AS names, u.pd_legal AS pd_legal, bugs, flavor_names
FROM (
SELECT c.id, c.layout, f.mana_cost, f.cmc, f.power, f.toughness, f.loyalty, f.type_line, f.oracle_text, f.hand, f.life, f.position, f.card_id, f.name AS face_name,
pd_legal,
legalities
FROM
card AS c
INNER JOIN
face AS f ON c.id = f.card_id
LEFT JOIN (
SELECT
cl.card_id,
SUM(CASE WHEN cl.format_id = 54 THEN 1 ELSE 0 END) > 0 AS pd_legal,
GROUP_CONCAT(CONCAT(fo.name, ':', cl.legality)) AS legalities
FROM
card_legality AS cl
LEFT JOIN
format AS fo ON cl.format_id = fo.id
GROUP BY
cl.card_id
) AS cl ON cl.card_id = c.id
GROUP BY
f.id
ORDER BY
f.card_id, f.position
) AS u
LEFT JOIN (
SELECT
cb.card_id,
GROUP_CONCAT(CONCAT(cb.description, '|', cb.classification, '|', cb.last_confirmed, '|', cb.url, '|', cb.from_bug_blog, '|', cb.bannable) SEPARATOR 'SEPARATOR') AS bugs
FROM
card_bug AS cb
GROUP BY
cb.card_id
) AS bugs ON u.id = bugs.card_id
LEFT JOIN (
SELECT
fn.card_id,
GROUP_CONCAT(fn.flavor_name SEPARATOR '|') AS flavor_names
FROM
card_flavor_name AS fn
GROUP BY
fn.card_id
) AS fn ON fn.card_id = u.id
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))
GROUP BY u.id
```[]``` (slow_query, 5.7, mysql)
Reported on mysql by mysql-perf
Location Hash: b117d1d5b87083d5adc6677d8b790e7e9f33160b
Labels: mysql
') AS
oracle_text, SUM(CASE WHENu.position = 1 THENu.handELSE 0 END) AShand, SUM(CASE WHENu.position = 1 THENu.lifeELSE 0 END) ASlife, GROUP_CONCAT(CASE WHENu.position = 1 THENu.positionELSE '' END SEPARATOR '') ASposition, SUM(CASE WHENu.position = 1 THENu.card_idELSE 0 END) AScard_id,u.layoutASlayout, legalities, GROUP_CONCAT(face_name ORDER BY position SEPARATOR '|') AS names,u.pd_legalASpd_legal, bugs, flavor_names FROM ( SELECT c.id, c.layout, f.mana_cost, f.cmc, f.power, f.toughness, f.loyalty, f.type_line, f.oracle_text, f.hand, f.life, f.position, f.card_id, f.name AS face_name, pd_legal, legalities FROM card AS c INNER JOIN face AS f ON c.id = f.card_id LEFT JOIN ( SELECT cl.card_id, SUM(CASE WHEN cl.format_id = 54 THEN 1 ELSE 0 END) > 0 AS pd_legal, GROUP_CONCAT(CONCAT(fo.name, ':', cl.legality)) AS legalities FROM card_legality AS cl LEFT JOIN format AS fo ON cl.format_id = fo.id GROUP BY cl.card_id ) AS cl ON cl.card_id = c.id GROUP BY f.id ORDER BY f.card_id, f.position ) AS u LEFT JOIN ( SELECT cb.card_id, GROUP_CONCAT(CONCAT(cb.description, '|', cb.classification, '|', cb.last_confirmed, '|', cb.url, '|', cb.from_bug_blog, '|', cb.bannable) SEPARATOR 'SEPARATOR') AS bugs FROM card_bug AS cb GROUP BY cb.card_id ) AS bugs ON u.id = bugs.card_id LEFT JOIN ( SELECT fn.card_id, GROUP_CONCAT(fn.flavor_name SEPARATOR '|') AS flavor_names FROM card_flavor_name AS fn GROUP BY fn.card_id ) AS fn ON fn.card_id = u.id 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)) GROUP BY u.id