First of all, at least in Greece and Rome, while the Republican Roman equites and the pre-Macedonian Greek hippeis were generally not regarded as being as tactically important as infantry, individually cavalrymen generally had higher social status (although not necessarily tactical effectiveness) than infantry.

As for why cavalry became so important in Medieval Europe, there are many theories, but one I've read recently is from John France's Western Warfare in the Age of the Crusades: 1000-1300. One argument is that in the European middle ages, land ownership and political authority were rather intertwined, and that land ownership was rather fragmented, with titles to land being acquired through various accidents of marriage, inheritance, conquest, etc. The result was that the domains which a single authority (lord) had to defend were not a single, coherent domain.

Effectively, if you looked at four classical polities, all with tense and potentially hostile relations with each other, a map of their domains might look something like this.

If you looked at the holdings of four Medieval lords, all with tense and potentially hostile relations with each other, a map of their domains might look something like this.

Although in both the classical polity case and the feudal land ownership case, the amount of area needing defense is the same, the structure is rather different. The classical polity needs to defend a single, medium-sized domain. The feudal lord needs to defend an archipelago of small holdings distributed over a large area.

Infantry, with its moderate mobility, is good for defending a compact, medium-sized domain like that of a classical state, but if a Medieval lord wants to defend his holdings, infantry can't move from one detached fief to another quickly enough. The result is that better options for someone facing a feudal lord's problems is to focus on cavalry -- perhaps fast enough to move from one detached fief to the next and cover a large area -- or on fortifications, which completely lack mobility, but which are very effective at defending a small area.

This is one explanation for why cavalry and castles gain dominance in the Middle Ages and infantry generally lost ground, although of course it's not the only explanation.