Every MySQL server has a data directory under which it manages the contents of databases and tables. The server represents these using directories and files under the data directory as follows:

• MySQL associates each database with a directory under the data directory. (This means that the data directory is the parent of all database directories.) A database directory has the same name as the database that it represents. For example, a database named world corresponds to a directory named world under the data directory. MySQL uses the database directory to manage the components of the database—that is, its tables and indexes. A database may be empty, or have one or more tables. Databases cannot be nested; one database cannot contain another.

• 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. Every table is associated with a format file in the database directory that 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 directory under the server's data directory. Depending on the table type, the storage engine for a table might create additional files for the 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. If Country is an InnoDB table, MySQL still creates a Country.frm format file in the database directory, but the InnoDB storage engine stores the table data and index information elsewhere, in the InnoDB tablespace.

The MySQL server uses storage engines to manage data in tables.
Each storage engine handles a particular table type. Each table type has differing characteristics and features; these are summarized in this section as an overview. Elsewhere, this study guide concentrates primarily on the MyISAM and InnoDB table types, which are also discussed in more detail in the "Professional Study Guide." For
additional information on all table types, see the MySQL Reference Manual.

The MyISAM storage engine manages tables that have the following characteristics:

• Each MyISAM table is represented on disk by an .frm format file, as well as an .MYD datafile and an .MYI index file. All these files are located in the database directory.

• MyISAM has the most flexible AUTO_INCREMENT column handling of all the table types.

• MyISAM tables can be used to set up MERGE tables.

• MyISAM tables can be converted into fast, compressed, read-only tables.

• MyISAM supports FULLTEXT searching.

• MySQL manages contention between queries for MyISAM table access using table-level locking. Query performance is very fast for retrievals. Multiple queries can read the same table simultaneously. For a write query, an exclusive table-level lock is used to prevent use of the table by other read or write queries, leading to reduced performance in environments with a mix of read and write queries. Deadlock cannot occur with table-level locking. (Deadlock occurs when two or more queries are blocked, or stopped from completing, because each is waiting for one of the others to finish.)

The InnoDB storage engine manages tables that have the following characteristics:

• Each InnoDB table is represented on disk by an .frm format file in the database directory, as well as data and index storage in the InnoDB tablespace. The InnoDB tablespace is a logical single storage area that is made up of one or more files or partitions on disk. The tablespace is shared by all InnoDB tables.

• InnoDB supports transactions (using the SQL COMMIT and ROLLBACK statements) with full ACID compliance.

• InnoDB provides auto-recovery after a crash of the MySQL server or the host where the server runs.

• InnoDB supports foreign keys and referential integrity, including cascaded deletes and updates.

• MySQL manages query contention for InnoDB tables using multi-versioning and row-level locking. Multi-versioning gives each transaction its own view of the database. This, combined with row-level locking, keeps contention to a minimum. The result is good concurrency even in an environment consisting of mixed reads and writes. However, it's possible for deadlock to occur. Multi-versioning is discussed further in the "Professional Study Guide."

The MERGE storage engine manages tables that have the following characteristics:

• A MERGE table is a collection of identically structured MyISAM tables. Each MERGE table is represented on disk by an .frm format file and an .MRG file that lists the names of the constituent MyISAM files. Both files are located in the database directory.

• Logically, a query on a MERGE table acts as a query on all the MyISAM tables of which it consists.

• A MERGE table creates a logical entity that can exceed the maximum MyISAM table size.

The BDB storage engine manages tables that have the following characteristics:

• Each BDB table is represented on disk by an .frm format file and a .db file that stores data and index information. Both files are located in the database directory.

• BDB supports transactions (using the SQL COMMIT and ROLLBACK statements) with full ACID compliance.

• BDB provides auto-recovery after a crash of the MySQL server or the host where the server runs.

• MySQL manages query contention for BDB tables using page-level locking. This locking level provides concurrency performance that is intermediate to that of row-level and table-level locking. It's possible for deadlock to occur.

The HEAP storage engine manages tables that have the following characteristics:

• Each HEAP table is represented on disk by an .frm format file in the database directory. Table data and indexes are stored in memory.

• In-memory storage results in very fast performance.

• HEAP table contents do not survive a restart of the server. The table structure itself survives, but the table contains zero data rows after a restart.

• HEAP tables use up memory, so they should not be used for large tables.

• MySQL manages query contention for HEAP tables using table-level locking. Deadlock cannot occur.

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.