Yes i know asking for the best one is a difficult question because you don't know what i like and such, which is why i also asked for the most popular.

http://www.tesnexus.com/downloads/file.php?id=12466 which keeps a more vanilla feel to the game's magic while adding a significant number of new spells and tweaking several of the default ones. Also re-balances the magic system to make it less exploitable.

http://theelderscrolls.info/?go=dlfile&fileid=219 a very aggressive re-balancing of the magic system with an emphasis on long-lasting passive spells. Also includes plenty of new spells and a number of interesting goodies for anyone who uses the sister mod, Race Balancing Project.

You can even use the two together. Simply follow the instructions for doing so, and load whichever mod you want the spell balancing to take priority from after the other.

Thanks i'll look into those.

http://www.tesnexus.com/downloads/file.php?id=30973 is also highly recommended as well as SM and/or LAME. Unlike Midas Magic it also adds the spells to caster NPCs in the game, making things much much more interesting.

The spell system (that is only for the FM spells) is also unique and make these powerful spells to be used tactically, sort of like powers. Rather than with Midas Magic, where you can spam them if you have the magicka for them.

Always a good starting point: http://www.gamesas.com/index.php?/topic/814477-magic-overhaul-comparison/

There are a few newer and more specialized magic compilations (new spells & abilities) but that covers the actual overhauls.

http://www.tesnexus.com/downloads/file.php?id=33114 is probably the best thought-out magic overhaul but it doesn't stop being just a magic overhaul.
Less Annoying Magic Experience makes enchantments overpowered, and magic itself also gains a strong buff but it doesn't make magic itself much more overpowered than in vanilla Oblivion.
Supreme Magicka keeps enchantments in check but makes magic itself way, way too powerful.
I haven't tested Mighty Magick: Reborn yet but given the fact that it is viewed as 'Strong Powerlevel' while both latter mods are viewed as 'Medium Powerlevel' I assume it removes any challenge for mages...

If you want to play a magic user and the game should stay challenging use ROM, if you don't plan to use magic yourself but want to increase the difficulty use one of the latter mods.

Difficulty is extremely subjective and depends entirely on your use of other mods. Overhauls and quest mods can range in various difficulty as well as other combat tweaking mods that make melee and archery more powerful.

I find SM and Fearsome Magicka to be perfectly in balance with Duke Patrick's Melee Combat and Combat Archery, so everything is equally deadly. Then use a overhaul like FCOM or OWC-ND+WAC or TIE+WAC which all have varying levels of difficulty and if you still don't find it to suit your taste, adjust the difficulty slider.

I agree that LAME is overpowered for one reason, the Dremora summons are too powerful and too easy to get especially when coupled with the SM increased conjuration casting limit, 3 of those can demolish anything - you just have to run around and stay alive. I like SM just by itself.

Also I used ROM for a while and I love the magic changes in it. But it was very buggy with leveling when I played it thankfully with the latest ROMu it's very playable. The Conjuration changes are great but I just wish it was more modular, it does so much in one but I would like to use it's gameplay changes for example with FCOM on it's own.

From what you're saying, is it safe to assume that I can add this mod to modify NPC caster? My current character is not a mage but the ones I face feel so boring I'd like to see them surprise me.

Yep, all NPC casters will use all the spells depending on their major skills. You can turn on and off which spells they can use in the FM.ini (note: I would recommend turning OFF Raise Undead, it has a known issue).

Sweet, I'll be sure to check it out. Thanks for the tip man!