Learning MySQL

Table Types

In the book so far, we’ve used only the default MyISAM table type. There’s a good reason behind this: you very rarely need to make any other choice in small- to medium-size applications because it’s a very fast, reliable table type for most tasks. However, at the time of writing, there are at least nine other choices you can make. This section gives you an insight into these choices, explaining briefly the pros and cons of the alternatives to MyISAM.

You can divide the MySQL table types up into two sets using a few different criteria. The most common division is transaction-safe (TST) versus non-transaction-safe (NTST):

transaction-safe tables (TSTs)

These include the InnoDB and the (no longer supported) Berkeley Database (BDB) table types. TSTs support transactions and have advanced features that allow you safely restore and recover from database failures.

Non-transaction-safe tables (NTSTs)

These include the MyISAM, Merge, and Memory (also called Heap) types described in this section. They’re less advanced than the TSTs, but that isn’t always bad. They’re typically much faster to query because there’s less overhead, and they use much less disk and memory space. They’re also much easier to understand.

We’ve avoided TSTs in this book, because you’re unlikely to need to configure, set parameters for, and use such tables for most applications.

Another consideration when choosing a table type is whether it supports foreign key constraints. With foreign-key support, you can tell ...