vorpal-buildbot
commented
1 month ago Exceeded slow_query limit (3.8 > 1.0) in mysql: ```
SELECT
d.id,
d.finish,
d.decklist_hash,
cache.active_date,
cache.wins,
cache.losses,
cache.draws,
cache.color_sort,
ct.name AS competition_type_name,
COUNT(*) OVER () AS total
FROM
deck AS d
LEFT JOIN
competition AS c ON d.competition_id = c.id
LEFT JOIN
competition_series AS cs ON cs.id = c.competition_series_id
LEFT JOIN
competition_type AS ct ON ct.id = cs.competition_type_id
LEFT JOIN
deck_cache AS cache ON d.id = cache.deck_id
LEFT JOIN deck_cache AS season ON d.id = season.deck_id
WHERE
(d.id IN (SELECT deck_id FROM deck_card WHERE card IN ('Vivid Marsh', 'Dockside Chef', 'Silhana Ledgewalker', 'Helm of the Gods', 'Servant of the Scale', 'Avatar of the Resolute', 'Rampaging Baloths', 'Grave Titan', 'Brain Maggot', 'Out of Time', 'Taurean Mauler', 'Brushland', 'Commune with the Gods', 'Hogaak, Arisen Necropolis', 'Tranquil Thicket', 'Nissa, Voice of Zendikar', 'Sheltered Thicket', 'Jadelight Spelunker', 'Dread Return', 'Prophetic Prism', 'The Eternal Wanderer', 'Unflinching Courage', 'Cease // Desist', 'Wrath of God', 'Aphemia, the Cacophony', 'Abrupt Decay', 'Azusa''s Many Journeys', 'Nurturing Pixie', 'Swarm Shambler', 'Eldrazi Displacer', 'Hardened Scales', 'Kaya, Ghost Assassin', 'Warriors'' Lesson', 'Shriekhorn', 'Polukranos, Unchained', 'Disfigure', 'Obscura Storefront', 'Carapace', 'Spring-Loaded Sawblades', 'Soul Enervation', 'Damn', 'Hissing Quagmire', 'Argoth, Sanctum of Nature', 'Guardian of Ghirapur', 'Tymaret, Chosen from Death', 'Sentinel''s Mark', 'Escape Tunnel', 'Sylvan Scrying', 'Forbid', 'Gilt-Leaf Palace', 'Insidious Roots', 'Deduce', 'Courser of Kruphix', 'Cloudpost', 'Grakmaw, Skyclave Ravager', 'Golgari Grave-Troll', 'Kami of Transience', 'Touch the Spirit Realm', 'Incinerate', 'Glint-Horn Buccaneer', 'Spirited Companion', 'Kor Spiritdancer', 'Scattered Groves', 'Silence', 'Fortified Village', 'Michiko''s Reign of Truth', 'Maelstrom Pulse', 'Monument to Perfection', 'Woodland Cemetery', 'Benevolent Bodyguard', 'Hall of Heliod''s Generosity', 'Okina, Temple to the Grandfathers', 'Yorion, Sky Nomad', 'The Celestus', 'Tymaret Calls the Dead', 'Fire-Lit Thicket', 'Deadeye Tracker', 'Sphinx''s Revelation', 'Dire Fleet Daredevil', 'Setessan Training', 'Teachings of the Kirin', 'Squire''s Devotion', 'Skullbriar, the Walking Grave', 'Mystifying Maze', 'Volatile Fault', 'Collective Defiance', 'Rubblebelt Maverick', 'Vindicate', 'Kefnet the Mindful', 'Words of Wind', 'Etali, Primal Conqueror', 'Hour of Promise', 'Lumbering Falls', 'Raging Ravine', 'Weaver of Harmony', 'Generous Patron', 'Wooded Bastion', 'Field of the Dead', 'Temple of Mystery', 'Sylvan Library', 'Azusa, Lost but Seeking', 'Brokers Hideout', 'Webweaver Changeling', 'Cathedral of War', 'Conservatory', 'Heliod''s Pilgrim', 'Thragtusk', 'Path to Exile', 'Mortal Obstinacy', 'Crawling Barrens', 'Llanowar Wastes', 'Brass''s Tunnel-Grinder', 'Clay-Fired Bricks', 'Siege Rhino', 'Spell Pierce', 'Aftermath Analyst', 'Necroblossom Snarl', 'Search for Azcanta', 'Insolent Neonate', 'Cabaretti Courtyard', 'Jadelight Ranger', 'Terminus', 'Circuit Mender', 'Novice Inspector', 'Restoration Angel', 'Riveteers Overlook', 'Phantom Interference', 'Seaside Citadel', 'Ruric Thar, the Unbowed', 'Cartouche of Solidarity', 'Restless Vinestalk', 'Gloomshrieker', 'Flooded Grove', 'Tendo Ice Bridge', 'Kaslem''s Stonetree', 'Sunpetal Grove', 'Cruel Sadist', 'Eidolon of Blossoms', 'Temple of Plenty', 'Throne of the Grim Captain', 'Avacyn''s Pilgrim', 'Cenote Scout', 'Growth-Chamber Guardian', 'Call of the Death-Dweller', 'Lotleth Troll', 'Hinterland Harbor', 'Dreg Mangler', 'Grand Coliseum', 'Yavimaya Coast', 'The World Tree', 'No More Lies', 'Seismic Assault', 'Temple of Malady', 'Crucible of Worlds', 'Glimmervoid', 'Trash the Town', 'Cinder Glade', 'Putrid Imp', 'Knight of Autumn', 'Radagast the Brown'))) AND (TRUE)
GROUP BY
d.id,
d.competition_id, -- Every deck has only one competition_id but if we want to use competition_id in the HAVING clause we need this.
season.season_id -- In theory this is not necessary as all decks are in a single season and we join on the date but MySQL cannot work that out so give it the hint it needs.
HAVING
TRUE
ORDER BY
active_date DESC, d.finish IS NULL, d.finish
```[] (slow_query, 3.8, mysql)
Reported on decksite by mysql-perf
Location Hash: 7a146c3cf11d18b4615020ae73ef9b1e6be09489
Labels: decksite
[](slow_query, 2.4, mysql)Reported on decksite by mysql-perf
Location Hash: 7a146c3cf11d18b4615020ae73ef9b1e6be09489