Fireball isn't meant to be useful in every battle. Use it in bigger battles with a lot of 2-move-per-turn enemy units, and preferrably with units of your own that can surround your casters while they cast. Obviously it isn't meant to be useful against panthers.
I think that the 3-turn wait after casting is fine. It's a damn powerful spell. And there is, as already noted, more than one way to counter it:
Fast-moving units, especially fast, high initiative units
Fire resistance
Archers taking out the caster before cast
Counterspell
Spreading units out to avoid multi-unit kills
And there are ways to make it more effective:
High-defense units to protect caster
Casting haste on caster
Slowing enemy units with various spells
If there are enough units, creating choke-points on the battlefield so the enemy can't spread out
Giving the caster movement bonus so they can retreat father before casting