14.5 The MyISAM Storage Engine

14.5.1 MyISAM Startup Options
14.5.2 Space Needed for Keys
14.5.3 MyISAM Table Storage Formats
14.5.4 MyISAM Table Problems

MyISAM is the default storage engine. It is based on the older (and no longer available) ISAM storage engine but has many useful extensions.

Table 14.2 MyISAM Storage Engine Features

Storage limits256TBTransactionsNoLocking granularityTable
MVCCNoGeospatial data type supportYesGeospatial indexing supportYes
B-tree indexesYesT-tree indexesNoHash indexesNo
Full-text search indexesYesClustered indexesNoData cachesNo
Index cachesYesCompressed dataYes[a]Encrypted data[b]Yes
Cluster database supportNoReplication support[c]YesForeign key supportNo
Backup / point-in-time recovery[d]YesQuery cache supportYesUpdate statistics for data dictionaryYes

[a] Compressed MyISAM tables are supported only when using the compressed row format. Tables using the compressed row format with MyISAM are read only.

[b] Implemented in the server (via encryption functions). Data-at-rest tablespace encryption is available in MySQL 5.7 and higher.

[c] Implemented in the server, rather than in the storage engine.

[d] Implemented in the server, rather than in the storage engine.

Each MyISAM 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 format. The data file has an .MYD (MYData) extension. The index file has an .MYI (MYIndex) extension.

To specify explicitly that you want a MyISAM table, indicate that with an ENGINE table option:


Normally, it is unnecessary to use ENGINE to specify the MyISAM storage engine. MyISAM is the default engine unless the default has been changed. To ensure that MyISAM is used in situations where the default might have been changed, include the ENGINE option explicitly.

You can check or repair MyISAM tables with the mysqlcheck client or myisamchk utility. You can also compress MyISAM tables with myisampack to take up much less space. See Section 4.5.3, “mysqlcheck — A Table Maintenance Program”, Section 4.6.3, “myisamchk — MyISAM Table-Maintenance Utility”, and Section 4.6.5, “myisampack — Generate Compressed, Read-Only MyISAM Tables”.

MyISAM tables have the following characteristics:

MyISAM also supports the following features:

Additional Resources