GNU Info

(mysql.info)Table types

---

Next: Clients Prev: Reference Up: Top

---

Enter node , (file) or (file)node

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 type (`BDB', or `InnoDB'), 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. Note: InnoDB start.

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 supports
different table types (like copying tables to a slave that is optimized
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. Note: `ALTER TABLE'.

Note that MySQL supports two different kinds of tables.
Transaction-safe tables (`BDB', `InnoDB' 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)

Advantages of not transaction-safe tables (NTST):

   * Much faster as there is no transaction overhead.

   * Will use less disk space as there is no overhead of transactions.

   * Will use less memory to do updates.

You can combine TST and NTST tables in the same statements to get the
best of both worlds.

MyISAM

MyISAM tables

MERGE

MERGE tables

ISAM

ISAM tables

HEAP

HEAP tables

BDB

BDB or Berkeley_db tables

InnoDB

InnoDB tables