MyISAM Overview

The MyISAM storage engine was the default storage engine from MySQL 3.23 until it was replaced as default by InnoDB in MariaDB and MySQL 5.5. Historically, MyISAM is a replacement for the older ISAM engine, removed in MySQL 4.1.

It's a light, non-transactional engine with great performance, is easy to copy between systems and has a small data footprint.

You're encouraged to rather use the Aria storage engine for new applications, which has even better performance in most cases and the goal of being crash-safe.

A MyISAM table is stored in three files on disk. There's a table definition file with an extension of .frm, a data file with the extension .MYD, and an index file with the extension .MYI.

MyISAM features

• Does not support transactions.
• Does not support foreign keys.
• Supports FULLTEXT indexes.
• Supports GIS data types.
• Storage limit of 256TB.
• Maximum of 64 indexes per table.
• Maximum of 32 columns per index.
• Maximum index length of 1000 bytes.
• Limit of (2³²)² (1.844E+19) rows per table.
• Supports large files up to 63-bits in length where the underlying system supports this.
• All data is stored with the low byte first, so all files will still work if copied to other systems or other machines.
• The data file and the index file can be placed on different devices to improve speed.
• Supports table locking, not row locking.
• Supports a key buffer that is segmented in MariaDB.
• Supports concurrent inserts.
• Supports fixed length, dynamic and compressed formats - see MyISAM Storage Formats.
• Numeric index values are stored with the high byte first, which enables more efficient index compression.
• Data values are stored with the low byte first, making it mostly machine and operating system independent. The only exceptions are if a machine doesn't use two's-complement signed integers and the IEEE floating-point format.
• Can be copied between databases or systems with normal system tools, as long as the files are not open on either system. Use FLUSH_TABLES to ensure files are not in use.
• There are a number of tools for working with MyISAM tables. These include:
  • mariadb-check for checking or repairing
  • myisamchk for checking or repairing
  • myisampack for compressing
• It is possible to build a MERGE table on the top of one or more MyISAM tables.