I'm guessing that the secret chamber at the bottom of the list says more about the players ranking the cards than the card itself. Not many people seem to play it well. Beggar seems quite strong too, particularly in multiplayer, and Vagrant is considerably stronger than pearl diver.
Vagrant, in a messy kingdom with looting and sans trashing, will be in the same league as Wishing Well. Also, it harmonises with Mystic.
As an aside, if you find a crappy card at the botton of the deck, and you have Mystic in hand, will you move the card to the top?
But in the kingdoms you describe it's usually better to have some money providing card like Silver than a Wishing Well or even a Vagrant. The chances of this finding the type of card it's looking for just isn't ever going to be very high, maybe in a alt-vp rush or slog, but again money is more important in those games than this small cycling effect. I'd rather buy a Duchess than a Vagrant in those games.
And when you often miss like that it's just a bad card doing nothing. The fact that it combos with one other card, Mystic, doesn't add much for me.
Now don't get me wrong, I will most likely buy it whenever I have $2 and an extra buy, because, like Pearl Diver, it rarely hurts to have it in your deck. But that just isn't going to be the case that often. I don't think I'll get it often with $3 or $4 whereas the power $2's if you can call them that I will most definitely sometimes get with $3 or even $4.
Herbalist can provide a critical +Buy and Moat deserves some more attention because not all Dominion games are 2 player.