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

BDB
The 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

HEAP
HEAP 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!

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

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

MyISAM
This 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).

Next: Even More Optimization... >>
 

More MySQL Articles
More By W.J. Gilmore

Comments On: Optimizing MySQL

>>> Be the FIRST to comment on this article!