It's never a waste because it is a FREE +1 to attack. The only time you would not do this is if you want another weakness for whatever reason. It would ALWAYS be worth it because it is a free +1 to attack, no matter your build.
If I want a free +1 attack, I'll take Cruel. That lets me pick another, potentially more useful, trait, and +5 unrest in every city is barely noticeable and easily negated - just get a Commander specialized in Administration up enough to get Administration III and the malus is gone, or just ignore the penalty. If I want to get free attack bonuses, I'll take Might and Clumsy, because the penalty from Clumsy is so easily evaded that it almost isn't a weakness, and +3 attack greatly outweighs the chance of hitting your own units in melee, especially if you go for an archer build, in which case Clumsy can never trigger (unless you for some reason decide to equip your archer with a melee weapon), or I'll take Might and Cruel, for the same reasons as I'd take Cruel for +1 attack given above.
Beyond that, I'd rather take something like Brilliant or Tactician over Might, because my preference in champions is more Field Commanders and Mages than Warriors. Even when I go in for warrior-type champions, most of the damage is coming from trained troops anyways, since there are more of them and I'm not as concerned about losing them.
The point is, though, that if you're going to min-max for something, you pick what you're trying to maximize. If you want to make a great warrior-type sovereign, then there are better ways to go than Weak + Might. If you want a great Commander sovereign, then there are better traits to take than Might, and you probably want at least Air I, and possibly Water I, for Haste and Slow, which are great support spells with very low casting costs, and most enemies don't have enough spell resistance for it to be too difficult to get Slow to stick even with a relatively low-level commander. If you want a great mage, then Might has no real use for you except possibly in the early game, when you probably will not have the mana income for much spellcasting, and this stage doesn't last long enough that I'd consider it worthwhile to spend a weakness to gain a trait to get a net of +1 attack. If I'm that desperate for additional early attack, I'll either take Cruel and pick something that more directly benefits me as a caster (like Brilliant, Attunement, or Scholar) or I'll go for Might and a weakness that doesn't kill most of the benefit of taking Might. Moreover, magic tends to provide ways to get around unrest penalties (unrest reduction from Death and maybe Life, bonus production from Earth, bonus income from Air (allowing lower tax levels or fewer cities for similar income), bonus research from Water, better troops from just about any of them (allowing fewer troops, which permits you to spend less time on training), better troop conservation from Life (also allowing you to spend less time on training)).
In the end, though, the only time that +1 attack on my Sovereign is going to be helpful to me is right at the beginning of the game, when I don't have the mana production to support lots of spellcasting and don't have the economy to support large armies (or rapidly replace loses). After that part of the game, I should have large enough armies that my champions are kind of insignificant, I should have enough mana income to cast at least one or two good spells per battle, I should have found a +1 attack book or a better weapon, or something else that makes +1 attack on the sovereign insignificant. If you want to min-max for an easy early game, there's better ways to do it (especially since as long as it's in the early game, your armies are small enough that working around the penalty from Clumsy is trivially easy, and +3 Attack from Might and Clumsy is far better than +1 Attack from Might and Weak - early game, you're usually looking at no more than 10 defense on enemies and about 10 attack on your sovereign, so +1 attack is roughly a 10% damage boost while +3 attack is roughly a 40% damage boost), and if you want to min-max for an easy late game, then +1 attack is almost worthless.