Limits for TABLES in DATABASE?

[Janne Timmerbacka]

Hello,

where can I find limits of how many TABLES can exists in one DATABASE?
Also, is there any performance penalty (or other) for having alot of
TABLES in one DATABASE?

Regards
Janne Timmerbacka

[Aggro]

Janne Timmerbacka wrote:

where can I find limits of how many TABLES can exists in one DATABASE?
There is no limit from MySQL, limit comes from os.

x = (How many files can be hold in single directory in your operating
system / 3)

Solve x
Also, is there any performance penalty (or other) for having alot of
TABLES in one DATABASE?

For one user? Poor database design is one and good penalty. And if you
need to run selects with joins, that will propably slow queries. One
table design is also poor design so don't go there.

Most common reason for using multiple tables for a bad reason is
something like this:

table1:
- field_x
- field_y
- field_z

table2:
- field_x
- field_y
- field_z

As you can see, both table1 and table2 have same column names, and they
contain same kind of objects. Only reason there are two tables is that
they belong to two different groups. Instead you should have:

table1:
- field_x
- field_y
- field_z
- group

Where you can now put both of them into same table and sort them by
group value.

[Janne Timmerbacka]

Thank you for your fast answer ;).

I'm using www.e107.org CMS-system, which creates about 30 mysql TABLES. I
need to create multiple CMS-systems with around 30 TABLES each, but my
current www server host gives me only one DATABASE. Maybe I will have
around 200 tables, but I don't belive need more than that. Each table has
it's own tablename prefix depending on which CMS-system it belongs to. I
don't have to write any SQL-queries my self, because e107 takes care of
all database stuff.

If this approach dosen't create performance problems, I wouldn't like to
change anything or pay more to other www server host company.

Regards
Janne Timmerbacka

On Tue, 01 Jun 2004 06:35:17 GMT, Aggro <sp**********@yahoo.com> wrote:

Also, is there any performance penalty (or other) for having alot of
TABLES in one DATABASE?

For one user? Poor database design is one and good penalty. And if you
need to run selects with joins, that will propably slow queries. One
table design is also poor design so don't go there.

[Aggro]

Janne Timmerbacka wrote:

If this approach dosen't create performance problems, I wouldn't like
to change anything or pay more to other www server host company.

If different CMS-systems don't need to use tables from each other, then
there won't be speed issues. (It would be the same if you would use
multiple databases).

[Janne Timmerbacka]

They don't. Thank you for your time and support ;)
Janne

On Tue, 01 Jun 2004 15:58:23 GMT, Aggro <sp**********@yahoo.com> wrote:

If different CMS-systems don't need to use tables from each other, then
there won't be speed issues. (It would be the same if you would use
multiple databases).