8.1. Table Properties

Each MySQL server has a directory called the "data directory" under which it stores its databases. The data directory contains one subdirectory for each database managed by the server. Each of these is called a "database directory" and has the same name as the database that it represents. The server uses a given database directory to manage the tables in that database. Tables have both a logical and physical structure.

Logically, each table in a database consists of rows and columns. A table can be empty (it can have zero rows of data), but it must have at least one column. A table may also be indexed to improve query performance. Indexes enable MySQL to look up data values quickly rather than searching through the entire table. Indexes become increasingly important the larger a table becomes.

Physically, each table is associated with one or more files on disk. Every table has a format file in its database directory. The format file is created by the server and contains the definition, or structure, of the table. The format filename is the same as the table name, plus an .frm suffix. For example, the format file for a table named Country in the world database is named Country.frm and is located in the world database directory under the server's data directory.

MySQL manages tables using storage engines, each of which handles tables that have a given set of characteristics. Different storage engines have differing performance characteristics, and you can choose which engine most closely matches the characteristics that you need. For example, you might require transactional capabilities and guaranteed data integrity even if a crash occurs, or you might want a very fast lookup table stored in memory for which the contents can be lost in a crash and reloaded at the next server startup. With MySQL, you can make this choice on a per-table basis. Any given table is managed by a particular storage engine. In addition to the .frm file that the server creates, a table may be associated with one or more other files that the storage engine creates in which to store the table's contents. The number and types of files vary per storage engine, because each engine manages table storage differently. Here are some examples:

• The MyISAM engine creates a data file and index file for each table. If Country is a MyISAM table, the MyISAM storage engine creates data and index files named Country.MYD and Country.MYI to store data rows and indexes (respectively) for the table.

• By default, the InnoDB engine shares files for multiple tables. If Country is an InnoDB table, there will be a Country.frm format file created by the in the database directory, but the InnoDB storage engine itself stores the table data and index information elsewhere, in the InnoDB shared tablespace. The tablespace is used by multiple tables. That is, files for storing table contents are not per-table as for MyISAM but are shared among tables.

• The MEMORY engine does not use any disk storage at all for table contents. It manages table contents in memory.

Additional detail on storage management for these engines is given in Chapter 29, "Storage Engines."

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:

• 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 then can 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.

• 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.