Too many records on database and how to handle it? MySQL is it good to clone?

Question

I am doing a social network and came up with the following concern regarding database storage. Let's put facebook as an example, imagine 2,000 photos per user, just 1,000 users, it would equal to 2,000,000 rows in total assuming there is a row per photo.

Now imagine 100,000 users it would be two billion rows (in a single table?)

The problem is how to handle this? With clones of the same table and then selecting by user id which is assigned to one sector?

suppose

photos_0
photo, date

photos_1
photo, date

and so on, then storing the suffix, 0, 1, etc along the user id and working from that point.

Is there any other better known solution?

//media

mysql_query("DROP TABLE IF exists media");
$vals='sbid int NOT NULL AUTO_INCREMENT,
PRIMARY KEY(sbid),
vids varchar(200),
vidshd varchar(200),
vidso varchar(200),
duration varchar(200),
videow varchar(200),
videoh varchar(200),
notify varchar(200),
pics varchar(200),
picss varchar(200),
picsm varchar(200),
picsr varchar(200),
picsa varchar(200),
faces longtext,
descr longtext,
location varchar(200),
sbidv varchar(200),
id varchar(200),
albumid varchar(200),
albumn varchar(200),
norder int(200),
oldorder int(200),
title varchar(200),
shot varchar(200),
shott varchar(200),
nye varchar(200),
visibility varchar(200),
datetimep int(200),
datetimep_pp int(200)';
$vals=str_replace("
"," ",$vals);
$predefined='';
mysql_query("CREATE TABLE media ($vals) AUTO_INCREMENT=10000000;");

The above is the sample table, would I go with storing up to billions of records on it, then updating and selecting from it normally where id='$uid' and $uid=theuser's id.

It is not the final table structure but I think it wouldn't be possible to use one table only so I thought about cloning and then when altering a table the need to alter all of the tables becomes necessary which sounds difficult to have to alter hundreds or thousands of tables using lots of queries whereas if it were one table only then it would mean a single query, that is one of the concerns I have about cloning but I also don't know if it is the actual solution to such eventual problem.

Answer 1

Partitioning and proper indexing plus do not forget proper multi server setup. Start from MySQL documentation on indexing and partitioning. When you hit such db size you should be able to afford somebody who knows what to do.

For big datasets you might want to look in to other solutions like Hadoop. Do not forget caching Redis/memcached.

Based of my experience with big data sets. Your table has too many columns, does not have proper indexing. So in case you do proper indexing and partitioning on production(properly setup) speed on indexed, partitioned columns would start degrade after 40,000,000-50,000,000 per partition.