CODEWITHSUNDEEP
mysqlnumbers
Emeka Mbah
Emeka Mbah

Reputation: 17553

Recommended number of tables for in a single MYSQL Database

Am currently developing an application in Laravel 4. After proper database normalization the required number of tables for my application increased to about 48 tables.

Am worried so I will like to ask you experienced guys for advice or suggestions.

  1. What impact will this have on my database on the long run in terms of performance and maintenance.
  2. Should I create another database and split the tables

Any contribution or suggestion will be well appreciated.

NB:The longest column in the one of the tables is 25

Upvotes: 0

Views: 104

Answers (2)

N.B.
N.B.

Reputation: 14091

Recommended number of tables is how many your application needs. There is no magic number.

Databases are designed to be useful to extract data in a way it's useful to the user using it. If you need N tables after you've done the analysis of requirement, then use N tables.

Performance of database (for small apps like relatively low volume websites or web apps) is usually governed by the speed of access to the data set. That means it's bound by the hard drive, or so called I/O bound.

If you worry that amount of tables will slow down your app - it won't. The default MySQL engine (InnoDB) stores all physical data in a single file by default (unless file per table is turned to 1). This is an optimization done by InnoDB/MySQL devs so you don't have to worry about myriad of problems such as open file descriptors, number of tables and what not.

Now, to reflect from my own experience - I have had access to a beast that contains table and view number ranging in such a huge number that I'm afraid to mention it (it's a CRM/ERP solution composed out of tens of products), and it ranges in above 100,000. So yes, 25 (or 1000) shouldn't worry you.

Upvotes: 0

Joe Doherty
Joe Doherty

Reputation: 3958

Number of tables does not really make that much of a difference. There are few tuning things which can be done to help but ~25 is not going to be an issue for MySQL.

I also would NOT HAVE a separate database, this will mean you can't do table joins or foreign keys across the data.

I would go with your current design, and when needed ask some questions for optimisation.

I have seen MySQL deployments with ~500 tables, just to put your mind at rest.

Regards Joe

Upvotes: 1

Related Questions