Transaction-Safe Tables (TST) and Not Transaction-Safe Tables (NTST)

MySQL supports two different kinds of tables: Transaction-Safe Tables (InnoDB and BDB etc) and Not Transaction-Safe Tables (HEAP, MERGE, and MyISAM etc). MySQL also allows to combine Transaction-Safe Tables and Not Transaction-Safe Tables tables in the same database to get the best results. However, each kind of tables has its own advantages.

Advantages of Transaction-Safe Tables

1. 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.
2. You can combine many statements and accept these all in one go with the COMMIT command.
3. You can execute ROLLBACK to ignore your changes (if you are not running in auto-commit mode).
4. If an update fails, all your changes will be restored.

Advantages of Not Transaction-Safe Tables

1. Much faster as there is no transaction overhead.
2. Will use less disk space as there is no overhead of transactions.
3. Will use less memory to perform updates.

With Not Transaction-Safe Tables: If an update fails, no change will be restored as changes that have taken place are permanent.