vorpal-buildbot
commented
5 years ago Exceeded slow_query limit (108.2 > 100.0) in mysql: ```
SELECT
d.id,
d.finish,
d.decklist_hash,
cache.active_date,
cache.wins,
cache.losses,
cache.draws
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
(
SELECT
`start`.id,
`start`.code,
`start`.start_date AS start_date,
`end`.start_date AS end_date
FROM
season AS `start`
LEFT JOIN
season AS `end` ON `end`.id = `start`.id + 1
) AS season ON season.start_date <= d.created_date AND (season.end_date IS NULL OR season.end_date > d.created_date)
WHERE
(d.id IN (SELECT deck_id FROM deck_card WHERE card IN ('Traxos, Scourge of Kroog', 'Consuming Vapors', 'Psychic Strike', 'Putrefy', 'Secluded Steppe', 'Zada''s Commando', 'Forbid', 'Lotleth Giant', 'Shefet Monitor', 'Summoner''s Egg', 'Anticipate', 'Seal of Primordium', 'Hanweir Militia Captain', 'Seal of Doom', 'Stun Sniper', 'Take Inventory', 'Rampant Growth', 'Martyr of Frost', 'Choked Estuary', 'Ivory Tower', 'Selesnya Guildgate', 'Savage Lands', 'Salvage Titan', 'Healing Hands', 'Blighted Fen', 'Unwind', 'Separatist Voidmage', 'Avoid Fate', 'Vessel of Nascency', 'Triskelion', 'Barter in Blood', 'Moratorium Stone', 'Cremate', 'Simic Guildgate', 'Unsummon', 'Pentavus', 'Salvager of Secrets', 'Desert of the Glorified', 'Crumbling Necropolis', 'Flameshadow Conjuring', 'Narcolepsy', 'Wharf Infiltrator', 'Urborg', 'Zulaport Cutthroat', 'Vitu-Ghazi, the City-Tree', 'Sculpting Steel', 'Psychic Spiral', 'Burning Oil', 'Cloudcrest Lake', 'Bloodsoaked Champion', 'Elemental Bond', 'Prepare // Fight', 'Azra Bladeseeker', 'Protean Hydra', 'Brimstone Volley', 'Whirler Virtuoso', 'Augury Owl', 'Aquitect''s Will', 'Charmbreaker Devils', 'Kessig Cagebreakers', 'Scourge Wolf', 'Always Watching', 'Hidden Stockpile', 'Enclave Elite', 'Memorial to Genius', 'Ondu Giant', 'Magus of the Future', 'Collective Effort', 'True Believer', 'Pillar of Flame', 'Oona''s Grace', 'Psychic Possession', 'Careful Consideration', 'Rishkar''s Expertise', 'Shrine of the Forsaken Gods', 'Toolcraft Exemplar', 'Elixir of Immortality', 'Geistflame', 'Jayemdae Tome', 'Perilous Myr', 'Identity Thief', 'The Mending of Dominaria', 'Mockery of Nature', 'Banewhip Punisher', 'Kuro, Pitlord', 'Spiritmonger', 'Vivid Meadow', 'Chromatic Lantern', 'Rattleclaw Mystic', 'Channeler Initiate', 'Selesnya Signet', 'Blighted Woodland', 'Drowned Secrets', 'Talus Paladin', 'Curious Homunculus', 'Maximize Velocity', 'Rupture Spire', 'Festering Newt', 'Diluvian Primordial', 'Manta Riders', 'Spear of Heliod', 'Graveblade Marauder', 'Protean Raider', 'Tracker''s Instincts', 'Helm of the Host', 'Greatbow Doyen', 'Consume the Meek', 'Scent of Brine', 'White Sun''s Zenith', 'Urza''s Factory', 'Gilded Lotus', 'Jwar Isle Refuge', 'Artificer''s Epiphany', 'Chief of the Foundry', 'Krosan Tusker', 'Psychic Drain', 'Browbeat', 'Howling Golem', 'Bogardan Hellkite', 'Bubbling Cauldron', 'Tome Scour', 'Deeproot Waters', 'Followed Footsteps', 'Academy Elite', 'Distress', 'Blood Scrivener', 'Vampire Cutthroat', 'March from the Tomb', 'Warleader''s Helix', 'Merrow Harbinger', 'Boggart Harbinger', 'Collective Defiance', 'Bloodwater Entity', 'Diabolic Tutor', 'High Priest of Penance', 'Consuming Aberration', 'Increasing Confusion', 'Banishing Light', 'Trial of Knowledge', 'Golgari Guildgate', 'Sultai Charm', 'Hedron Archive', 'Bile Blight', 'Cogwork Assembler', 'Veil of Secrecy', 'Cephalid Illusionist', 'Phantasmal Bear', 'Resolute Archangel', 'Planar Cleansing', 'Scatter to the Winds', 'Chained to the Rocks', 'Tempered Steel', 'Altar''s Reap', 'Pilgrim''s Eye', 'Mystic Monastery', 'Fiery Impulse', 'Herald of Torment', 'Merfolk Sovereign', 'Makeshift Mannequin', 'Expedition Envoy', 'Aven Riftwatcher', 'Conqueror''s Galleon', 'Sygg, River Guide', 'Crystal Ball', 'Psychotic Episode', 'Halimar Wavewatch', 'Scepter of Fugue', 'Addle', 'Desecration Demon', 'Sphinx Summoner', 'Crumbling Vestige', 'Reef Shaman', 'Cackling Counterpart', 'Curse of the Swine', 'Painful Truths', 'Benefaction of Rhonas', 'Longtusk Cub', 'Stolen Identity', 'Moment of Craving', 'Yavimaya Hollow', 'Fungal Infection', 'Dread Return', 'Mercurial Pretender', 'Last Breath', 'Dark Withering', 'Sram''s Expertise', 'Ally Encampment', 'Spelltwine', 'Hermit Druid', 'Sifter Wurm', 'Wanted Scoundrels', 'Vivid Grove', 'Dead Weight', 'Cloudblazer', 'Spined Thopter', 'Repeal', 'Spawning Pool', 'Flame Jab', 'Ritual of Rejuvenation', 'Tidal Warrior', 'Glint Hawk Idol', 'Hellspark Elemental', 'Astral Slide', 'Mirko Vosk, Mind Drinker', 'Early Frost', 'Thornweald Archer', 'Sphinx of Uthuun', 'Stromkirk Condemned', 'Haunted Dead', 'Dream Twist', 'Cloudgoat Ranger', 'Felhide Spiritbinder', 'Call the Bloodline', 'Skeleton Shard', 'Naga Vitalist', 'Read the Bones', 'Jace''s Ingenuity', 'Rootwater Depths', 'Shuko', 'Pale Rider of Trostad', 'Kindle', 'Flayer of the Hatebound', 'Ire Shaman', 'Faerie Trickery', 'Intet, the Dreamer', 'Hordeling Outburst', 'Oona, Queen of the Fae', 'Encroaching Wastes', 'Dack''s Duplicate', 'Clone', 'Trial of Ambition', 'Consign // Oblivion', 'Sylvan Advocate', 'Tatyova, Benthic Druid', 'Cataclysmic Gearhulk', 'Void Shatter', 'Trophy Mage', 'Bow of Nylea', 'Necropolis Fiend', 'Thawing Glaciers', 'Grow from the Ashes', 'Oath of Gideon', 'Planar Outburst', 'Radiant Flames', 'Demonlord of Ashmouth', 'Ancient Amphitheater', 'Sphere of the Suns', 'Sanctum of Ugin', 'Bojuka Brigand', 'Aether Swooper', 'Soul Foundry', 'Commune with Nature', 'Quasiduplicate', 'Spellheart Chimera', 'Scourglass', 'Epochrasite', 'Selesnya Guildmage', 'Desert of the Fervent', 'Winds of Rebuke', 'Faith''s Fetters', 'Evolving Wilds', 'Web of Inertia', 'One with the Machine', 'Precognition Field', 'Renewed Faith', 'Grenzo, Dungeon Warden', 'Millstone', 'Mycosynth Wellspring', 'Saving Grace', 'Serene Steward', 'Attune with Aether', 'Mercurial Chemister', 'River Sneak', 'Loxodon Warhammer', 'Temur Ascendancy', 'Scoured Barrens', 'Cultivator''s Caravan', 'Grozoth', 'Gigantomancer', 'Drownyard Behemoth', 'Declaration in Stone', 'Waterveil Cavern', 'Circuitous Route', 'Summon the School', 'Asylum Visitor', 'Pilfered Plans', 'Kjeldoran Outpost', 'Murderous Cut', 'Engulf the Shore', 'Agony Warp', 'Voldaren Pariah', 'Elite Archers', 'Fabrication Module', 'Confiscate', 'Spiketail Hatchling', 'Revitalize', 'Edge of Autumn', 'Galvanic Bombardment', 'Firedrinker Satyr', 'Crossbow Infantry', 'Prophet of Kruphix', 'Psychatog', 'Snapback', 'Baleful Ammit', 'Cryptoplasm', 'Gatekeeper of Malakir', 'Lash Out', 'Dreadship Reef', 'Restoration Specialist', 'Grove of the Guardian', 'Lonely Sandbar', 'Gateway Plaza', 'Rogue''s Passage', 'End Hostilities', 'Join the Ranks', 'Boros Challenger', 'Pain Seer', 'Foreboding Ruins', 'Make a Stand', 'Docent of Perfection', 'Trostani''s Summoner', 'Sundering Growth', 'Desert of the Indomitable', 'Insidious Will', 'Treasure Cruise', 'Scrabbling Claws', 'Trail of Evidence', 'Mind Grind', 'Cartouche of Strength', 'Tormenting Voice', 'Khalni Gem', 'Kederekt Leviathan', 'Autochthon Wurm', 'Rakshasa Deathdealer', 'Journeyer''s Kite', 'Falkenrath Gorger', 'Blisterpod', 'Court Homunculus', 'Drana''s Emissary', 'Hellrider', 'Incendiary Flow', 'Slith Firewalker', 'Hallowed Moonlight', 'Cadaver Imp', 'Putrid Imp', 'Seeker of the Way', 'Landslide', 'Saheeli''s Artistry', 'Felidar Guardian', 'Phantom Nomad', 'Fevered Visions', 'Decoction Module', 'Stasis Snare', 'Boon Reflection', 'Shaman en-Kor', 'Qasali Ambusher', 'Benevolent Bodyguard', 'Sin Collector', 'Rogue Refiner', 'Sin Prodder', 'Story Circle', 'Izzet Guildgate', 'Advent of the Wurm', 'Bogbrew Witch', 'Compulsive Research', 'Gruul Signet', 'Empyreal Voyager', 'Watchers of the Dead', 'Cunning Lethemancer', 'Terra Stomper', 'Dissipate', 'Power Sink', 'Opulent Palace', 'Hada Freeblade', 'Magister of Worth', 'Gathan Raiders', 'Unknown Shores', 'Sandsteppe Citadel', 'Skinrender', 'Exile', 'Final Judgment', 'Confirm Suspicions', 'Mesmeric Fiend', 'Servo Exhibition', 'Shrine of Loyal Legions', 'Duskmantle, House of Shadow', 'Ulcerate', 'Falkenrath Noble', 'Executioner''s Capsule', 'Azorius Guildgate', 'Tribute to Hunger', 'Inner Demon', 'Dregscape Zombie', 'Altered Ego', 'Brigid, Hero of Kinsbaile', 'Orzhov Charm', 'Ulvenwald Tracker', 'Memorial to Unity', 'Glorious Anthem', 'Broken Bond', 'Beneath the Sands', 'Loxodon Hierarch', 'Kopala, Warden of Waves', 'Prophetic Bolt', 'Chandra''s Spitfire', 'Entomber Exarch', 'Brutal Expulsion', 'Village Messenger', 'Memorial to War', 'Weight of Memory', 'Connive // Concoct', 'Dread Statuary', 'Wrecking Ball', 'Skirsdag High Priest', 'Lake of the Dead', 'Wretched Banquet', 'Chandra''s Ignition', 'Gruul Guildgate', 'Tranquil Expanse', 'Carrier Thrall', 'Reviving Dose', 'Bounty of the Luxa', 'Thopter Spy Network', 'Survival Cache', 'Sky Hussar', 'Phylactery Lich', 'Hypnotic Specter', 'Heir of Falkenrath', 'Vivid Marsh', 'Kalastria Healer', 'Sunbird''s Invocation', 'Perilous Voyage', 'Tranquil Garden', 'Sinister Concoction', 'Metalwork Colossus', 'Stone Quarry', 'Splinterfright', 'Rugged Highlands', 'Starstorm', 'Highland Lake', 'Vendetta', 'Ravenous Rats', 'Molten Primordial', 'Narcomoeba', 'Hammer of Bogardan', 'Scatter Arc', 'Indulgent Aristocrat', 'Captain''s Claws', 'Transgress the Mind', 'Harm''s Way', 'Slice and Dice', 'Pyramid of the Pantheon', 'Fleetwheel Cruiser', 'Increasing Savagery', 'Woodland Stream'))) 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.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
1 = 1
ORDER BY
active_date DESC, d.finish IS NULL, d.finish
```[] (slow_query, 108.2, mysql)
Reported on decksite by mysql-perf
Location Hash: 9225e75b66b37b77e3ac1d58359c27de20a1a24c
Labels: decksite
[](slow_query, 151.3, mysql)Reported on decksite by mysql-perf
Location Hash: 9225e75b66b37b77e3ac1d58359c27de20a1a24c