MyISAM supports three different table types:

Static tables

Have a fixed length and are the default format MyISAM uses if no VARCHAR, 

BLOB or TEXT columns are used.

Dynamic tables

Are used as default if VARCHAR, BLOB or TEXT columns are defined in the 

table.

Compressed tables

Are read only tables. Static and dynamic tables can be compressed to use 

very little disk space. To write data to a compressed table, the table has to be 

uncompressed first.

Heap tables

Heap tables use hashed indexes and can only be held in memory. Therefore, 

they are mostly used as temporary tables.

InnoDB tables

In MySQL 4.0 InnoDB is enabled by default. The InnoDB engine is a complete 

separate database back end produced by a finish company called 

Innobase Oy

(

http://wwww.innodb.com

) and placed under MySQL. InnoDB tables are 

transaction safe (ACID compliant) and provide commit, rollback, and crash 

recovery capabilities. It also provides locking on row level and a consistent 

non locking read in 

SELECT

 statements. Furthermore, it supports foreign key 

constraints. Tables and indexes are stored in a table space consisting of several 

files or even raw disk partitions. InnoDB uses B tree indexes to locate data in the 

tables. MyISAM tables can be converted to InnoDB tables by using the 

ALTER 

TABLE ...TYPE=INNODB

 command.

BerkeleyDB (BDB) tables

In MySQL 4.0 BDB table support is provided but not activated by default. 

BerkeleyDB is also a transaction safe storage engine, but has some 

disadvantages compared to InnoDB. BDB supports table locking and locking on 

page level. BDB tables are stored in files with the extension 

.db

. More 

information regarding BerkeleyDB can be found at:

http://www.sleepycat.com

MySQL standard SQL compliance

Up to version 4.0 MySQL meets only the entry level SQL 92 and ODBC level 

0 3.51 standard. But MySQL AB aims toward to support the full SQL 99 standard 

in the next versions. The most important missing compatibilities of MySQL 

default table MyISAM toward the ANSI SQL 92 standard refers to:

Transactions:

36 

MySQL to DB2 UDB Conversion Guide