15.6 Table Types

As we've discussed previously, when you create a table, its default type is MyISAM. There are other choices you can make, including Merge, Heap, InnoDB, and BDB (Berkeley DB), and you're free to make different choices for the tables in a single database. This section discusses the choices, the advantages and disadvantages of each table type, and how to use them in practice.

15.6.1 Overview

The main choice you need to make when deciding on a table type is whether you want a transaction-safe (TST) or not-transaction-safe (NTST) table; if you don't make a choice, then the default is MyISAM.

InnoDB and BDB tables are transaction-safe tables, and the MyISAM, Merge, and Heap types are non-transaction-safe tables. We describe the MyISAM, Heap, and InnoDB tables in this section; details of the BDB and Merge (which is a variant of MyISAM) tables, which aren't often used in web database applications, can be found in Section 7 of the MySQL manual.

Transaction-safe tables have the following advantages:

  • They look after your data, and you'll be able to restore your data if MySQL or your system crashes (this'll either happen automatically, or you'll be able to do it using a backup you've put aside and using the log that a transaction-safe table stores; see Section 15.7 for more information).

  • You can batch together a set of SQL statements as a transaction and treat them as a distinct, atomic operation. This means you can either do all of the statements or none of them. This allows you to easily rollback out of a situation where the user presses the Cancel button, a step fails, or the user doesn't complete their interaction with the web database application. We show you an example later in this section.

Transaction-safe tables sound good, but nontransaction-safe tables also have their advantages:

  • They're much faster, because looking after and managing data in a transaction-safe table has a substantial overhead.

  • They use less resources (both disk space and memory) because of the reduced overhead.

  • They're conceptually simpler: compare the size of the manual entries in the MySQL manual!

In general, you don't need transaction-safe tables in web database applications. Commit and rollback processing is useful, but it's less interesting in the stateless HTTP environment, in which operations aren't usually complex and need to be as independent as possible. For most practical purposes in web database applications, transactional processing isn't required. If it is required, it's normally part of the logic of your PHP scripts.

After you've decided to use a table type, you need to create or change a table to have that type. When you create a table, you can optionally add the table type you require (it defaults to MyISAM). For example, to make the winery table an InnoDB table type, you can create it as follows:


  winery_id int(4) NOT NULL,

  winery_name varchar(100) NOT NULL,

  region_id int(4) NOT NULL,

  PRIMARY KEY (winery_id),

  KEY name (winery_name),

  KEY region (region_id)

) type=InnoDB;

The MyISAM, Merge, Heap, and InnoDB table types are available as choices in all MySQL 4 installations. If you want BDB support, you need to compile it in. If you try and create a table of a type that isn't supported by your installation, MySQL will silently create a MyISAM table instead; this was done to improve portability of databases between installations but can be annoying.

You can also change a table's type after it has been created using the ALTER TABLE statement described previously in this chapter. For example, to change the winery table to an InnoDB table, type:

ALTER TABLE winery type=InnoDB;

15.6.2 MyISAM

The MyISAM table type is the default. It's nontransaction-safe but is instead designed for very fast querying, and also has low overheads for data modifications that are common in web database applications. What's more, it has three underlying storage methods that allow it to adapt to different table designs and requirements. Most of the time, it's the ideal tool for a web database application.

One of the key features of MyISAM is that it has table locking. We discuss locking in detail in Chapter 8, but it's important only in situations where there's more than one simultaneous user (concurrency), and one user needs to read data from a database and then use that data in modifying the database (or the user writes data and then reads the same data back). Table locking means that one or more tables are wholly or partially unavailable to other users in only those situations.

Table locking works particularly well for most web database applications that have concurrency issues. This is because:

  • Locks are needed only for a short time. DELETE and UPDATE operations are on specific rows (most often accessed by the primary key value) and the rows are accessed through an index, so the commands are fast.

  • Locks are used infrequently. There are usually many more read operations than write operations, and concurrency issues are rare anyway.

  • Table locking is the only option for some operations. Examples include GROUP BY operations, updates of sets of rows, and reading in most rows in a table.

  • MyISAM tables automatically manage concurrent updates in a clever way. When a mix of read and write operations occur on a MyISAM table, MySQL automatically creates a new copy of the data to be changed and carries out the write operation on the copy. Other SELECT statements being run by other users read the unchanged data and, when they are no longer reading the unchanged data, the modified copy is written back to the database. This technique is known as data versioning .

Although table locking sounds heavy handed, it's typically beneficial in a web database application. However, there are advantages and disadvantages in comparison with other finer-grain locking paradigms, and these are discussed in "InnoDB."

Technical details of MyISAM tables and indexes are discussed in Section 7.1 of the MySQL manual. However, one major point is that MyISAM is clever in its choice of disk storage structure. If your table has only fixed-length attributes (because it doesn't use varchar, blob, or text types), MySQL stores rows in a fixed-length format on disk. This makes access to the data extremely fast, and it'll stay that way even if the data changes frequently. What's more, it's easy to recover in the event of a crash.

If your table has variable-length attributes, MyISAM automatically switches to a dynamic table, which is slower but more compact on disk. There's also a third type, a compressed table, that's read-only, fast, and compact, and can be created using the myisampack tool by an administrator; we don't discuss this further here.

15.6.3 InnoDB

The InnoDB table type is a general-purpose alternative to MyISAM. It's transaction-safe, enforces FOREIGN KEY constraints, and offers commit, rollback, data recovery, and row-level locking. It's a powerful table type, but its benefits usually don't outweigh its drawbacks for a web database application. Most of the time, you can stick with MyISAM.

In detail, the advantages of InnoDB are:

  • COMMIT and ROLLBACK support. This allows you to treat a set of SQL statements as one block, and to ensure either all or none of them affect the database. We show you an example later in this section.

  • Flexible, fast, row-level locking. This means InnoDB locks affect only the rows being queried and updated, rather than the whole table as in MyISAM. This works better than MyISAM's table locking when many users are writing to a database concurrently, or locks are held for a long time.

  • FOREIGN KEY constraint support. This is a tool that protects the structure and integrity of your data, ensuring that you can't add rows to one table unless there's a valid matching row in another table. For example, you could use this to ensure that you can't create a row in the orders table that has a cust_id value for which there isn't a matching row in the customer table. Also, it'll ensure you can't delete a customer if they still have an order in the orders table.

    For most web database applications, foreign key constraints are unnecessary. They add overhead to the data modification process, and your application logic in PHP should implement the controls and manage the constraints anyway. Writing data with PHP is discussed in Chapter 8.

    In MySQL, if a table type doesn't support foreign keys constraints, then the FOREIGN KEY constraint is silently ignored.

  • Checkpoints for recovery. A checkpoint is a log file entry that allows an InnoDB table to recover quickly in the event of database or system failure.

  • Flexible transaction isolation. In Chapter 9, we describe common concurrency problems. You can relax the InnoDB transaction model so that queries are faster, but all of the transaction properties aren't enforced (and so there is less guarantee of correct results within a transaction).

  • Flexible indexing. InnoDB decides when a table needs a fast hash index (similar to that used in the Heap table type discussed next) and creates one automatically.

The disadvantages of InnoDB tables are:

  • They require much more space than MyISAM tables.

  • Foreign key constraints, if used, add overhead to table management.

  • Data versioning and transactions add overhead to table management.

  • They are much slower than MyISAM for most web database applications.

  • They can lead to high memory requirements to manage large numbers of locks used in row locking.

  • Locking can cause relatively slow performance, because row locking involves much more locking and unlocking activity. In particular, operations that require locks on a whole table, such as GROUP BY operations, are very slow.

  • Indexes are slow to build when they're added after a table has been created. Indexes should therefore be created when the data is bulk-loaded. Transactions using COMMIT and ROLLBACK

Transactions allow you to treat a series of SQL statements as an indivisible group: either all of the statements in the group succeed and affect the database, or none do. Transactions can only be used with transaction-safe table types such as InnoDB.

By default, InnoDB transactions offer repeatable reads. As discussed in Chapter 8, this allows you to reread data from a database and get consistent results, regardless of what data other users change. For example, if you check the amount of stock available in the inventory using a SELECT that's part of a transaction, and another user adds more stock through an update, you'll still see the original value if you re-run the SELECT until you issue either a ROLLBACK or COMMIT statement. You can learn about other transaction isolation options in Section 6.7.4 of the MySQL manual.

When using transactions, writes to the database don't occur until you issue a COMMIT.[1] Therefore, other users can't see any changes you're making until the end of the transaction. However, you can see the changes as if they've been written: if you change the database and then read your change as part of a transaction, the database will appear to you as if it's changed.

[1] Or another statement that implicitly ends a transaction such as START TRANSACTION, ALTER TABLE, DROP DATABASE, LOCK TABLES, UNLOCK TABLES, DROP TABLE, CREATE INDEX, and other major database structural changes or transaction-related statements.

There are two methods you can use to work with transactions. The first is to use the START TRANSACTION, COMMIT, and ROLLBACK statements. The second is to turn off MySQL's auto-commit feature, and to manually issue COMMIT or ROLLBACK statements as required.

Consider an example of using START TRANSACTION and COMMIT that's entered into the MySQL command interpreter:


Query OK, 0 rows affected (0.01 sec)

mysql> SELECT SUM(on_hand) FROM inventory;


| SUM(on_hand) |


|       513275 |


1 row in set (0.01 sec)

mysql> INSERT INTO report VALUES (1, "December 2004", 513275);

Query OK, 1 row affected (0.00 sec)

mysql> COMMIT;

Query OK, 0 rows affected (0.00 sec)

In this example, a transaction is started and then a value is read from the inventory table. This value is then used to update an InnoDB report table that stores a primary key value, a description of the report, and the total from the previous query. After that, the transaction is committed, which writes the insert to the database.

If you don't want to proceed with changes to the database, you can replace the COMMIT with ROLLBACK in the previous example as follows:

mysql> INSERT INTO report VALUES (1, "December 2004", 513275);

Query OK, 1 row affected (0.00 sec)

mysql> ROLLBACK;

Query OK, 0 rows affected (0.01 sec)

After the rollback is complete, all statements issued since the most-recent START TRANSACTION are undone. In both our previous examples, there's no need to LOCK TABLES because your transaction is correctly isolated from other transactions.

The second method you can use to work with transactions is to disable the auto-commit mode. You do this as follows:

mysql> set autocommit=0;

Query OK, 0 rows affected (0.00 sec)

With auto-commit disabled, data isn't written to the database until you issue a COMMIT statement. If you issue a ROLLBACK, all writes to the database are rolled-back until immediately after the last COMMIT statement. You can turn auto-commit on by issuing:

mysql> set autocommit=1;

Query OK, 0 rows affected (0.00 sec)

When auto-commit is on and you're not in a transaction, MySQL behaves as though it does not have transaction support.

If you use transaction statements with a table type that doesn't support them, your transaction statements will be silently ignored. This applies to the Heap and MyISAM table types we discuss in this section.

15.6.4 Heap

Heap tables are used for special purposes and have significant limitations. They're stored in memory (not on disk) and use a hash index to access the rows. They're ideal for temporary tables or for frequently used lookup tables. However, they have several limitations that prevent them being used for a wide range of purposes. The most significant limitation is that when MySQL is shutdown and restarted, the data in your Heap tables is not loaded.

Hash indexing is the fastest search method when you want to find an exact match using = or <=>, but it can't be used if you want to find values using the other comparison operators. Moreover, you can't use the hash index to do an ORDER BY. Therefore, a Heap table's primary use is as a lookup table where you want to find a row associated with a key value.

Heap tables are limited in the features they support. They don't support TEXT or BLOB types, and they don't support MySQL's auto_increment feature. Of course, because they're memory-resident they take up memory just by existing, and should therefore be restricted to small tables and used sparingly. Last of all, they offer locking only on the table level.

If MySQL crashes, you'll lose the data in any Heap tables since they're never written to disk. In addition, the data in Heap tables is only kept while the MySQL server is running. When you stop and restart MySQL, you need to manually reload your Heap tables with data. To do this, you can follow the steps in "Restore" for only your Heap tables.