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 OverviewThe 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:
Transaction-safe tables sound good, but nontransaction-safe tables also have their advantages:
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: CREATE TABLE winery ( 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 MyISAMThe 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:
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 InnoDBThe 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:
The disadvantages of InnoDB tables are:
15.6.3.1 Transactions using COMMIT and ROLLBACKTransactions 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.
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: mysql> START TRANSACTION; 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 HeapHeap 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. |