Too Many Tables - Bad?

[Turtle]

I have a database with 138 tables. Is having too many tables bad? Can it slow things down?

[dniwebdesign]

I think it will only slow things down if you query all of them. If you are only selecting a few at a time I don't think it should slow things down all that much. Correct me if I'm wrong.

[marek_mar]

It shouldn't. If you query them resonably. You could ask the guys in the database forum too.

[Spookster]

If you have that many tables then either this is one incredibly huge application with dozens of entities or else the database was designed incredibly wrong. Or are you just putting tables from many different applications into one database?

In any case this is not a PHP issue so I will move it to the database forum.

[Turtle]

Thanks for replying. This is my forum's database. It doesn't query them all at the same time, just a few. The reason it has so many tables is because I have lots of addons.

[Kiwi]

The main application I work on has something like 10,000 tables (or there abouts). A fair number of them top 1,000,000 records.

Properly normalising your data into tables and using sensible indexing will significantly improve performance, not reduce it. The number of tables is a very bad indicator of performance: a lot of other variables are more important.

[Spookster]

Originally Posted by Kiwi

The main application I work on has something like 10,000 tables (or there abouts). A fair number of them top 1,000,000 records.

Properly normalising your data into tables and using sensible indexing will significantly improve performance, not reduce it. The number of tables is a very bad indicator of performance: a lot of other variables are more important.

10,000 tables? Come on now. That sounds a bit exaggerated. I can't fathom any application needing that many tables. I spent 3 years as a database engineer for Corning Inc. I dealt with processing millions of records a day into a historical database somewhere in the 2TB size. It contained a few hundred tables however was not a normalized database system. In fact it was a very poorly designed system and performance sucked when it came to retrieving data but it was designed around fast processing of data coming from machines on the plant floor.

The number of tables can be a big factor on performance if you are querying specific data across a dozen tables all containing millions of records each. There are of course lots of other important factors that can be involved in performance but the number of tables is definitely one of those.

[Kiwi]

10,000 is a low estimate - similar systems run up towards 14-15,000 tables. This is pretty normal for an ERP system. I never dig into the admin tables to see exactly how big the installation is. That excludes all the temp tables built, used and dropped during execution of the various applications and SQL views. I'm not sure what sort of total tablespace our DBAs are working with - I just know that at year end, we need to extend it and bump up the processing capactity.