Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question

migrated from stackoverflow.com Nov 24 '12 at 21:32

This question came from our site for professional and enthusiast programmers.

    
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 '12 at 14:45
    
I wold go for 5.5 MySQL. Faster better options for partitioning. –  E_p Nov 24 '12 at 14:49
    
lbennet , Read this post , I guess will help stackoverflow.com/questions/3479720/… –  Ahmad Samilo Nov 24 '12 at 14:51
    
Don't use the old mysql_* extension! Use PDO or MySQLi! –  ComFreek Nov 24 '12 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 '12 at 15:19

1 Answer 1

up vote 1 down vote accepted

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.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.