Previous / Next / Up / Table of Contents

7 MySQL Table Types

As of MySQL Version 3.23.6, you can choose between three basic table formats (ISAM, HEAP and MyISAM). Newer versions of MySQL support additional table types (InnoDB, or BDB), depending on how you compile it. A database may contain tables of different types.

When you create a new table, you can tell MySQL what type of table to create. The default table type is usually MyISAM.

MySQL will always create a `.frm' file to hold the table and column definitions. The table's index and data will be stored in one or more other files, depending on the table type.

If you try to use a table type that is not compiled-in or activated, MySQL will instead create a table of type MyISAM. This behaviour is convenient when you want to copy tables between MySQL servers that support different table types. (Perhaps your master server supports transactional storage engines for increased safety, while the slave servers use only non-transactional storage engines for greater speed.)

This automatic change of table types can be confusing for new MySQL users. We plan to fix this by introducing warnings in the new client/server protocol in version 4.1 and generating a warning when a table type is automatically changed.

You can convert tables between different types with the ALTER TABLE statement. See section 6.5.4 ALTER TABLE Syntax.

Note that MySQL supports two different kinds of tables: transaction-safe tables (InnoDB and BDB) and not transaction-safe tables (HEAP, ISAM, MERGE, and MyISAM).

Advantages of transaction-safe tables (TST):

Safer. Even if MySQL crashes or you get hardware problems, you can get your data back, either by automatic recovery or from a backup + the transaction log.
You can combine many statements and accept these all in one go with the COMMIT command.
You can execute ROLLBACK to ignore your changes (if you are not running in auto-commit mode).
If an update fails, all your changes will be restored. (With NTST tables all changes that have taken place are permanent)
Can provide better concurrency if the table gets many updates concurrently with reads.

Note that to use InnoDB tables you have to use at least the innodb_data_file_path startup option. See section 7.5.3 InnoDB Startup Options.

Advantages of not transaction-safe tables (NTST):