Originally posted by chrisranjana
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.