I believe that any Merlin [or place what you think is the most powerful wizard, mage, or magician of all time here] level mage should be able to wipe out an army of 10,000 (job classes doesn't matter, yes it can even be other mages) easily while only consuming 10HP and 5MP. Yep, they're badass. However, mages should not be like this from the very beginning or even after 20 to 30 hours of gameplay (aka grinding). There should be grinding, but also trials, runes, and artifacts they need to acquire to reach such a level. Repeated use should come into play (more on that in a bit) but also combination of spells (ex: earth and water make mud spells ....(?)).

Also, a mage's power should not be just the level of attack but the speed at which he can execute the attack as well. For instance. Let's say our mage learns a water spell. This water spell consumes 2MP and cause 10HP of damage. Well as most mages do, they must say an incantation and sometimes even the name of the spell. For this example, let's assume the mage has to do both. Eventually the mage will use the water spell enough times to only have to say the name of the spell and not the incantation. Because of this, the mage can now perform the spell in 3 seconds instead of 10. However, if after gaining the ability to skip the incantation, the mage decides to say the incantation anyway, then the water spell consumes only 1MP and causes 15HP of damage. Again after more continued use, the mage will be able to perform the spell without saying the incantation or the name. The spell stills cost 2MP and causes 10HP of damage. Casting the water spell will akin to firing a sem-automatc weapon. However, if the mage even after gaining the ability to perform the spell without the incantation or name choose to say the name anyway, then the spell will consume 1MP and cause 15HP of damage. If the mage decides to say the incantation and name, then the water spell will consume 0MP and cause 25HP of damage. It should be noted that if the mage casts the water spell 5 times (with full incantation), then 2MP will be consumed.

For ease, let's say this:
Water Spell Level 1 = full name and incantation needed to cast spell
Water Spell Level 2 = full name needed to cast spell
Water Spell Level 3 = no name or no incantation needed to cast spell

When a mage reaches Water Spell Level 3, saying the name will give the water spell an extra effect. Such as damaging close surrounding enemies (one meter away from the targeted enemy and targeted enemy is slowed by mud or ice depending on terrain). Saying the name and incantation will cause all enemies to be attacked with the non-targeted enemies receiving 35% damage of the targeted enemy.

As I said before, to reach a Level 3 Mastery of any spell is not going to be a simple task of grinding for 20 to 30 hours, spamming spells. Trials will have to performed (ex: defeating a dragon, killing mages more powerful than yourself), finding runes that teach you new magic or magic combos, and artifacts that enhance your powers. If a mage does not do these things, then they can still be a powerful mage but definitely not invincible and certainly killable. The amount of time it takes to reach this level will certainly be high. Very high. But it would make the mage class not just a support class but frankly a powerhouse. A class to be feared because of its potential. But dreaded because of the amount of time and effort it takes to reach such a level. Therefore, a mage can be hax but there won't be a swarm or over-population of Merlin-level mages running around because of the extreme difficultly of reaching such a level.

In any case, this is my proposal. Now, please, flame-on

I think something like this would only work if mages had permadeath. If not, then no matter how slow and difficult you made the levelling process, you will still end up with a majority of mages in your game.

But yeah, I agree. I've always found it ridiculous that an almost god-like being can be defeated by a highly trained warrior.

A majority of mages is fine. A majority of Merliln-mages (as I described) would be un-fine. Trying to be a Merlin-mage would be hell on earth for the player. Discouraging god-hood if you will.

Look at this from the player's point of view: what is the point of being someone else than a mage (except for roleplaying players that just want to run a tawern and chat with others)?

Possible solution:
Make 100 magic towers for the whole game, each tower can hold 1 wizard and 2 apprentices (so 100 big wizards and 200 smaller wizards total). Only mages that own a tower have access to the powerful magic you described, others are just a standard RPG mages. Mages can duel with other mages to take control over a tower.

I think that the problem of mages being hax starts earlier and can't be avoided by letting them get this power later.

There're two kinds of mages. The first one is the one who is able to do things normal people can't do (think of harry potter). The second one is a god in human form (think of wheel of time, dragonlance, etc.).

When taking a look at the god-mode mages most stories already have trouble of handling these kind of characters. The problem is, you need to continue to increase the power, but once you reach a hi-power level you need to get rid of them, else you loose your conflict (almost no-one is able to beat him) and therefor you loose your story. Often these characters are taken out of the active story or are crippled(aka nerfed) once they get too powerful.

An interesting alternative is to shift super powers, making the "common" hero equally powerful (Marvel & DC Universe). But these are not really powerful characters any longer, because the common human are just "cannon fooder" for the bad guys, they can't really beat any super hero without an other super power (item or character).

Therefor god-like mages in games are only feasable when you cut off the power once it gets too powerful. This happens when a single player game ends, but in a open game context (i.e. MMORPG) this is a real problem.

So, to be honest, the most interesting and feasable mage is the one who is able to do minor things other can't do, but not god-like things. I.e. by igniting some candles, tricking some opponents with a little illusion (only once), being invisilbe for 10 heartbeats etc. From a game perspective these mages are really interesting, because they let you experience a game from an other perspective without delivering some kind of hack by shifting game mechanism into a new direction.