I found an older "Greatest intros in Rock !" thread that was fun to read through.
But, what about the Worst intros.

The only intro (to an otherwise good song) that has made me fire up the audio software to edit it out was the boys choir intro to "You Can't Always Get What You Want" by the Stones.
The song just came up on the music service at work today, and I really can't believe how long that intro seems. It just never stops.