Table types in MySQL: Part 2 – InnoDB Tables

Last month we looked at
the HEAP
table type, a table type which runs entirely in memory. This month we look at
setting up the InnoDB table type, the type of most interest to serious users.
The standard MyISAM table type is ideal for website use, where there are many
reads in comparison to writes, and no transactions. Where these conditions do
not apply (and besides websites, they do not apply often in the database
world), the InnoDB table is likely to be the table type of choice. This article
is aimed at users who are familiar with MySQL, but have only used the default MyISAM
table type.

InnoDB Features

• ACID-compliant
  transactions.

• Full
  referential integrity

• Row-level
  locking

• Tables are
  stored in a tablespace (unlike MyISAM tables where each table is a file)

What is an ACID-compliant transaction?

Database requests
(selects, updates or inserts) happen linearly, one after the other. When one
user has a related set of requests, or there are many users working at the same
time, it is important that the results remain consistent. A transaction
is a set of related SQL statements making up one logical whole, for example, a
sales transaction could consist of updates to customer, sales and product
tables. A transaction is said to be committed when it is completed. To
maintain data integrity, there are four conditions which must apply to
transactions, encapsulated by the acronym ACID. These are Atomicity, Consistency, Isolation and Durability. Very briefly, atomicity means that the
whole transaction must complete, and if this is not possible, none of the
individual statements must be carried out.

Consistency refers to the state of
the data, and the rules to ensure this state is maintained. For example, each
invoice may have to relate to a customer. During a transaction, these rules can
be broken (an invoice could be inserted before the customer), but once the
transaction is complete, the consistency must be restored. Isolation means that
data changed during one transaction cannot be used by another until the first
transaction is complete. Two transactions trying to reduce a bank balance
cannot both work off the same balance (if you had two transactions removing
$100 from a $1000 account, the second must obviously work off a $900 balance,
once the first is complete.) Each transaction works in isolation. Durability
means that once a transaction has been committed, the data remains consistent. Therefore,
if the database crashes in the middle of a transaction, a restore will return
the data to the situation it was in as of the most recently committed
transaction.

Creating InnoDB tables

Being a more complex
table type, InnoDB tables require slightly more administration savvy than the
default MyISAM tables. InnoDB tables are created in a tablespace, which
is not the same as the MyISAM table, that being simply a file on the filesystem.
Assuming you are running a stable version of MySQL 4 (4.0.15 is the latest at
the time of writing), MySQL automatically creates a file called ibdata1
in the data directory (datadir, usually C:MYSQLdata on Windows
systems, or /usr/local/mysql/data or /usr/local/var on Unix-based
systems). This file begins at 10MB, but is auto-extending, meaning it grows
larger (in 8MB blocks) as your data expands. Early versions of MySQL 4 set the
table to be 64MB, but not auto-extending, meaning when that was full, you would
be unable to add more data).

With all this happening
automatically, all that is left to create an InnoDB table is simply the CREATE
statement. Here is an example:

CREATE TABLE innodb_table1(field1 INT, field2 CHAR(10), INDEX (field1)) 
TYPE=INNODB;

The syntax is identical
to what you are probably used to, but with the type specified at the end.