Page 1 of 1

mysql table limit??

Posted: Wed Jul 18, 2007 5:50 am
by nathanr
So I've been a senior dev for lots of years, clustered environments, XXXgb databases etc etc

somebody said to me last ngiht that mysql has a limit on the number of tables you can use per database, and that is was 256! I've dismissed this as "erm no" but can't find any info anywhere about it.. which leads me to confirm that nope there is no limit. I also can't see how there can be a limit to tabels in db, based on the fact each table is a file..

anybody shed any long forgotten insight into this? something runs in my mind about there being a limit way back in the 3.X days but??

Posted: Wed Jul 18, 2007 6:36 am
by feyd
I have never seen any documentation that would substantiate a limit of 256 tables. That limitation seems absurd. It shouldn't been difficult to test though. ;)

Posted: Wed Jul 18, 2007 6:41 am
by nathanr
snap :) tested on 5 + 5.1 and obviously no limit, people say some funny things but you have to check them out just incase! opinions appreciated back in the "schema" thread feyd, I've replied and show a quick demo of the base functionaility required.

Posted: Wed Jul 18, 2007 7:01 am
by volka
http://dev.mysql.com/doc/refman/5.0/en/features.html wrote: Scalability and Limits:
* Handles large databases. We use MySQL Server with databases that contain 50 million records. We also know of users who use MySQL Server with 60,000 tables and about 5,000,000,000 rows.
Doesn't mean that there is a much lower limit per database, but...

Posted: Sat Jul 28, 2007 7:03 pm
by nathanr
thought I best revisit..

4294967295 or indeed 2^32 is the amount of rows you can stick in a myisam table without enabling large-tables :)

Posted: Sat Jul 28, 2007 8:45 pm
by Charles256
I seem to remember reading there was a limit to the number of tables you can have...Though it was ridiculously high.. Bah. I don't remember. Something we went over in Database Design courses... Definitely not important. Heh :-D

Posted: Sun Jul 29, 2007 5:21 am
by onion2k
Sounds like someone spreading MySQL FUD to me.

Posted: Sun Jul 29, 2007 7:45 am
by nathanr
yeah a junior came to me some weeks ago, claiming to be a senior and said that "mysql can only handle 256 tables" - when somebody says soemthing like that, however rediculous you have to double check just to be on the safe side, theoretically I can't see any table limit, and I've certainly never came accross one. There does come a time when you have to think, why do I have this many tables.. perhaps I need a bit of normalisation here..

In short, to the best of my knowledge, there are limits placed on number of rows, number of index's, number of cols that can be indexed or in an index, limit's to filesizes depending on OS config's, but short of running a stored proc to make millions of tables and actually get a point where we get an error, I can't see any way of getting a firm answer.

recomemnd the debate closes, unless somebody has a solid fixed figure on a per version/OS basis as to the number of tables limit.

Posted: Sun Jul 29, 2007 9:54 am
by mwasif
The maximum number of tables in a Cluster database in MySQL 5.0 is limited to 1792.