Web Database Applications with PHP and MySQL, 2nd Edition

Table Types

As we've discussed previously, when you create a table, its default type is MyISAM. There are other choices you can make, including Merge, Heap, InnoDB, and BDB (Berkeley DB), and you're free to make different choices for the tables in a single database. This section discusses the choices, the advantages and disadvantages of each table type, and how to use them in practice.

Overview

The main choice you need to make when deciding on a table type is whether you want a transaction-safe (TST) or not-transaction-safe (NTST) table; if you don't make a choice, then the default is MyISAM.

InnoDB and BDB tables are transaction-safe tables, and the MyISAM, Merge, and Heap types are non-transaction-safe tables. We describe the MyISAM, Heap, and InnoDB tables in this section; details of the BDB and Merge (which is a variant of MyISAM) tables, which aren't often used in web database applications, can be found in Section 7 of the MySQL manual.

Transaction-safe tables have the following advantages:

• They look after your data, and you'll be able to restore your data if MySQL or your system crashes (this'll either happen automatically, or you'll be able to do it using a backup you've put aside and using the log that a transaction-safe table stores; see Section 15.7 for more information).

• You can batch together a set of SQL statements as a transaction and treat them as a distinct, atomic operation. This means you can either do all of the statements or none of them. This allows you to easily ...