1 . Is there a maximum number of tables LIMIT per database in Mysql ?
2. splitting into smaller tables will increase performance compared to having large tables isnt it ?

chris

1. could'nt remember a database limit in mysql.

2. yes. build joins instead of large tables.

Thanks for the reply

Yes mysql join makes mysql queries efficient

But still I want to know how many tables a mysql database can hold ?

When using MyISAM table, the limit will be determined by your operating system, ie. the number of files you can have in one directory.

Using InnoDB tables removes that limitation by allowing multiple tables within a single datafile (tablespace).

The maximum tablespace size is 4 billion database pages. By default, a 'page' is 16K, but it is possible to recompile with 64K pages. This will allow a single tablespace size of ... 'BIG'.

A table will obviously take up at least one database page, meaning you can theoretically have a maximum of 4 billion tables per tablespace.

The number of tablespaces will again be limited by your operating system. Using NTFS on NT, the limit is 4,294,967,295. I imagine UNIX will be limited by the number of inodes. However, before reaching the physical limit of files you wil probably run into an issue with the maximum number of 'open' files allowed.

The first limit you hit will undoubtedly be that of your wallet while buying disk drives.

just one word "WOW !"

in a word: no

compare to the alternatives:

1. you need the data but don't perform the join, leaving your query results useless

2. you decide not to normalize, resulting in even slower queries

well, you asked twice, i answered twice

what was i supposed to do, leave the question unanswered? what if somebody else stumbles over this thread like you did?

by the way, you've won this month's prize for dredging up the oldest dormant thread -- eight years!!