Lately though I've been playing Civs randomly in a so-far vain attempt to gain the exalted heights of Demigod. I've found the Immortal to be superior 'cuz of its increased attack: Basically it's a Medieval Infantry buttwhomper in Ancient Times. And if an experienced player like Ision rates the Mounted Warrior even higher due to its mobility, that begs the question: How in heck did you win consistent dominations on Emperor with a "second tier" (according to him and Arathorn) UU?
In my experience, the value of a certain UU also depends on difficulty level and map characteristics. For example:
The Roman Legion is a Swordsman with +1 defense. If the AI never attacks you, the extra defense doesn't give you any benefit, so the Legion is no better than an ordinary Swordsman. But if you are up against heavy counter-attacks from the AI, the extra defense is invaluable! That means: on lower difficulty levels, where the AI usually does not have the resources for dangerous counter attacks, the Roman Legion is a lousy UU. After the first initial skirmish, the AI usually has shot its wad and all that remains is to march through their remaining cities as fast as possible. The Mounted Warrior can get this task done twice as fast as the slow Legions (especially if the AI didn't even manage to build a halfway decent road-network in its territory...) and the -2 defense compared to the Legion doesn't matter, because the MW are never attacked anyway. But they have a higher survival rate when attacking Spearmen because of the automatic retreat when losing.
On Demigod or higher, on the other hand, the Legion is great, because you would risk very high losses when sending an unprotected MW stack into the territory of a well-developed AI... So without a unit like the Legion you would have to employ "combined arms" techniques, which needs much more shields, because you need different units for attacking and for defending.
(Of course, with certain map characteristics, a Mounted Warrior stack can be as deadly on Deity as it is on Warlord, for example:
a) If the AI does not have horses, it may be possible to lead your MW stack in such a way, that it always "evades" the counter-attacks of the slow-moving enemy units. For this it is good, if there is a neutral third party, which has a RoP with you but not with the enemy: you just gift captured towns to that third party, your own units can quickly move through that territory, while your enemy's units get stuck: they lose one turn to move next to your town, then you gift it to the third party and then the enemy units lose another turn for getting out of the neutral territory again...
So by the time the enemy stack is back to where it came from, your MW stack can already be 12 tiles away...
b) If you can advance in the cover of mountains or hills, 2-3 Spearmen will be enough as protection for the MWs.)
There is, however, one other reason, why I prefer the Mounted Warrior even on Demigod - Sid: it can be upgraded all the way to Cavalry! On high difficulty levels you cannot expect to win the game already in the ancient age, especially on continents: the far away AIs will have developed Musketmen or even Riflemen by the time you reach them. So you will need Cavalry anyway for a save victory. If your first wave started out with Roman Legions, you will now have to produce a new army from scratch and march it from your core all the way to the current front, which may cost 10 turns or so. I remember one Deity COTM where we played the Iroquois, and where I was able to keep rolling from start to end: I started my attack on the nearby neighbors with MWs and kept researching towards Military Tradition. By the time I reached the "second ring neighbors", they had Pikemen, but I was able to trade Invention for Chivalry (or something like that), just upgraded my MWs to Knights directly on the front and kept going. By the time I reached the far away nations, they had musketmen, but I had finished Military Tradition and just upgraded my army to Cavalry, again directly at the current front line, and next turn I was able to press on and reach Domination.
In my opinion this makes the Mounted Warrior the best UU there is.