FRIHOST FORUMS SEARCH FAQ TOS BLOGS COMPETITIONS
You are invited to Log in or Register a free Frihost Account!


What is the Limit of Tables in a SQL Database ?





ankurthakur
Hello Guyz,

I've been wondering if there is any Limit of Number of tables in a database.

Actually, I was thinking to start a Free Website making service which will be a realtime website. For example, it will not be a simple HTML, but it will be provided with a CMS.

Of course, I cannot make separate installation (CMS files) for each user as it will take too much of space then (10 mb x 100 users or more).

Hence, I am thinking to use common files and make different tables for different users. So I will be doing it in single Database.

So if it takes 30 tables for 1 user, then it may take : 30 x 100 users = 3000 tables

Similarly, it can reach upto thousands of tables.

So I was wondering if there is any limit of no of tables in a database in general ? General question !

Thank You
davidv
I've actually wondered about this myself when I just started learning about databases and from what I recall, the limit is imposed by either the maximum size a file can be which I believe is based upon your operating system or the total capacity of your physical drive. However, I could be wrong.
ankurthakur
davidv wrote:
I've actually wondered about this myself when I just started learning about databases and from what I recall, the limit is imposed by either the maximum size a file can be which I believe is based upon your operating system or the total capacity of your physical drive. However, I could be wrong.


Yeah.. I think that you're right. It must be memory dependent because I cannot find any INI option or something like that which will set MAX NO OF TABLES or etc in MySQL.

Let me try it on my Localhost... Very Happy
badai
i can only give info on mysql when using myisam or innodb

myisam:
the limit will be determined by your file system, that is, the number of files you can have in one directory (31998 for ext3 for example).

innodb:
using innodb engine 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. 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 file system. however, before reaching the physical limit of files you wil probably run into an issue with the maximum number of 'open' files allowed.

so, you're gonna have 30k users?
ankurthakur
badai wrote:
i can only give info on mysql when using myisam or innodb

myisam:
the limit will be determined by your file system, that is, the number of files you can have in one directory (31998 for ext3 for example).

innodb:
using innodb engine 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. 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 file system. however, before reaching the physical limit of files you wil probably run into an issue with the maximum number of 'open' files allowed.

so, you're gonna have 30k users?


Oh man... Thanks a lot for this useful info... Smile

And yeah, this problem will arise for sure because I will be having thousands of Users to create website and hundreds of their corresponding registered users ... Razz

Anyways, I am giving it a try first on my Localhost and then I will proceed for online here Very Happy
badai
forget to tell you, innodb is damn slow. you have to tweak it to make it faster.
Related topics
too many people online, session error when loading page
SQL Tutorial
SQL
Importing an existing database...
SQL and PHP
Can I create many SQL database by my self
sql with phpmyadmin
Trying to move SQL database
Issue uploading SQL database.
How to move SQL database to new domain?
manually input to sql database
Move database to different SQL server
English Dictionary in text file or sql database
PHP website with SQL Database.
Reply to topic    Frihost Forum Index -> Scripting -> Php and MySQL

FRIHOST HOME | FAQ | TOS | ABOUT US | CONTACT US | SITE MAP
© 2005-2011 Frihost, forums powered by phpBB.