Turna Turna -4 years ago 98
MySQL Question

How should I arrange the mysql database?

Let's-
I have 1,000,000 of users signed in. It will be a facebook (Not really facebook) type of site.

So I have huge amount of data to be stored in my database. Please help me choosing the database design.

Design 1:

Database count : 1,000,000 [i.e. user_1, user_2, user_3]
Table count : 15 Per Database
Row Count : 100+ Per Table


Design 2:

Database count : 15
Table count : 1,000,000 Per Database
Row count : 100+ Per Table


Design 3:

Database count : 1
Table count : 15 Per Database
Row count : 10,000,000++ Per Table




What should I choose?

Design 1 : Which have Huge amount of Databases in the server.
Design 2 : Which has Huge amount of tables in a database.
Design 3 : Which has Huge amount of Rows in a table.

Answer Source

Option 1 may actually be a practical impossibility in reality, as each database requires its own resources and you will likely exhaust whatever system you use to implement it. And there's the operational nightmare of having to set up a new database every time a new user joins. This choice is consistent with the dictionary definition of "insane".

Option 2 is only slightly behind option 1 on the insanity scale, except it may be possible to implement.

Option 3 is no big deal and is a situation that occurs routinely. Use this option.

For a traditional relational database, up to about 20M rows is considered "small" for a table (no citation, just experience). 20M-200M is "medium", above that "large".

For non-traditional RDBMS, eg redshift, and NoSql DBs (mongo, cassandra, hadoop, etc) 10M rows is "tiny" and billions of rows is no big deal.

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download