1 Large Database or 400 smaller
 

I need some advice from the pro's :)

I am building an application that has data from several places. The structure is all the same but the data is specific to a user.
1 such a feed can consist of 50,000 rows but on average it's more between 500-2000 rows. In total there will be about a 400 different tables.

I am in doubt if I should merge all data into 1 big table. The number of rows will go beyond 2 million I suspect.

My main "problem" is I want to be able to search and select
in the description and title column and that should be done over all tables if I keep it the original way.

What is the best way to tackle this. Something to note is that the search and select is at the backend where no actual visitor will be bothered as they only see the end result.

Re: 1 Large Database or 400 smaller
 

I've never used MySQL, but since your structure is the same in all cases, it makes ssnse to put everything into one big database with a key to indicate which user each record belongs to. That will probably save you a lot of trouble in the long run. For instance, if you need to change the structure later, it will be a lot easier to change in once instead of 400 times. The only question is if MySQL can handle that number of records without a problem.

Re: 1 Large Database or 400 smaller
 

I do think i will move forward with that idea too. Been thinking about it and asked around a bit and altho I didn't got much response what I heared was it should not be a problem to put it all in 1 big db.

I do want to make sure tho that mysql is capable of handling 2 million rows and what alternatives are.

Re: 1 Large Database or 400 smaller
 

I didn't see this thread sooner and it may now be too late, but 1 database is the way to go; easier to manage, easier to optimise, easier to backup, faster, requires less disk space.. 1 database is better in every way.

Don't be worried about the number of rows in a database; they are designed to cope with large volumes of data.