With hundreds and hundreds of cards, The YuGiOh card game has so many cool cards that just aren't really worth the effort that it gets its own page.
Keep in mind, in the right deck, most of these examples can be viable if you have the time and creativity to do so.
There is a series of WATER monsters that are unaffected by Spells as long as Umi is on the field. This is great against cards like Raigeki and Dark Hole, until you realize that they can't be targeted by any Spell Cards, including your own.
Most Tribute Monsters (monsters with 5 or more level stars) in the card game. You can spend an enormous amount of resources on it and lose the whole thing to a simple Bottomless Trap Hole.
And then there's the anime-only card, Ragnarok. The effect? If Dark Magician,Dark Magician Girl,Dark Sage, or Magician of Black Chaos (a minimum of 2 required) are on the field, all monsters on the enemy's side of the field can be banished. The cost? You have to banish every monster from your hand, deck, and graveyard. The cost was probably only there for the undoubtedly awesome visual effect: all of Yugi's monsters appear and swarm the enemy in order to banish it.
In the card game, we have Final Destiny, which destroys all cards on the field at the cost of 5 discards. Since the maximum hand size is 6 (barring the use of spells like Hieroglyph Lithograph and Infinite Cards), playing Final Destiny leaves you with likely no hand and no field.
Super Vehicroid Stealth Union, a Combining Mecha Fusion Monster made of 4 specific monsters, with an effect that lets it attack all the opponent's monsters while negating their effects... and halving its own ATK, which isn't too high to begin with.
The banned Victory Dragon, an extremely hard-to-summon monster with poor stats that, if it somehow attacks directly for the win, wins the entire match. Not just this game, but an entire set of "best of three" duels. However, there's no rule saying your opponent can't just forfeit the duel when you attack, sparing him the match loss.
Perfectly Ultimate Great Moth embodies this trope: he's the strongest Insect in the game, but his Summoning conditions are practically impossible. But a reading of the card means that the listed summoning conditions only apply to special summons; you can tribute-summon normally.
And now the original Egyptian God Cards are being printed for legal tournament usage. They're somewhere between this trope and Game Breaker as they can be either useless or nearly unstoppable forces of destruction in the game as they require THREE tributes to summon. Being one of the few things that requires three tributes to summon, it would make them useless if they didn't block any and all effects from going off when they were summoned. Sadly they still fall to a Mirror Force just as easily as any other monster when they attack.
Armityle the Chaos Phantom. It requires banishing three cards (the Sacred Beasts, who can also fall under this trope) , however since each of those three cards themselves require three cards to play, it really takes a total of 12 cards to get out. It gains a whopping 10,000 attack during your turn, meaning any successful attack would almost certainly win you the game (unless your opponent had a +2000 attack monster or really boosted his life points) since you start with 8000 life points. As if being ridiculously hard to get out wasn't enough, it only gains 10,000 attack during your turn, can be destroyed by any common methods (except battle), and is actually inferior than the combine might of the cards it requires (the Sacred Beasts combined may have as little as 8,000 attack, but usually will have +12,000 attack).
Exodia: It gives you an instant win, but only if you have 5 certain cards in your hand at the same time (the cards can also be played as weak monsters). And you can only have one of each in your deck. The only way to use him efficiently is to have a deck completely built around getting him in your hand, which is still a heavily luck-based strategy which leaves you almost defenseless, especially if your opponent uses cards that discard from your hand.
Similarly, Exodia Necross. It starts off with 1800 Attack and gains 500 at the start of each of your turns, and can't be destroyed in battle, or as a result of any Spells or Traps (meaning no Raigeki or Mirror Force will get it off the field). The catch is that it can only be Special Summoned with a Spell Card that you can only use if all five pieces of Exodia are in your graveyard. And if any of those pieces are removed from the graveyard, it is immediately destroyed and can't be revived. It can also be destroyed by monster effects (like Cyber Jar) and can be banished from the field.
The Dark Crisis booster pack brought quite a few of these in addition to Exodia Necross, an example being Berserk Dragon. While it has 3500 Attack, it can only be Special Summoned with the Quickplay Spell Card A Deal with Dark Ruler on a turn that one of your Level 8 monsters is sent to the Graveyard. It also gets to attack all monsters on your opponent's field once...but in addition, it loses 500 Attack at the end of each of your turns, which will quickly make it easy prey to anything tough your opponent has.
Consider its really cool looking and effect in the anime, Gandora the Dragon of Destruction is considered this for four reasons. First of all, this card cannot be Special Summoned, which means that you will have to tribute two monsters on your field (or Double Coston, since you just need to tribute it only). Secondly, you will have to pay half of your LP to nuke the field except this card. Although it banishes the cards, most players prefer Judgement Dragon or Demise, King of Armageddon as they have a much lower cost when nuking the field. Thirdly, this card gains 300 ATK for each card destroyed this way. However, the ATK boost is not impressive unless there are lots of cards on the field beforehand. Forth and lastly, this card is sent to the Graveyard during the End Phase of the turn it was Summoned.
Fusion monsters partially depend on what card you're summoning. In the early days of the card game, there were worthless fusion cards like Flame Ghost or Fusionist, possibly some of the most useless cards in the game. Other fusions that are useless unless you get very lucky or are very good are Dragon Master Knight, requiring Black Luster Soldier (a ritual monster) and Blue-Eyes Ultimate Dragon, another fusion monster. Generally anything that requires more than one fusion monster is probably more difficult to use than the average player would have patience for.
Reign-Beaux, Overlord of Dark World: He's the big daddy of the Dark World archetype (until they got a new big daddy), who gains effects when discarded by effects (but not costs) and gains better effects when discarded by an opponent's effect! This guy, however, needs to be discarded by an opponent's effect in order to do anything, and while there are ways to force an opponent's effect into letting you discard (such as with Dark Deal) they're way too inconsistent to rely on as a main tactic. But if you do manage to discard him by your opponent's effect, you get a monster with a respectable 2500 ATK AND you get to destroy all your opponent's monsters OR their spells and traps! However, Goldd, Wu-Lord of Dark World is only marginally less awesome but summons himself when discarded by your own effect, so he's one of the more practical cards in a Dark World deck.
Even if it has the second highest ATK points of any monster in the game, Machina Force is one of the best examples of Awesome, but Impractical due to its extremely difficult summoning conditions of having Commander Covington on your field and sending Machina Defender, Machina Soldier, and Machina Sniper you control to the graveyard. To make things worse, it cannot declare an attack unless you pay 1000 Life Points. What's more, it's a "NOMI", meaning that if your opponent destroys it, it's gone for good (unless you feel like using something like Monster Reincarnation and repeating the whole process of summoning it again.) This card is even impractical in a deck that uses other Machina, especially since Machina Fortress, a far more practical boss-monster for the archetype, was released.
Sophia, Goddess of Rebirth, from the latest Duel Terminal. It has a heavy summoning cost of requiring a Ritual, Fusion, Synchro, and Xyz monster to be on the field. However, they can be on either side of the field, and can be tributed against the opponent's will a la Lava Golem. Once it's summoned, its effect activates which banishes everything from both players' fields, hands, and graveyards. Neither Sophia's effect nor its summon can be stopped, meaning that if you pull her off, you'll get an instant 3600 shot at your opponent's life points, if not a game win due to their loss of resources (unless they pull a card like Dark Hole or Mirror Force out of their ass, and even then, they likely won't be able to do anything other than that). Difficult to summon, by no means splashable, but in the right deck (and when you know your opponent enough to use his monster selection strengths against him), it can make quite a punch.
Back in the old days of the game, there was Gate Guardian, a 3750 ATK monster who can only be summoned by tributing his three components, each of which require two tributes themselves. The best bit? Gate Guardian's three pieces combined have twice as much ATK as Gate Guardian (not to mention pretty decent effects that Gate Guardian doesn't get), and 3 monsters are harder to get rid of than a single target, so Gate Guardian is impractical even in a deck based around him.
Thenien the Great Sphinx: 6500 ATK on the turn that he's summoned, and 3500 ATK on all the other turns. In order to get him out, you need to summon Andro Sphinx, and Sphinx Teleia. Each requires two simple tributes to get out (unless you control Pyramid of Light - see below - in which case you can Special Summon them via their own effects by paying 500 Life Points apiece, but only from your hand). You then need to somehow destroy BOTH at once in order to summon Thenien. They do make a card for accomplishing this, called Pyramid of Light. When active, it does nothing, but if destroyed, it will destroy all of the Sphinx cards that you control. Problem is, if your opponent somehow destroys it while you have only a single Sphinx out, then you just lost your good monster.
The Spell card Reverse Quiz: To use it, you have to discard your entire hand and get rid of every card you have on the field. Afterwards, you have to guess what the card on the top of your deck is (Monster, Trap, or Spell). If you guess correctly, you get to swap Life Points with your opponent. Whilst this seems like a decent enough payoff, if you fail to guess the card on top of your deck, you're essentially defenseless against your opponent (unless you've got more Life Points than them, which defeats the purpose of playing the card in the first place). If you do guess it correctly, you'll still have to hope that the very next card you pick up is a decent card which can defend you from your opponent, since they'll essentially get a free shot at you after you activate Reverse Quiz. If it isn't a decent card, then by the time you can get a decent enough defense up, your opponent will probably have whittled your Life Points down to what they were when you played the card. In short, it is virtually impossible to play Reverse Quiz and get out of a situation which only it could have solved/improved.
The Wicked Eraser: First of all, he can't be Special Summoned, which means if he's sent to the Graveyard (which is quite likely considering almost all of his effects occur when he's sent to the Graveyard), he's done. (Unless you plan to use Monster Reincarnation, or something like that.) On top of that, his attack and defense are equal to the number of cards your opponent controls, which means if your opponent controls only one monster with 1000 ATK or more, he's done.
Some of the Spirit Monsters from the Legacy of Darkness booster pack and such can be this. An example is the Yamata Dragon, who packs 2600 Attack and upon inflicting battle damage, allows you to draw until you have five cards in your hand. There's also Hino-Kagu-Tsuchi, which has 2800 Attack, and, if it does battle damage to the opponent, they have to discard their entire hand during their next draw phase before they draw. The problem is that both of them take two tributes. Now let's look at the main drawbacks of Spirit Monsters. First, they can't be Special Summoned in any way, so it's usually not worth it unless you build a deck around them. Second, they return to your hand at the end of the turn, so it can mean trouble if they're the only things you have on the field. There is the Spiritual Energy Settle Machine, which allows them to stay on the field, but you have to discard one card from your hand each turn to keep it on the field. And if that leaves the field at any time, the spirit monsters return to the hands, so it's not the easiest thing to maintain.
Ultimate Obedient Fiend: 3500 attack and 3000 defense...but in order for it to attack it has to be the only card on your side of the field, and you can't have any other cards in your hand. Likewise, any low level monster with 2000 or more attack but with a detrimental effect (Dark Elf, Boar Soldier, Nuvia The Wicked, Flash Assailant, Zombrya The Dark etc). Though these can all be made more practical with Skill Drain, which negates all monster effects, thus giving you a whole team of beatsticks with no drawbacks.
The Counter Trap card Judgment of Anubis has a cool name and an awesome effect: you can negate a card, destroy a monster, and inflict damage equal to its attack points all at once. However, the negated card in question has to be very specific: a Spell card that destroys your Spell/Trap cards. In practice, it becomes essentially an anti-Heavy Storm/Mystical Space Typhoon card similar to cards like White Hole which are powerless against anything else and thus are most likely to just be a dead draw.
Big Shield Gardna has 2600 defense, and it requires no tribute to summon. The catch? At the end of the damage step of a battle where this card is attacked, it turns to attack mode, so that you lose life points almost as if the attacking monster had just attacked your life points directly.
Number 88: Gimmick Puppet of Leo is an Xyz monster that has an effect that can allow you to win the duel within 3 turns of it being on the field. However, it requires you to have no spells or traps on the field to use its effect and keeps you from conducting your battle phase that turn if you do. This is extremely dangerous against any skilled opponents since it allows them plenty of time to get the right card to stop it before you get that far and requires you to be very cautious about what spell/trap cards you set/activate lest you give up the ability to use its effect. And given it takes 3 Level 8 monsters to be on the field to summon it in the first place (Monsters that usually have very high stats), you'd most likely have a better chance of winning by just attacking with them and not having to be careful with placing or using traps/spells.
Some of the Archfiend monsters, particularly the ones introduced in Dark Crisis, can be considered this. On one hand, they have a side effect that allows you to roll a die in response to your opponent's cards that target it, and if it ends up a certain number, you can negate the effect completely and destroy it. However, it comes at a rather large turnoff of a cost: they force you to pay between 500 and 900 Life Points (depending on the monster) during each of your Standby Phases whether you want to or not, unless you have less than the required amount (in which case the monster is destroyed), even if Skill Drain is active. You can remove this effect using the field card Pandemonium, however.
The Trap Card Fiend Comedian is this trope in spades. You have to toss a coin and call it, and if you call it right, your opponent's entire graveyard goes bye-bye immediately, which would make it an effective chain against them if they were trying to revive a monster or such. But if you call it wrong? You're forced to send cards from the top of your deck equal to the amount of cards in your opponent's graveyard into your graveyard. That is essentially make it or break it right there, because if you don't have a big deck, and they have a big graveyard, it makes the odds of you decking out much higher, so if you call it wrong that can be detrimental. However, this is somewhat compounded in the fact that you can't even use it if your deck is smaller than their graveyard, but if it's close between them it's still a big gamble.
Rocket Arrow Express has 5000 attack (one of only 5 monsters that do) and just requires your field to be empty to special summon it from your hand. The problem is that it's effect makes it so you can't conduct your battle phase the turn it's summoned, you can't activate any card effects or set anything while it's on the field, and it destroys itself unless you discard your entire hand each of your standby phases. All of this adds up to a monster that has extremely high attack but makes you into a sitting duck for your opponent when you summon it (unless the opponent happens to have a Skill drain on the field...).
In the anime, Sugoroku Muto's Ancient Dragon. It's a pretty powerful Monster, make no mistake; it's got 2800 ATK, it revives itself as long as you've got Ancient City out, and it has a proto-version of Red Demons Dragon's effect (only better, since it only destroys your opponent's monsters). However, it easily beats Perfectly Ultimate Great Moth in the "friggin' impossible to Summon" category - at least PUGM requires only two cards to summon. Ancient Dragon requires seven, and an insanely complicated procedure. First, you need to activate Ancient City, a Field Spell that is needed for the other cards. Then, you need to activate Ancient Tome, a card that would be sort of useful if not for the discard effect. Then, you need to summon Ancient Giant, a roundly mediocre Level 5 Monster with 2200 ATK and no effect other than damaging you when you don't attack with it. Then you need to activate Ancient Key, which Summons two Stone Giant Tokens, which could lose a fight to a Basic Insect and have the same effect as Ancient Giant (only slightly worse - Ancient Giant does 300, Stone Giant Tokens do 500 each). Then you need to switch those Tokens to DEF (and yes, that does mean you take damage from their effects). Then, you need to switch them back to ATK, which activates the effect of Ancient Key, destroying the Tokens and activating Ancient Gate. Only then, you can use Ancient Gate to discard Giant and Tome to summon the dragon. This was, of course, intentional; Sugoroku considered being able to Summon one an impressive accomplishment and did it mostly to settle a bet.
On the subject of very effective Monsters with annoyingly-difficult Summoning conditions, meet Fusioh Richie(or Nosferatu Lich if you're not dumb). Richie has a pretty impressive set of stats, he's immune to targeting effects, and he can Summon a Zombie once per turn. He was also available in Pharaonic Guardian, a very early set, at a time when Zombies were a very popular Deck type. This obviously led to a massive surge of Decks with him as the ace, right? Well, not so much. To Summon Richie, you need to play Great Dezard, a one-Tribute Spellcaster with 1900 ATK, and then use Great Dezard to destroy at least two opponent's Monsters. Only then can you Tribute Dezard to summon Richie. Even in the days when Summoned Skull was considered a game-winning card, this was way, way too slow, and in the modern day, it's gone from slow to glacial.
Muka Muka was a 600 ATK Level 2 Monster that gained 300 ATK and DEF for every card in your hand. This sounds like a good deal, since if you draw him on the first turn, you can get a Level 2 Monster with 2100 ATK... and his big brother, Enraged Muka Muka, costs a tribute but starts with 1200 and gains 400 for each, which would give him 3200 ATK in the same situation. So what's the problem? Well, he's completely useless in the mid-game, because by then both players probably have only one or two cards in their hand. Even if you draw him early, it's a better strategy to just play the cards in your hand instead of letting them sit there to power up Muka Muka. There are Decks that try to focus on drawing tons of cards to inflate Muka's strength, but let's be honest - if you have a whole Deck focused on drawing cards, it's a better idea to just run Exodia.
LV Monsters zigzag this. Their concept (protect a weak Monster and then Tribute it to summon an upgraded form) sounds like it'd be rife with this, but in practice, they vary quite a bit, with many being Awesome But Practical and a few even being Boring, but Practical. And then there's Dark Lucius and Allure Queen, who are... this trope. To level up Dark Lucius, you need to use his 1000-ATK LV 4 form to destroy an opponent's monster, then bring out his 1700-ATK LV 6 form and negate an opponent's monster's effect that activates when it's destroyed, and only then can you summon the respectable LV 8 form. You are probably asking, "What if I try to start from the LV 6 form?" Then you're screwed; Lucius's higher forms only get their effects if summoned by a lower form, so starting from the LV 6 form leaves you with a weak card with no effect. (And no, they don't get their effects if you use Level Up, either.) You're probably also asking, "What if my opponent doesn't have a Monster with an effect that activates when destroyed?" Then you're screwed, because LV 6 can't activate its effect and level up. And Lucius is the stronger one. Allure Queen has the same drawback of its cards losing their effects if not Summoned by a lesser form's effect, but they add in the problem that they rely on the opponent having the right Monsters on the field. Furthermore, their effect (equip an opponent's monster onto themselves) is pretty much a watered-down version of Relinquished, a much older and easier-to-play card. Allure Queen LV 7 has only 1500 ATK and no way to raise it, which makes it a sitting duck. At least if you couldn't jump through all the hoops, you could use Lucius LV 8 as an effect-less beatstick, but attempting to Normal Summon Allure Queen LV 7 is about as dumb as strategies get.
To Summon Montage Dragon, you discard three Monsters. The awesome? Montage Dragon's ATK is equal to their combined levels x300, which can easily lead to a card strong enough to One-Hit Kill the opponent; he maxes out at 10800. The impractical? Discarding three monsters sets you back heavily in card advantage (playing it on the first turn involves dropping two-thirds of your hand), Montage Dragon is a complete dead draw if you don't have three other monsters in your hand, and Montage Dragon has no protective effects, making it a massive clay pigeon for every Trap under the sun. Overall, it will either win you the Duel in one turn, or die the second it hits the field and leave you with no options... and if your opponent is even remotely competent, it'll be the latter.
It was practically a tradition for a while to feature a high-ATK (usually 2000) Level 4 Monster with some kind of drawback: Panther Warrior, Unfriendly Amazon, Flash Assailant, no less than two of the above Archfiends, Nuvia the Wicked, Dark Elf, Cave Dragon, and that's just a sample. The practice started to die down around the release of 2000-ATK Normal Monster Gene-Warped Warwolf, but even before then, players had largely refused to bite. Put simply, most cards with the aforementioned drawbacks suffered such severe drawbacks that you needed to restructure your strategy to accommodate them, which flew in the face of Boring, but Practical Beatdown strategy: sure, you could set your whole hand and Flash Assailant would be good, sure, you could play Scapegoat and Panther Warrior would be acceptable... or you could just play Gemini Elf, which has only 100 less ATK. The only ones to ever catch on were Terrorking Archfiend (which had some useful effects and was actually very easy to use in the right Deck), Berserk Gorilla (only weakness was being locked into Attack! Attack! Attack!, which is what you were doing anyway), and Goblin Attack Force and Chainsaw Insect (whose detrimental effects didn't lessen their ability to wallop an opponent any). The fact that Beatdown in general has fallen heavily out of favor means that even Gene-Warped Warwolf is pretty much meaningless today, so what chance has a loser like Boar Soldier got?