Chapter 9. Understanding MySQL s Table Types

Chapter 9. Understanding MySQL's Table Types

In this chapter, we'll review the different table types available to the MySQL database designer. We have generally used InnoDB or MyISAM tables in the examples in this book, but there are others.

Designing a database management system, like many other design tasks, involves many compromises. For example, many database tasks should be done in a transaction-safe way, but providing for this increases time, disk, and memory requirements. The creators of MySQL have deferred some of the compromises to you, the database designer, by offering you a choice of table types. You can choose one of the transaction-safe types, if needed, for your application, or you can choose a higher performance non “transaction-safe type. In any case, you need to know what compromises you are making.

You might hear table types referred to as storage engines. This reflects the fact that some of the table types rely on large amounts of separate source code that manage their own caching, indexing, locking, and disk access. It also reflects the core of a database's purpose: It stores things.

The term transaction or transaction safe will come up a lot in this chapter. It is an important criterion to understand when selecting a table type. Examples so far in the book have involved SQL queries being executed in isolation, but for many applications, this is not what really happens.

Imagine for a moment that you had a database that contained bank account details. If you wanted to transfer $1,000 from one account to another, you would need at least two SQL queries ”one to deduct $1,000 from one account, and one to add $1,000 to the other. It would be a disaster if something (such as a power failure) caused one query to complete, but the other to fail. It would be far preferable in a case like this for the two related queries to either both happen or both fail because the database must be left in a consistent state.

Transaction-safe tables allow you to specify that a set of queries is one indivisible unit of work ”a transaction. The whole transaction should complete, or the database must roll back or return to the same state it was in before the transaction started.

We will cover transactions and the MySQL syntax to use them in Chapter 10, "Using Transactions with InnoDB Tables."

The table types available in MySQL are

• ISAM

• MyISAM

• InnoDB

• BerkeleyDB (BDB)

• MERGE

• HEAP

We will look at each table type in turn , but will devote most space to the most commonly used types: MyISAM and InnoDB. InnoDB and BerkeleyDB are transaction safe. The others (ISAM, MyISAM, MERGE, and HEAP) are not.

We will also cover the special features of MyISAM tables in this chapter, specifically compressed tables and full-text searching. We will spend the whole of Chapter 10 on the special features of the InnoDB storage engine.