8.5 The ISAM Storage Engine

8.5 The ISAM Storage Engine

The original storage engine in MySQL was the ISAM engine. It was the only storage engine available until MySQL 3.23, when the improved MyISAM engine was introduced as the default. ISAM now is deprecated. As of MySQL 4.1, it's included in the source but not enabled in binary distributions. It will disappear in MySQL 5.0. Embedded MySQL server versions do not support ISAM tables by default.

Due to the deprecated status of ISAM , and because MyISAM is an improvement over ISAM , you are advised to convert any remaining ISAM tables to MySAM as soon as possible. To convert an ISAM table to a MyISAM table, use an ALTER TABLE statement:

 mysql>  ALTER TABLE   tbl_name   TYPE = MYISAM;  

For more information about MyISAM , see Section 8.1, "The MyISAM Storage Engine."

Each ISAM table is stored on disk in three files. The files have names that begin with the table name and have an extension to indicate the file type. An .frm file stores the table definition. The data file has an .ISD extension. The index file has an .ISM extension.

ISAM uses B-tree indexes.

You can check or repair ISAM tables with the isamchk utility. See Section 4.6.2.7, "Using myisamchk for Crash Recovery."

ISAM has the following properties:

• Compressed and fixed-length keys

• Fixed and dynamic record length

• 16 indexes per table, with 16 key parts per key

• Maximum key length 256 bytes (default)

• Data values are stored in machine format; this is fast, but machine/OS dependent

Many of the properties of MyISAM tables are also true for ISAM tables. However, there are also many differences. The following list describes some of the ways that ISAM is distinct from MyISAM :

• Not binary portable across OS/platforms.

• Can't handle tables larger than 4GB.

• Only supports prefix compression on strings.

• Smaller (more restrictive ) key limits.

• Dynamic tables become more fragmented .

• Doesn't support MERGE tables.

• Tables are checked and repaired with isamchk rather than with myisamchk .

• Tables are compressed with pack_isam rather than with myisampack .

• Cannot be used with the BACKUP TABLE or RESTORE TABLE backup- related statements.

• Cannot be used with the CHECK TABLE , REPAIR TABLE , OPTIMIZE TABLE , or ANALYZE TABLE table-maintenance statements.

• No support for full-text searching or spatial data types.

• No support for multiple character sets per table.

• Indexes cannot be assigned to specific key caches.