What is the best practice? use different database for every users or use same database with userid coloumn

Question

What is the best practice? use different database for every users or use same database with userid coloumn.

I am working on mysql database and php. I having a little bit confusion about my project.

My project is user based. If a user has been registered then he can store data on a many tables. I have created all tables with userid column to find data for particular user. But now I am thinking about it if tables having huge amount of rows then my project will getting slow to fetch records.

Now the confusion is "what is the best practice use different database for every users or use same database with userid coloumn."

Answer 1

Use one database.

Using different database will not help scalability if you have a "huge amount of rows". Acquiring a database connection is much slower in general than simple queries, especially those running against indexed columns like primary keys and unique keys. (A popular webhost considers the performance equivalence of connections to queries as 25:1)

If you are worried about scalability, you'll need to read up on database connection pools, database indexes, and, in extremely large datasets, database sharding.

I've never heard of using different databases per user. I've heard of hosted applications that host data for third-party customers use different databases for each customer in order to keep data completely separate (the alternative, hosting all customers' data in one database, is called multitenancy).

Answer 2

Use one database only,

For scaling it further you can use many things like:-

1. sharding of user table. MySQL sharding approaches?
2. Mysql Partitioning (http://dev.mysql.com/doc/refman/5.1/en/partitioning.html)
3. using Master- slave configurations (insert/update/delete on master and select on slave)
4. a caching layer over mysql like (Redis/Memcache)
5. optimizing table structures, using primary key in where clause
6. putting right indexing on table structure.