Database question for social network site
Discussion
I'm creating a social network site, it wont be a facebook or myspace, but it will be unique and could easily attract a million registered users.
It's gonna have the usual blog and messages for each user, which brings me to me question... In terms of the database structure, should i use a separate table for each user's blog/messages, or use one table for all blog entries/messages and then query by user when needed?
The one table method will be easy for me to administer, but if the number of users really does start to grow to big numbers, then mysql query would have to work on a huge amount of data and would surely become slower and slower.
The table per user method is a little more work to implement, but still pretty straightforward, and should mean queries will be quicker. The only downside i can think of is that i'll have loads of tables to administer, though i guess once everything works correctly you just let it grow and grow.
Also, can mysql/php cope with large numbers of tables, or is it limited?
It's gonna have the usual blog and messages for each user, which brings me to me question... In terms of the database structure, should i use a separate table for each user's blog/messages, or use one table for all blog entries/messages and then query by user when needed?
The one table method will be easy for me to administer, but if the number of users really does start to grow to big numbers, then mysql query would have to work on a huge amount of data and would surely become slower and slower.
The table per user method is a little more work to implement, but still pretty straightforward, and should mean queries will be quicker. The only downside i can think of is that i'll have loads of tables to administer, though i guess once everything works correctly you just let it grow and grow.
Also, can mysql/php cope with large numbers of tables, or is it limited?
A table per user would be probably faster but I would question whether it is really a managemable solution?
As long as the table is indexed on user_id queries shouldn't be too bad. I would think you'll need somekind of purging/archiving going on so that the 'main' table only contains the most current data with older data archived away into a seperate table so as not to impact on the majority of the queries. This would allow most of the site to be really snappy, with a bit more delay if people start browsing through older stuff - I think most users would accept that anyway.
If you really want to know, I would suggest you do a mock up of both solutions and do some performance testing. I can see MySQL becoming a little unhappy/slow with a million tables. These will all have their overheads, and will all need to be indexed etc. I can see it becoming a nightmare to be honest.
Good luck.
As long as the table is indexed on user_id queries shouldn't be too bad. I would think you'll need somekind of purging/archiving going on so that the 'main' table only contains the most current data with older data archived away into a seperate table so as not to impact on the majority of the queries. This would allow most of the site to be really snappy, with a bit more delay if people start browsing through older stuff - I think most users would accept that anyway.
If you really want to know, I would suggest you do a mock up of both solutions and do some performance testing. I can see MySQL becoming a little unhappy/slow with a million tables. These will all have their overheads, and will all need to be indexed etc. I can see it becoming a nightmare to be honest.
Good luck.
There's quite an interesting (and long) article on how myspace's backend started and evolved here, you might find some pearls of wizdom in there somewhere!
eta, would a table per user make it easier to spread the users out across multiple database machines in the future?
eta, would a table per user make it easier to spread the users out across multiple database machines in the future?
Edited by SneakyNeil on Wednesday 23 May 22:07
jsr said:
Also, can mysql/php cope with large numbers of tables, or is it limited?
Yes it can. However, if you are absolutely pounding the back end MySQL is out the window. As is MS-SQL; Access is to be ignored (which is SOP anyway). DB2 or Oracle. Be prepared to pay through the nose for the support and, if you go with say DB2 + AIX, the kit as well. MySQL has come a long way from being rinky-dink but if you're serious about this, you need a serious DB.
boony said:
As long as the table is indexed on user_id queries shouldn't be too bad. I would think you'll need somekind of purging/archiving going on so that the 'main' table only contains the most current data with older data archived away into a seperate table so as not to impact on the majority of the queries. This would allow most of the site to be really snappy, with a bit more delay if people start browsing through older stuff - I think most users would accept that anyway.
Thanks boony, i hadn't even considered a 'current' and an 'achieve' structure - will definitely give this some thought, it already sounds like the best approach though!Cheers for the link SneakyNeil. Should be quite interesting, and probably a scarey too. I reckon i'll need dedicated serves long before i'll need to worry about how many tables i should use.
Will keep to one table for the time being and archieve the old stuff should the site grow large.
I would'nt rule out MS-SQL server - we're running systems here with 30 million rows of data in some tables, performing joins across them and still getting adequate performance (read that as software asset management reports returning results via web based front ends within 20 seconds).
Anyway to avoid DB OS evangelism issues one way to address potentially very large scaling issues such as you describe is to look to horizontally partition your data. Some RDMS (no names mentioned) can do this as native functionality.
For example one could choose a very simple (and this is just for examples sake not a recommendation for production) horizontal partition scheme based on the lexical distribution of your users. So all the A-C go in one pot and so on and so on.
Such an approach gives you a half way house between the single pot approach or the table per user approach which will likely result in an acceptable compromise between manageability and performance.
Anyway to avoid DB OS evangelism issues one way to address potentially very large scaling issues such as you describe is to look to horizontally partition your data. Some RDMS (no names mentioned) can do this as native functionality.
For example one could choose a very simple (and this is just for examples sake not a recommendation for production) horizontal partition scheme based on the lexical distribution of your users. So all the A-C go in one pot and so on and so on.
Such an approach gives you a half way house between the single pot approach or the table per user approach which will likely result in an acceptable compromise between manageability and performance.
ThePassenger said:
jsr said:
Also, can mysql/php cope with large numbers of tables, or is it limited?
Yes it can. However, if you are absolutely pounding the back end MySQL is out the window. As is MS-SQL; Access is to be ignored (which is SOP anyway). DB2 or Oracle. Be prepared to pay through the nose for the support and, if you go with say DB2 + AIX, the kit as well. MySQL has come a long way from being rinky-dink but if you're serious about this, you need a serious DB.
PostgreSQL will do it, but you have to consider more than just performance, there's failover, replication and backups to consider as well. Forget DB2, Sybase and Oracle unless you're already minted. Forget any proprietary OS/hardware for the same reason.
Some unix variant running an Apache webserver and a database on a separate server will handle that lot quite easily to kick off with, poor coding/setup notwithstanding.
Some unix variant running an Apache webserver and a database on a separate server will handle that lot quite easily to kick off with, poor coding/setup notwithstanding.
m12_nathan said:
You are right, after all MySpace running 30 million+ users on SQL 2005 must be a fluke, and google using MySQL - well, they probably don't have very much data to worry about
I would not want to use MYSQL in any significant project. Five looked promising when I saw it had views, triggers and stored procedures. But it dosent support check constraints. It dosent support transactions within triggers and the developers seem to have a very arrogant attitude. This snippet from the manual in particular worries me: MySQL allows you to store certain incorrect date values into DATE and DATETIME columns (such as '2000-02-31' or '2000-02-00'). The idea is that it's not the job of the SQL server to validate dates
They are moving in the right direction and hopefully version 6 will be a big imporvement.
m12_nathan said:
ThePassenger said:
jsr said:
Also, can mysql/php cope with large numbers of tables, or is it limited?
Yes it can. However, if you are absolutely pounding the back end MySQL is out the window. As is MS-SQL; Access is to be ignored (which is SOP anyway). DB2 or Oracle. Be prepared to pay through the nose for the support and, if you go with say DB2 + AIX, the kit as well. MySQL has come a long way from being rinky-dink but if you're serious about this, you need a serious DB.
As plasticpig said MySQL is kinda almost there (those were the reasons I was thinking of but erm... beer makes me forget). If you don't want or need the stuff that's missing and your happy to write your own middleware to go between the apps and the DB then sure, it's all golden. Google did this and it works for them, but you have to factor that in to the additional time/cost of doing so.
Oracle does have one advantage if you stick it on your 'must have' requirements sorting the wheat from chaff when it comes to hiring people is easier... but the cost of an Oracle dev Vs MySQL dev does make the little fella tempting.
ThePassenger said:
m12_nathan said:
You are right, after all MySpace running 30 million+ users on SQL 2005 must be a fluke
Very true but MS-SQL can very quickly run out of steam or get its knikkers in a knot. You would also have the issue of 'what if' floating around after it. A wrong firewall change goes in and opps. Sorry, it is an MS product.As for a wrong firewall change breaking it, give me strength.
Any change of any part of any system can have an effect on any other part of that system, whether its MS, Oracle, mySQL, whatever. If you make a badly planned change, you can break stuff. Blaming the stuff you broke is rarely appropriate.jsr said:
I'm creating a social network site, it wont be a facebook or myspace, but it will be unique and could easily attract a million registered users.
It's gonna have the usual blog and messages for each user, which brings me to me question... In terms of the database structure, should i use a separate table for each user's blog/messages, or use one table for all blog entries/messages and then query by user when needed?
The one table method will be easy for me to administer, but if the number of users really does start to grow to big numbers, then mysql query would have to work on a huge amount of data and would surely become slower and slower.
The table per user method is a little more work to implement, but still pretty straightforward, and should mean queries will be quicker. The only downside i can think of is that i'll have loads of tables to administer, though i guess once everything works correctly you just let it grow and grow.
Also, can mysql/php cope with large numbers of tables, or is it limited?
Interesting.It's gonna have the usual blog and messages for each user, which brings me to me question... In terms of the database structure, should i use a separate table for each user's blog/messages, or use one table for all blog entries/messages and then query by user when needed?
The one table method will be easy for me to administer, but if the number of users really does start to grow to big numbers, then mysql query would have to work on a huge amount of data and would surely become slower and slower.
The table per user method is a little more work to implement, but still pretty straightforward, and should mean queries will be quicker. The only downside i can think of is that i'll have loads of tables to administer, though i guess once everything works correctly you just let it grow and grow.
Also, can mysql/php cope with large numbers of tables, or is it limited?
I'll email you some ideas if you agree to give me 5% of any money you make on the site in the future. :-)
Edited by RameshUK on Friday 25th May 15:20
Gassing Station | Computers, Gadgets & Stuff | Top of Page | What's New | My Stuff