search

PennyDreadfulMTG / perf-reports

2 stars 2 forks source link

Exceeded slow_query limit (6.4 > 5.0) in mysql: ```SELECT bq.name FROM ( #59489

Open vorpal-buildbot opened 2 years ago

vorpal-buildbot commented 2 years ago 
    SELECT
        `u`.`id` AS `id`, 
    CASE
    WHEN layout = 'transform' OR layout = 'flip' OR layout = 'meld' OR layout = 'adventure' OR layout = 'modal_dfc' THEN
        GROUP_CONCAT(CASE WHEN `u`.position = 1 THEN face_name ELSE '' END SEPARATOR '')
    ELSE
        GROUP_CONCAT(face_name ORDER BY position SEPARATOR ' // ' )
    END
 AS name, 
    CASE
    WHEN layout = 'flip' THEN
        GROUP_CONCAT(CASE WHEN `u`.position = 1 THEN mana_cost ELSE '' END SEPARATOR '')
    ELSE
        GROUP_CONCAT(`u`.`mana_cost` SEPARATOR '|')
    END
 AS mana_cost, 
    CASE
    WHEN layout = '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 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 = 44 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 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 ) AS bq WHERE bq.id IN (SELECT card_id FROM card_legality WHERE format_id = 44)```

[] (slow_query, 6.4, mysql)

Reported on mysql by mysql-perf

Location Hash: 116aaee705c67a4e7809b71c61522efbdd9db8c5

vorpal-buildbot commented 2 years ago

Exceeded slow_query limit (9.3 > 5.0) in mysql: ```SELECT bq.name FROM (

    SELECT
        `u`.`id` AS `id`, 
    CASE
    WHEN layout = 'transform' OR layout = 'flip' OR layout = 'meld' OR layout = 'adventure' OR layout = 'modal_dfc' THEN
        GROUP_CONCAT(CASE WHEN `u`.position = 1 THEN face_name ELSE '' END SEPARATOR '')
    ELSE
        GROUP_CONCAT(face_name ORDER BY position SEPARATOR ' // ' )
    END
 AS name, 
    CASE
    WHEN layout = 'flip' THEN
        GROUP_CONCAT(CASE WHEN `u`.position = 1 THEN mana_cost ELSE '' END SEPARATOR '')
    ELSE
        GROUP_CONCAT(`u`.`mana_cost` SEPARATOR '|')
    END
 AS mana_cost, 
    CASE
    WHEN layout = '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 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 = 44 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 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 ) AS bq WHERE bq.id IN (SELECT card_id FROM card_legality WHERE format_id = 44)```

[] (slow_query, 9.3, mysql)

Reported on mysql by mysql-perf

Location Hash: 116aaee705c67a4e7809b71c61522efbdd9db8c5

Labels: mysql

vorpal-buildbot commented 1 year ago

Exceeded slow_query limit (7.7 > 5.0) in mysql: ```SELECT bq.name FROM (

    SELECT
        `u`.`id` AS `id`, 
    CASE
    WHEN layout = 'transform' OR layout = 'flip' OR layout = 'meld' OR layout = 'adventure' OR layout = 'modal_dfc' THEN
        GROUP_CONCAT(CASE WHEN `u`.position = 1 THEN face_name ELSE '' END SEPARATOR '')
    ELSE
        GROUP_CONCAT(face_name ORDER BY position SEPARATOR ' // ' )
    END
 AS name, 
    CASE
    WHEN layout = 'flip' THEN
        GROUP_CONCAT(CASE WHEN `u`.position = 1 THEN mana_cost ELSE '' END SEPARATOR '')
    ELSE
        GROUP_CONCAT(`u`.`mana_cost` SEPARATOR '|')
    END
 AS mana_cost, 
    CASE
    WHEN layout = '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 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 = 44 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 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 ) AS bq WHERE bq.id IN (SELECT card_id FROM card_legality WHERE format_id = 44)```

[] (slow_query, 7.7, mysql)

Reported on mysql by mysql-perf

Location Hash: 116aaee705c67a4e7809b71c61522efbdd9db8c5

Labels: mysql

vorpal-buildbot commented 1 year ago

Exceeded slow_query limit (11.5 > 5.0) in mysql: ```SELECT bq.name FROM (

    SELECT
        `u`.`id` AS `id`, 
    CASE
    WHEN layout = 'transform' OR layout = 'flip' OR layout = 'meld' OR layout = 'adventure' OR layout = 'modal_dfc' THEN
        GROUP_CONCAT(CASE WHEN `u`.position = 1 THEN face_name ELSE '' END SEPARATOR '')
    ELSE
        GROUP_CONCAT(face_name ORDER BY position SEPARATOR ' // ' )
    END
 AS name, 
    CASE
    WHEN layout = 'flip' THEN
        GROUP_CONCAT(CASE WHEN `u`.position = 1 THEN mana_cost ELSE '' END SEPARATOR '')
    ELSE
        GROUP_CONCAT(`u`.`mana_cost` SEPARATOR '|')
    END
 AS mana_cost, 
    CASE
    WHEN layout = '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 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 = 44 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 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 ) AS bq WHERE bq.id IN (SELECT card_id FROM card_legality WHERE format_id = 44)```

[] (slow_query, 11.5, mysql)

Reported on mysql by mysql-perf

Location Hash: 116aaee705c67a4e7809b71c61522efbdd9db8c5

Labels: mysql