I am newbie to MySQL. I want to know about Transaction-Safe Tables (TST) and Not Transaction-Safe Tables (NTST). Can you help me to explain, what are Transaction-Safe Tables and Not Transaction-Safe Tables in MySQL?
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
- 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.
Advantages of Not Transaction-Safe Tables
- 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 perform updates.
With Not Transaction-Safe Tables: If an update fails, no change will be restored as changes that have taken place are permanent.