Databases Reference
In-Depth Information
Alternatively, you can use
ALTER TABLE
to adjust the type after it's created. For example,
you could convert the
artist
table to the InnoDB type:
mysql>
ALTER TABLE artist type = InnoDB;
In both examples, you can substitute the alias
ENGINE
for
TYPE
. Of course, much like
every other
ALTER TABLE
statement, the overhead of changing your choice can be high
for large tables.
Note that there are several, rarely used table types we don't discuss at all in this topic.
These include Merge (which is a variant of MyISAM used in large distributed installa-
tions), Example (a nonfunctioning type used to illustrate ideas for programmers), NDB
Cluster (a high-performance type used to partition tables across many computers),
Archive (a high-performance, index-free table type used for very large data collections),
CSV (a table type for working with data stored as comma-separated values in text files),
and Federated (a very new engine—added in MySQL 5.0.3—that's used to store data
in remote databases). You can find out more about these under “Storage Engines and
Table Types” in the MySQL manual.
MyISAM
Before we discuss the alternatives, let's focus on the default MyISAM type. It's an all-
around performer that's designed for typical applications; it supports very fast querying
and has very low overhead for changes to data. It's also very flexible: underneath; it
adapts how it stores data, depending on the structure of the tables you ask it to create.
You'll recall from Chapter 6 that we encouraged you to consider using fixed-length
column types in preference to variable-length types. It was with MyISAM in mind that
we made the recommendation: when you use fixed-length fields, MySQL adapts its
disk-storage structures for fast data access and modification; it's also easier to recover
data from a corrupted table file if it uses fixed-length fields.
One of the key features of MyISAM is its unique way of
locking
tables. In brief, MyISAM
locks are whole-table locks. This means that when you decide to lock a table, other
users can have no access to the table at all. While this seems heavy-handed, it works
fine for most typical applications, and management of the locks in this way costs very
little memory and computational overhead. We'll contrast this with other locking
schemes later when we describe InnoDB and BDB tables.
Unless you can see a good reason, stick with MyISAM while you're learning MySQL.
Memory or Heap
Prior to MySQL 4.1, the Memory table type was known as the Heap table type. Both
keywords are supported, but the MySQL designers now prefer the term
Memory
. We'll
use the new term here, but they're interchangeable.
