search

PennyDreadfulMTG / perf-reports

2 stars 2 forks source link

Exceeded slow_query limit (4.8 > 1.0) in mysql: ```CREATE TABLE IF NOT EXISTS `_new_cache_card` (`id` INTEGER NOT NULL PRIMARY KEY AUTO_INCREMENT , `name` VARCHAR(190) NOT NULL , `mana_cost` VARCHAR(190) , `cmc` REAL , `power` VARCHAR(190) , `toughness` VARCHAR(190) , `loyalty` VARCHAR(190) , `type_line` VARCHAR(190) NOT NULL , `oracle_text` LONGTEXT NOT NULL , `hand` INTEGER , `life` INTEGER , `position` VARCHAR(190) NOT NULL , `card_id` INTEGER NOT NULL , `oracle_id` CHAR(36) UNIQUE , `layout` VARCHAR(190) NOT NULL , `legalities` LONGTEXT , `names` LONGTEXT , `pd_legal` BOOLEAN , `bugs` LONGTEXT , `flavor_names` LONGTEXT , FOREIGN KEY (`card_id`) REFERENCES `card`(`id`)) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci AS #63120

Open vorpal-buildbot opened 5 months ago

vorpal-buildbot commented 5 months ago 
    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.oracle_id AS oracle_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.oracle_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 = 22 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, 4.8, mysql)

Reported on decksite by mysql-perf

Location Hash: 8525a5e539ef5b665c5f49ec47ed6f240eecb18e
vorpal-buildbot commented 5 months ago

Exceeded slow_query limit (5.1 > 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 ,oracle_idCHAR(36) UNIQUE ,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.oracle_id AS oracle_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.oracle_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 = 22 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.1, mysql)

Reported on decksite by mysql-perf

Location Hash: 8525a5e539ef5b665c5f49ec47ed6f240eecb18e

Labels: decksite
vorpal-buildbot commented 5 months ago

Exceeded slow_query limit (6.1 > 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 ,oracle_idCHAR(36) UNIQUE ,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.oracle_id AS oracle_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.oracle_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 = 22 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, 6.1, mysql)

Reported on decksite by mysql-perf

Location Hash: 8525a5e539ef5b665c5f49ec47ed6f240eecb18e

Labels: decksite