MySQL table limit

[tingle]

Does MySQL have a limit to the number of tables you can have in each database before it becomes slow? For instance, if i have 100 tables in one database, does this affect performance at all?

Thanks in advance

Tingle

[eva2000]

http://www.mysql.com/doc/T/a/Table_size.html particularly line in bold ;)

MySQL Version 3.22 has a 4G limit on table size. With the new MyISAM table type in MySQL Version 3.23, the maximum table size is pushed up to 8 million terabytes (2 ^ 63 bytes).

Note, however, that operating systems have their own file size limits.

Linux-Intel 32 bit 2G, 4G or more, depends on Linux version
Linux-Alpha 8T (?)
Solaris 2.5.1 2G (possible 4G with patch)
Solaris 2.6 4G
Solaris 2.7 Intel 4G
Solaris 2.7 ULTRA-SPARC 512G

On Linux 2.2 you can get bigger tables than 2G by using the LFS patch for the ext2 file system. On Linux 2.4 there exists also patches for ReiserFS to get support for big files.

This means that the table size for MySQL databases is normally limited by the operating system.

By default, MySQL tables have a maximum size of about 4G. You can check the maximum table size for a table with the SHOW TABLE STATUS command or with the myisamchk -dv table_name.

[tingle]

hi, thanks for your reply, there is one thing, do you know if there is any kind of limit/performance issue with the number of tables you have. eg. Does it make a difference having 200 tables or 20 tables?
thanks

tingle

[Chris Schreiber]

No, there will be no difference between a database with 20 tables versus 200 tables.... it's only when you get a very large number of tables (over 1000) that you may see performance problems.

[tingle]

just what i wanted to hear, thanks :)