When is it time to create a 2nd database?

Question

I have a mysql database which has grown to over 200 tables in it. I know this isn't a performance problem, but most admin tools I have aren't organized well enough to manage this number of tables easily, and I am thinking of putting some (new) tables into a 2nd database just for my own sanity.

Has anyone else gone down this path, and was it worthwhile, or did it end up being more trouble than having one monolithic database?

Edit: Thanks for all the answers, although I was looking for some real-world experiences rather than hypothetical advice, sorry I can't pick a right answer.

Answer 1

Not sure what you'll gain. You'll have the same number of tables to maintain, just spread around more. Unless you can partition them in some reasonably logical scheme, you'll just be making them more difficult to find. If they are interrelated, then you make the relationships more difficult to express.

Answer 2

This is a similar question, one where the developer did that. They didn't have near that many tables, but some of the comments tell of how annoying it can be.

Answer 3

I currently have a database with 600 tables in it and growing and I haven't had a problem. I use MySQL Query Browser and MySQL Administrator without issue, are you having any specific problems?

Answer 4

Personally, as along as all the tables in the database are for the same application, I would recommend keeping them in the same database. If you run into performance problems, you should get more hardware. The most important reason not to split them up, is that no matter how much you think there is a partition in the data, you will inevitably need to join tables across the two databases which is a very costly, in terms of performance, operation to do.

Answer 5

If you want to start moving some things out to a second database make sure to do it with purpose. I do this when I realize some of the data will be reference data for several projects I'm working on. It becomes my "reference" database.

Then my database list would look something like the following:
project1
project2
project3
reference

Answer 6

I think you are having a tool problem, not a database problem! My advice would then be to change your tools, not to break your database in multiple parts. The short term "advantage" (having a shorter table list) will lead you to multiple long term problems, like managing code to update/extract data among your multiple databases.

Answer 7

I'd prefix your table names with something meaningful so you can find the tables you need more easily.

I agree with everyone else though .. 2 databases equals a coding nightmare, not to mention maintaining them (backups, restores, etc.)

Answer 8

Are these 200 tables part of one application? If not, then i'd recommend having one database per application, or at least one for every big application.

If all of them belong to the same app, I would not go to a second database because I think that's going to cause more problems (managing queries etc.) than it solves. Maybe a little bit of refactoring to use fewer tables?