MySQL is famously a hodge-podge of several languages. But large parts of the original are/were in ColdFusion.
Facebook is in PHP.
At the levels of traffic that these sites have the language itself is irrelevant.
All the issues are in the database and file storage. Facebook, for example, has something like 500 dedicated memcached servers - these cache data in RAM and mean the site doesn't have to hit the actual database directly for data.
Um, where was I. As a database MySQL is used to store all the data with the exception of files (such as images). So all profile data will be stored in MySQL.
Anyway ...
Building one of these sites is a MASSIVE undertaking. I was involved building a white-label social networking system last year - it took 4 programmers and nearly 6 months of work.
Seriously, the best advice I can give you regarding setting up a "niche" social network is to use an off-the-shelf product rather than build your own. There are so many of these social networks - the hard part is not the software, but the marketing.
A really good commercial option is
phpFoX » Social Networking Script. This is pretty slick has ALL the features you need for your own site. They will host it for you, you can extend it if you need to using PHP.
To get started today, this is my recommendation.
Lovd By Less -- Open Source Social Network -- Who loves you, baby? is an open-source ruby on Rails system. Ruby on Rails is my development platform of choice - I am easily 20-30% more productive in Rails and I charge my clients accordingly. This is not as feature complete as PHPFox, but if there are things that your niche needs that will require custom dev, I would suggest looking at Rails.
What I have been suggesting to most of my clients (everyone wants a niche network these days) is build a Facebook and/or MySpace/Open Social Application that provides features to this niche but leverages the existing community.