The MySQL server places no limits on the number of tables in a database, although individual storage engines might have their own limits. For example, the InnoDB storage engine allows a maximum of two billion tables to exist within the InnoDB shared tablespace. This places a limit (albeit a rather high one) on the number of InnoDB tables that can be created among all databases combined. (The limit isn’t enforced on a per-database basis because the InnoDB tablespace is shared among all databases.)
A limit on the maximum number of tables allowed might also be imposed by your operating system or filesystem. For example, the MyISAM storage engine places no limits on the number of tables in a database. However, MyISAM tables are represented by data and index files in database directories, so a limit on the number of tables in a database might arise from factors external to MySQL:
- If the operating system or filesystem places a limit on the number of files in a directory, MySQL is bound by that constraint.
- The efficiency of the operating system in handling large numbers of files in a directory can place a practical limit on the number of tables in a database. If the time required to open a file in the directory increases significantly as the number of files increases, database performance can be adversely affected.
- The amount of available disk space limits the number of tables. If you run out of space, you cannot create more tables.
MySQL storage engines do place limits on the allowable maximum size of individual tables. These limits vary per storage engine, but they tend to be rather high. Another factor that limits table size is the maximum file size allowed by your operating system or filesystem. An operating system may support different types of filesystems, each of which may have a different maximum file size.
For large tables, you might find that you run up against operating system or filesystem limits on file sizes before you reach MySQL’s internal table size limits. Several strategies can be used for working around file size limits:
Posted in: MySQL