can i create as many as 500000000 databases in Mysql??
If yes, is it efficient??
here, each database holds user data with 6 tables in it containing user info in it..
please explain me the pros and cons of this idea with respect to performance and implementation..
Please Don't!
Whatever you're thinking, whatever the reason. There is absolutely no way that creating that many databases is a good idea.
If you're looking for storing data per user, or for every X. Then please take a look again at what a relational database is "A relational database matches data by using common characteristics found within the data set."
Let's say you're createing an image service and want each user to have their own images. One way is to give each user their own database. This is wrong, because the data for each user contains the same characteristics, the only thing that is different is the relation (user A has picture A, user B has picture B), (1 user has 0...* pictures, and 1 picture belongs to 1 user). So instead of creating that many databases, you create a user table, and relate that to a picture table, this way you've efficiently used a database.
To answer your more direct question. Create a table with 50.000 entries is surely more efficient then creating 50.000 databases.
Edit a related answer on mysql.com: http://forums.mysql.com/read.php?20,74692,74705#msg-74705
Creating a complete database for each unique user is less than ideal. You're now forcing SQL to store each database and independently reference each table. Not to mention, the database has to now work harder at retrieving and storing this information as it's going between databases.
For normalization purposes, you're better off making a "user" column in each table that you can call upon for your CRUD operations. This keeps everything all in one place, and keeps the strain off of the database.
i suspect he is wondering this for performance reasons - not because he knows no better.
lookups in a unique db for each user will not get slower as the db grows - because, simply, it wont.
However, if you are willing to go to these lengths to improve performance, my personal opinion would be consider a different DBMS first. My personal experience with mySQL is that its a very good DBMS if speed is not a concern - I've personally found MSSQL much much faster.
