Home arrow MySQL arrow Page 3 - Data Definition Language, Part 1

4.3 Limits on Number and Size of Database Components - MySQL

Studying for the MySQL Certification exam? This article, the first of two parts, covers roughly 10 percent of the material that will appear on the exam, including general database and table properties, storage engines and table types, and more. It is excerpted from chapter four of the book MySQL Certification Guide written by Paul Dubois et. al. (Sams, 2004, ISBN: 0672326329).

  1. Data Definition Language, Part 1
  2. 4.1 General Database and Table Properties
  3. 4.3 Limits on Number and Size of Database Components
  4. 4.4 Identifier Syntax
  6. 4.7 DROP TABLE
  7. 4.9 Creating and Dropping Indexes
  8. 4.10 Column Types
  9. 4.10.2 String Column Types
  10. 4.10.3 Date and Time Column Types
  11. 4.10.4 Column Options
  12. 4.10.5 Using the AUTO_INCREMENT Column Option
  13. 4.10.6 Automatic Type Conversion and Value Clipping
By: Sams Publishing
Rating: starstarstarstarstar / 41
January 19, 2005

print this article



The MySQL server can manage multiple databases, each of which may contain multiple tables. MySQL does not place any limits on the number of databases, although your operating system or filesystem might. Each database is represented as a directory under the server's data directory, so if the filesystem on which the data directory resides has a limit on the number of subdirectories a directory may contain, MySQL can create no more than that number of databases.

The MySQL server places no limits on the number of tables in a database. The InnoDB storage engine, on the other hand, allows a maximum of two billion tables to exist within the InnoDB 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.)

Your operating system or filesystem might also impose limits on the maximum number of tables allowed. For example, the MyISAM storage engine places no limits on the number of tables in a database. However, MyISAM tables are represented by files in the directory that MySQL associates with the database, so a limit on the number of tables in the 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.

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:

  • Exploit any features allowed by a given table storage manager for increasing table size. For example, the contents of a MyISAM table can sometimes be distributed into several smaller tables, which can then be treated as a single logical unit by combining them into a MERGE table. This effectively multiplies the maximum table size by the number of component MyISAM tables in the MERGE table.

  • The MyISAM storage engine supports a software RAID feature that partitions data storage for a table into a set of files under the database directory. This has the effect of breaking the single-file size barrier, although only for the datafile. Indexes are still stored in a single file, so software RAID might not be feasible for a heavily indexed table.

  • Convert the table for use with a storage engine that allows larger tables. For example, convert a MyISAM table to an InnoDB table. The InnoDB storage engine manages tables within a tablespace that can be configured to be much larger than the size of a single file, and InnoDB tables can grow as large as the available storage within the tablespace.

  • Modify your operating system. A factor external to MySQL that can be used to allow larger tables is to modify your operating system to support larger files. This might be possible by using a different filesystem type, or by using a newer version of the operating system that relaxes the limits on file sizes compared to an older version. You might also consider switching to an operating system that supports larger files than does your current operating system.

This chapter excerpt is from MySQL Certification Guide by Paul Dubois et al. (Sams, 2004, ISBN: 0672326329 ). Check it out at your favorite bookstore today. Buy this book now.

>>> More MySQL Articles          >>> More By Sams Publishing

blog comments powered by Disqus
escort Bursa Bursa escort Antalya eskort


- Oracle Unveils MySQL 5.6
- MySQL Vulnerabilities Threaten Databases
- MySQL Cloud Options Expand with Google Cloud...
- MySQL 5.6 Prepped to Handle Demanding Web Use
- ScaleBase Service Virtualizes MySQL Databases
- Oracle Unveils MySQL Conversion Tools
- Akiban Opens Database Software for MySQL Use...
- Oracle Fixes MySQL Bug
- MySQL Databases Vulnerable to Password Hack
- MySQL: Overview of the ALTER TABLE Statement
- MySQL: How to Use the GRANT Statement
- MySQL: Creating, Listing, and Removing Datab...
- MySQL: Create, Show, and Describe Database T...
- MySQL Data and Table Types
- McAfee Releases Audit Plugin for MySQL Users

Developer Shed Affiliates


Dev Shed Tutorial Topics: