MySQL Table Types
*****************

 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.

 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. *Note `ALTER TABLE': ALTER TABLE.

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. *Note InnoDB start::.

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.

[Назад] [Содержание] [Вперед]