My dd had just turned 7 when we saw it and it really scared her. I had to carry her and shield her eyes, and this kid is not easily scared. The darkness, loud noises, and fire were just too much for her. But, her 5 yr old brother loved it!
My 6 year old boy loved it back in 2003. He is usuall quite timid about scary attractions, but after it was done he said it was pretty cool and we did it again a couple of days later. He was a little nervous when it got dark, but for the most part he loved it.
my 7 year old loved it and he is scared of his own shadow. he got close to me when the sounds got really loud but loved the effects and walking thru the water tunnel. we also had a really good ride host who made several references to famous kids movies during her speech and it kept him laughing. if we didn't have to wait so long, we would have gone again. i would say unless you think it will scare your kid i would definitely commit to it once and see for yourself.
My six year old DS loved it, and he is not the bravest little guy out there. I don't think it is especially scary, but it is a bit "cheesy" IMHO and you may want to skip it if you're pressed for time. A lot of the quality depends on how good your "guide" is.
The cheesiness of it is thanks to Universal "fixing" something that wasn't broken. It used to be a great show. Now it's incredibly stupid. They re-did the entire thing a few years ago. I've never seen someone go through so much effort to actually make a product so much worse.