I've actually been playing these guys all weekend trying to find a use for the Comanche Rider. Unfortunately, I didn't make a single Rider yet, let alone find a good use for them. I'm one of the people that suggested Comanches for the UU but they feel out of place here.
People seem to find little use in general for mounted/fast units as when it comes to actual combat they are a bit middle of the road...if you push them into a fight they will likely die next turn, if you hold them on the defensive they get no defensive bonus.
Fast/mounted units aren't meant to stand and fight they are meant to move around fast mopping up and harassing.
In an actual fight you can use them for flanking promotions and trapping units so they can't run off or to run and finish off a unit with a couple of hp left then run back to safety if needs be.
Once you have 3 range on your ranged units they are perfect for city attacks, they can move in to sight of the city so you can see the city and your ranged units can hit and move out to safety even in rough terrain. Once the city is down to no health they can also run in and take the city without having them hanging around for a turn or 2 getting shot up, most important when there is a ranged unit camped in the city on defence also and even more so if there is another ranged unit hanging around which can easily kill off even a melee unit in rough terrain.
Comanche riders with their extra movement point can make the difference between city raiding or not being able to. In my current game i was attacking a runaway on the other continent and was able to raid his inland cities by bombarding from the sea and running in my Comanche riders to take the city, in most cases having to run over hills and forests to do so would have fallen short with standard cavalry and due to the vastly superior land forces the enemy had they would have kill my units before i got to the city. I could land out of their borders and therefore out of LoS and therefore run in unseen rather than having to land and get hit on landing or land, move into range to strike and get hit that turn.
They are also excellent raiders in general especially with the new warmonger penalty system if is often inadvisable to take every city or even take it and raze it so you often want to only take a small number of cities or liberate some which means you either leave them with some decent cities intact or hand decent cities over to their original owners.
With fast/mounted units you can quickly and easily pillage all the tiles surrounding cities you either leave or liberate so they at least have a lot of work to do to get it back into a decent condition which in the case of cities you simply don't take should be made a lot harder as you should also be capturing all their workers with your fast/mounted units while pillaging all their tiles.