Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

1000's of tables or multiple instances of mysql?

Tags:

mysql

I've inherited a company application that uses 58 tables per "object", and the application can have N objects. We are looking at an install of 75 - 100 objects for an application, which is 4300-5000 tables.

What we are trying to figure out is do we want to use one database and prefix the table names per object, or use one database per object (the application supports both). The only difference would be for each install of the application, we'd need additional mysql instances on different ports if we were to do per database.

Has anyone done anything similar? Are there any issues (outside of management) of having 4000+ tables in a database?

Edit

Thanks for the updates. As to a bunch of the comments

1) The company pays very well...I'd be dumb not to be taking this job. I wish just writing great code put the $$ in my bank account

2) Our clients are happy with the product. We've thought about re-writing it, but aside from the costs, we'd miss the market. While the structure is bad, the app works better than what most clients have.

3) Object isn't the best term...it's not like an object/class, but objects inside the application. I guess I can just say bucket instead.

like image 575
Ian Avatar asked Dec 30 '09 15:12

Ian


2 Answers

If you're repeating 58 tables a hundred times, I'd suggest that normalization rules have been trampled on. It's not likely that your company will revisit the schema design for this product, but I'd recommend it based on the information you've provided.

Don't make it worse by distributing the databases. How can latency help?

like image 166
duffymo Avatar answered Nov 15 '22 06:11

duffymo


MySql stores each table as a file, and there is no limit aside from your OS and your hard drive. However, there's a reason that's not commonly discussed- having thousands of tables is almost certainly the wrong thing to do, your database schema is probably badly in need of redesign.

like image 44
RossFabricant Avatar answered Nov 15 '22 06:11

RossFabricant