No not really.
But the Secret Key is needed to unlock the door to the next Gym and progress in the Game.
You can't catch Mewtwo or Mew there, infact without hacking it is impossible to even get Mew in Leaf Green at all. Mewtwo on the other hand can be found in Cerulean Cave after you defeat the Elite 4 and Champion.
The Pokémon Mansion can be useful though especially if your wanting to complete the Pokédex as a few Pokémon can only be found in there. TM22 Solar Beam and TM14 Blizzard can also be found there along with Rare Candies and other items.
For more information about the Pokémon Mansion check out this link: