MySQL Reference Manual

Chapter 7. MySQL Table Types

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

When you create a new table, you can tell MySQL which table type it should use for the table. MySQL will always create a .frm file to hold the table and column definitions. Depending on the table type, the index and data will be stored in other files.

Note that to use InnoDB tables you have to use at least the innodb_data_file_path startup option. See Section 7.5.2.

The default table type in MySQL is MyISAM. If you are trying to use a table type that is not compiled-in or activated, MySQL will instead create a table of type MyISAM. This is a very useful feature when you want to copy tables between different SQL servers that support different table types (like copying tables to a slave that is optimised for speed by not having transactional tables). This automatic table changing can, however, also be very confusing for new MySQL users. We plan to fix this by introducing warnings in MySQL 4.0 and giving 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.

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

Advantages of transaction-safe tables (TST): ...