mysql table limit??

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
User avatar
nathanr
Forum Contributor
Posts: 200
Joined: Wed Jun 07, 2006 5:46 pm

mysql table limit??

Post 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??
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post 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. ;)
User avatar
nathanr
Forum Contributor
Posts: 200
Joined: Wed Jun 07, 2006 5:46 pm

Post 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.
User avatar
volka
DevNet Evangelist
Posts: 8391
Joined: Tue May 07, 2002 9:48 am
Location: Berlin, ger

Post 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...
User avatar
nathanr
Forum Contributor
Posts: 200
Joined: Wed Jun 07, 2006 5:46 pm

Post 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 :)
Charles256
DevNet Resident
Posts: 1375
Joined: Fri Sep 16, 2005 9:06 pm

Post 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
User avatar
onion2k
Jedi Mod
Posts: 5263
Joined: Tue Dec 21, 2004 5:03 pm
Location: usrlab.com

Post by onion2k »

Sounds like someone spreading MySQL FUD to me.
User avatar
nathanr
Forum Contributor
Posts: 200
Joined: Wed Jun 07, 2006 5:46 pm

Post 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.
mwasif
Forum Newbie
Posts: 13
Joined: Sun Jul 15, 2007 1:24 pm

Post by mwasif »

The maximum number of tables in a Cluster database in MySQL 5.0 is limited to 1792.
Post Reply