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?


photo, date

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?


mysql_query("DROP TABLE IF exists media");
$vals='sbid int NOT NULL AUTO_INCREMENT,
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);
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.

  • Your question in its current form is way too broad. First, you didn't mention what exactly is supposed to be stored in DB - a blob or a link (and it does matter). Second, you didn't describe a single bit of how you intend to use this data (it terms of reading AND updating). Still, I'd suggest checking this part of MySQL docs in any case.
    – raina77ow
    Nov 24, 2012 at 14:45
  • I wold go for 5.5 MySQL. Faster better options for partitioning.
    – E_p
    Nov 24, 2012 at 14:49
  • 1
    lbennet , Read this post , I guess will help stackoverflow.com/questions/3479720/…
    – Ahmad Samilo
    Nov 24, 2012 at 14:51
  • Don't use the old mysql_* extension! Use PDO or MySQLi!
    – ComFreek
    Nov 24, 2012 at 14:56
  • 2
    The answer is to not worry about it until it becomes a problem, because it is virtually guaranteed not to become a problem. Your site won't be 2-billion-rows-per-table popular, so planning for that level of traffic is wasting time and resources. This is like planning for being struck by lightning. Plus, you'd be surprised at how performant a properly indexed database can be, even with extremely large tables. Worry about building your site properly so you have a solid maintainable codebase. You can scale up your database as needed.
    – meagar
    Nov 24, 2012 at 15:19

1 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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.