The problem with saying a type of unit is better than another is it depends on the circumstances.
When using a overall defensive stance (not aiming for conquest) i tend to find cavalry units are much better as they can can generally be anywhere in your empire in 1-2 turns to react to any issues that may arise. Also when counterattacking, as i generally don't wish to take enemy cities but to exploit and weaken them instead they serve well at hit and run and pillaging large areas thus i tend to build proportionally more cavalry units when playing peaceful games.
Conversely when aiming for an aggressive game i find them less useful as i am generally dictating where the battles occur so can ensure my units are prepositioned and don't generally want to do much pillaging as i will then have to repair those tiles myself after capturing the city although i still tend to have a couple around to deal with errant units wandering around from the main conflict area or to run around the back of cities i am attacking to put them under siege so they don't heal.
When using a overall defensive stance (not aiming for conquest) i tend to find cavalry units are much better as they can can generally be anywhere in your empire in 1-2 turns to react to any issues that may arise. Also when counterattacking, as i generally don't wish to take enemy cities but to exploit and weaken them instead they serve well at hit and run and pillaging large areas thus i tend to build proportionally more cavalry units when playing peaceful games.
Conversely when aiming for an aggressive game i find them less useful as i am generally dictating where the battles occur so can ensure my units are prepositioned and don't generally want to do much pillaging as i will then have to repair those tiles myself after capturing the city although i still tend to have a couple around to deal with errant units wandering around from the main conflict area or to run around the back of cities i am attacking to put them under siege so they don't heal.