Optimizing MySQL - Table Types (
Page 4 of 6 )
Many MySQL users
might be surprised to know that MySQL actually offers five different table
types, namely BDB, HEAP, ISAM, MERGE, and MyISAM. The BDB table type falls into
a category all by itself, known as transaction-safe. The remaining tables fall
into a second category, named non-transaction-safe. I’ll discuss the details of
each category and those tables that fall under each category in this
section.
Transaction-safe
BDBThe Berkeley DB (BDB) tables are MySQL’s
transaction-capable tables, developed by Sleepycat Software (
http://www.sleepycat.com). These tables
provide functionality long-awaited by MySQL users, that is transaction-control.
Transaction-control is an extremely valuable function in any RDBMS because it
makes it possible to ensure that groups of commands are executed successfully,
nullifying the results brought on by the commands if anything happens to go
wrong during their execution. As you can imagine, transaction-control is very
important in applications such as electronic banking.
Non-transaction-safe
HEAPHEAP tables are the fastest MySQL
table for accessing data. This is because they use a hashed index and are stored
in dynamic memory. One very important point to keep in mind about HEAP tables is
that if either MySQL or your server crashes, the data is
lost!
ISAMISAM tables were the previous MySQL default until
MyISAM was developed. I would recommend not using this table altogether, and
work with the MyISAM table instead.
MERGEMERGE tables are an
interesting new kind of table, made available in Version 3.23.25. A MERGE table
is actually a collection of identical MyISAM tables, merged together as one. The
motive behind merging several identical tables is largely for efficiency
reasons. Doing so can improve speed, searching efficiency, repair efficiency,
and save disk space, depending on what you’re attempting to do.
The MERGE
tables are still considered beta, but should be officially declared stable very
soon.
MyISAMThis is the default MySQL table type. It’s based
on the ISAM code, but with several useful extensions. Here are just a few
reasons why MyISAM tables are good:
MyISAM tables are smaller than ISAM tables, thereby using less resources.
MyISAM tables are binary portable across various platforms
Larger key sizes, larger key limits.
There are many advantages to
MyISAM. Check out http://www.mysql.com/doc/I/S/ISAM.html
for a complete summary of this table.
Specifying Table Type
You specify a table’s type at the time of
creation. In the following example, I’ll show you how to create a HEAP
table:
mysql>CREATE TABLE email_addresses TYPE=HEAP (
->email char(55) NOT NULL,
->name char(30) NOT NULL,
->PRIMARY KEY(email) );
BDB tables require somewhat more configuration. Please refer
to http://www.mysql.com/doc/B/D/BDB_overview.html
for a complete summary of these tables and what must be done in order to take
advantage of them.
Even More Table Types
To make your MySQL administration tasks even more
interesting, the upcoming MySQL 4.0 will offer two new table types, named
Innobase and Gemeni. There isn’t too much information yet available about each,
so stay tuned.
There is so much to be learned about the MySQL table types
that this brief introduction certainly does not do them any justice. For a more
complete introduction, I recommend taking some time to review the information
found within the MySQL documentation (http://www.mysql.com).
