Magic is supposed to be evocative and wondrous, but often it’s as thrilling as using a paperclip.
Neat, clean “fire and forget” spells are b-o-r-i-n-g. They’re a tool, just like a flashlight, and about as involving.
In some systems spells have drawbacks — penalties attached to their use. But ad hoc, arbitrary consequences attached per-spell, with no consideration for cosmology or worldbuilding, are also boring. They are meaningless.
A great magic system has a cost, a price that must be paid, and it’s a price that ties into the cosmology of the setting. The setting is reflected in the magic, and by learning of the magic, you learn more about the setting.
One great example is Call of Cthulhu’s Insanity. Now, “Lose 1d4 SAN” isn’t interesting all by itself — that’s just a dry mechanical description. (But even that is more thrilling than some systems.)
But the consequences of actual insanity — becoming paranoid, depressive, manic, whatever — is interesting. The notion that magic warps your mind, makes you slightly insane at first, then more and more insane as you use it more, is compelling, especially in terms of role-playing a character. How do you play a man who is losing his mind? And the mind-warping power of magic ties into the nature of existence in CoC: human minds cannot grasp the truths of eality, and trying to do so drives you insane.
Magic then becomes not an add-on rules set, but an integral part of worldbuilding. Done properly, it enhances everything else in a setting. (As is the case with both Shadowrun and Earthdawn.)
Magic should always have a price (and not just because of game balance). Whether it’s a sacrifice involved in acquiring the power (losing an eye and hanging on a tree for ten days), learning the power (going insane), or utilizing it (blasting the vegetation around you to dust), there should be some cost involved.
Ideally, the price should be colorful, present some roleplaying opportunities and challenges, and be tied into the cosmology of the setting. This “price” is what makes magic involving.
Magic, how you use magic, and what magic you use should be choices with consequences. Choices are the core of roleplaying, and when magic is fraught with uncertainty, those choices are more meaningful.