My DD (5 at the time) was a little frightened on this ride. It was the only thing on the whole trip that bothered her. She didn't like the Indiana Jones part b/c she truly believed that our guide had gotten burned alive. Also, there is a scene with the alien hanging from the ceiling that can be a little scary.
As far as other scary rides...it just depends on the child. Are they afraid of heights, the dark, going fast, etc...