The MyISAM table type is an important type in MySQL, and its the default type if you not specify the type option.

MyISAM is based on old ISAM table type, it will replace the ISAM in the future.

The table format of a MyISAM table can be on of these three formats:

Fixed format

When the columns are all fixed length , the format is set to fixed automatically.

Make sure there is no VARCHAR column when create the table.

Create a fixed table:

 
mysql> create table fixedtable ( id int (11) not null primary key, name char(20));
 

And type "show table status" to show the format information of tables. If you are using old version of MySQL, the name may be "Static", not "Fixed".

mysql fixed table format

One of the beneficial of fixed size column is the performance, Fixed table is the fastest among the three MyISAM table formats.

Dynamic

One or more columns are variable length makes the table format be "Dynamic".

 
mysql> create table dynamictable ( id int (11) not null primary key, name varchar(20));
 

mysql myisam table format dynamic

In Dynamic table, each column actually store the length of the column in the table so MySQL can figure out the length of the data quickly.

Dynamic table can save your disk space. But there is a trade off. Since each column just store the data inserted, there is no extra space allocated for further update. If update the column later with longer data, MySQL will not update the data to the original row, because this may move the whole table to allocate space for the updated value.

Instead , MySQL will stored the data in a different location in the datafile. This creates the fragmentation in the datafile.

The fragmentation can be fixed by utility myisamchk.

myisamchk recover

Compressed

Compressed table is read only, and saves a lot of disk space. Use myisampack utility to compress a table:

mysql compressed table

When to use MyISAM

With MyISAM table, the DML statements cause table lock, each write operation will lock the whole table, and all the reads after that are pending until the write complete.

MyISAM is a good choice for high read or high write, but not suit for read/write environment.

MyISAM don't promise data integrity when MySQL instance crashed . The index file may be inconsistent when server crash, when this happens, fix it with REPAIRE TABLE command.