InnoDB Tables

The next table type we will discuss is InnoDB. InnoDB is a fast, transaction-safe storage engine. Its transaction capabilities are so important that we will spend the entire next chapter discussing how to use them.

InnoDB tables offer

• Transactions. See Chapter 10.

• Row-level locking. This means that only the row we are using in a particular query is unavailable to other users. Most of the other storage engines (with the exception of BDB) use table-level locking ”that is, while one process is updating a table, it is not available to other processes.

• Support for foreign keys. The examples in earlier chapters that include foreign keys would not work with other table types.

• Consistent nonlocking reads in SELECT s. (The idea for this is borrowed from Oracle.)

InnoDB has its own configuration options, its own directory, and its own way of storing data. Whereas MyISAM stores one table per file, InnoDB stores all its tables and indexes in a tablespace, which means that they may be stored over multiple files. This allows InnoDB to have very large tables that will be unaffected by any operating-system file size restrictions. Note, though, that in comparison to MyISAM, an InnoDB table uses a lot more disk space to store the same records.

We will spend the next chapter, Chapter 10, discussing how to use the special features in InnoDB.

InnoDB requires a certain amount of configuration effort. We supplied a basic configuration in Chapter 1, "Installing MySQL," and we will discuss these options in more depth in Chapter 12, "Configuring MySQL."

InnoDB, though developed separately from most of MySQL, is available under the same dual-licensing agreement.

Many of the large, high-profile Web sites that use MySQL, such as Slashdot (www.slashdot.org), Google (www.google.com), and Yahoo! Finance (http://finance.yahoo.com), use the InnoDB engine. It is especially good for dealing with large volumes of data at high speeds in a transaction-safe environment.

InnoDB is one of the fastest transaction-safe systems in the world, but providing this safety involves sacrifices. For most usage patterns, MyISAM will be faster, but the difference will not generally be huge.

InnoDB is made by a separate company, InnoBase Oy, and it has its own Web site. For the most up-to-date information on InnoDB, go to www.innodb.com.