To me the most memorable introductions was Discotheque on Popmart Tour. If you can call that an intro, that is. But I remember this as jawdropping, when the giant lemon began to spin, then moments later becoming a giant crystal ball. And when it opened with the whole stage filled by smoke... Edge stepped out dressed all in white followed by the others down the ladder to the B-stage. To me as a 23 year old on that time, time nearly stood still.
The worst for me is when the intros last more than a minute and becoming a speech. It is rather boring...