There could be tons of choices from Metallica: Master of Puppets, Battery, Sanitarium, Blackened, Moth into flame, Unforgiven, Wherever I may roam and so on and so on. The band is THE definition of cool intros.
Definitely have to go with Cherub Rock by the Smashing Pumpkins, such a mega huge build up that leads directly into a rad fucking riff
Paint it Black by the stones.
Sweet child of mine by Guns n Roses.
Took way too long for someone to mention Sweet Child of Mine.
crazy train
then it falls apart fast
Seeing as Metal-GAF isn't represented here, allow me to point out the most obvious:
Ye Entrancemperium by Emperor
Flowing directly from the intro preceding it, the intro riff of this song grabs you by the throat, mops the floor with you and beats you immediately into submission. One of the all-time greatest metal masterpieces and I daresay, one of the best riffs ever. There's absolutely zero slack and general tomfoolery present.