It seems to me that if a group of soldiers on horseback charged deep into the ranks of enemy foot soldiers, the absolute best case scenario is that they cut down a handful of infantrymen before having their horses disabled and being left completely surrounded. My first thought is that this probably NEVER happened in actual medieval warfare, but I wonder why it's so commonly used in film and television. Is there any evidence of this being an effective strategy, or even one that was ever used? or is it just totally a dramatic scene used for effect?
You're overlooking quite a few considerations, in particular morale. Indeed, even in the Napoleonic Wars, when the disparity of capabilities between the infantryman and the cavalryman is much less lopsided than in the Medieval Period, cavalry getting into an infantry formation is still Bad News - for the infantry. I shall refer you to this previous compilation of mine on the dimensions of cavalry; consult the linked threads as well as u/dandan_noodles' comment in that thread. As always, should anyone wish to contribute more on cavalry matters, please don't let this post stop you!