Sword armed cavalry is actually quite rare.

What you have instead are:

Carbine-armed cavalry Lance-armed cavalry Pistol-armed cavalry

All of whom typically carried a sword as a sidearm.

When performing duties as scouts, a lance is unwieldy and has large visual signature. It impairs the cavalryman's ability to fight dismounted effectively, and is useless for broken-country skirmishing.

Mass charges in a set piece battle are a relatively insignificant element of your average cavalryman's career.

Taking a look at British cavalry in particular, nearly all their cavalry units had a Dragoon pedigree and carried carbines by the 19th century. Lancer regiments were added in 1816, but only a relatively small number. More were used in India. But even the 9th Lancers carried carbines in action in Afghanistan. Neither the terrain nor the tactics of the Afghans made for good opportunities to charge with lances. The 17th Lancers participated in the charge of the Light Brigade in 1854, and did not execute another charge until the battle of Ulundi in 1879. And in the latter battle, Chelmsford did not let any of his cavalry out of the square until the battle was won - this charge was against broken units fleeing the battlefield.

https://www.nam.ac.uk/explore/cavalry-roles

https://the-past.com/feature/regiment-the-17th-lancers-at-ulundi-4-july-1879/

American cavalry never got into lances because they were always intended to fight dismounted and emphasized pistols for mounted combat. Even after being impressed by Mexican lancers during the Mexican War, the US response was to write a few pages in drill manuals about how to make sure to disrupt Lancer formation with fire from dismounted troopers.

https://markerhunter.wordpress.com/2015/10/05/what-of-the-lance/

This link directly links to one of those manuals.

The lance had it's enthusiasts who advocated for its widespread adoption, but Anglophone military professionals did not tend to agree.

I leave the explanation for French use of the lance to someone who can come with a better explanation than suggesting they were overly romanticizing the performance of Lancers in the Napoleonic Wars, because that's the only theory I know. But undoubtedly there is French literature I can't read.