Web Database Applications With Php And Mysql (2nd Edition) [Electronic resources] نسخه متنی

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:

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 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.

15.6.3.1 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:

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 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.