Selecting the Right Engine - MySQL

When designing MySQL-based applications, you should decide which engine to use for storing your data. If you don’t think about it during the design phase, you will likely face complications later in the process. You might find that the default engine doesn’t provide a feature you need, such as transactions. Or maybe the mix of read and write queries your application generates will require more granular locking than MyISAM’s table locks.

Because you can make the choice on a table-by-table basis, you’ll need a clear idea of how each table is used and the data it stores. Of course, it also helps to have a good understanding of the application as a whole and its potential for growth. Armed with this information, you can begin to make good choices about which table engines can do the job.

While there are many factors that can affect your decision, it usually boils down to just a few considerations: transactions and concurrency, backups, and special features.

When it comes to transactions and concurrency, consider the following guidelines:

• If your application requires transactions and high read/write concurrency, InnoDB is probably your best bet.

• If your application requires transactions but only moderate read/write concurrency, either BDB or InnoDB tables should work fine.

• If your application doesn’t require transactions and issues primarily SELECT or primarily INSERT/UPDATE queries, MyISAM is a good choice. Many web applications fall into this category.

The need to perform regular backups may also influence your table choices. If your server can be shut down at regular intervals for backups, the storage engines are equally easy to deal with. However, if you need to perform online backups in one form or another, the choices become less clear. Chapter 9 deals with this topic in more detail.

Another way of looking at this is simplicity. As you’ll see in Chapter 9, using multiple storage engines increases the complexity of backups and server tuning. You may decide that it’s just easier to use a single storage engine rather than those that are theoretically best.

Finally, you sometimes find that an application relies on particular features or optimizations that are provided by only some of MySQL’s storage engines. For example, not all tables provide a quick answer to queries like the following:

SELECT COUNT(*) FROM mytable

If your application depends on accurate and fast row counts, MyISAM is the answer. InnoDB must actually count up all the rows, but the MyISAM storage engine always knows the exact row count of a table without the need to do any work.

If your application requires referential integrity with foreign keys, you’re limited to just InnoDB tables. Do you need full-text search capabilities? Only MyISAM tables provide it.

Keep this in mind as you read the more detailed information about each table’s features. There will come a time when you find that the feature you really, really need is available only in one table engine. When that happens, you need to either compromise or break a table into multiple tables of different types.

If you've enjoyed what you've seen here, or to get more information, click on the "Buy the book!" graphic. Pick up a copy today! Visit the O'Reilly Network http://www.oreillynet.com for more online content.