In my opinion, the best Earth magic: slow, resurrection, raise dead, portal to town, explosion (level 5). Air is second best: haste, various lightning spells, flight, gate.

I completely agree. Once you learn the town portal, all your castles are protected, and you can concentrate your troops around your main hero. I upgraded the explosion to 8-9 thousand damage, which I think is very good. Slow + archers, and the battle is won. And finally, resurrection, both for the dead and the living, allows for battles without losses. And for those who like mass attacks, there's the meteor shower. What else do you need?
Air is also good. Chain lightning (I like to use it, especially with Solmyr) hits hard, but it's not always applicable due to the chain affecting 4-5 units. Haste is straightforward. I rarely use flight and the portal, so I don't value them much, although they are indispensable in the mountains.
Fire is primarily about blindness and berserk. Effective, but limited.
Water – I don't like anything about it. Except maybe prayer.