MyISAM Overview

The MyISAM storage engine was the default storage engine from MySQL 3.23 until it was replaced 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 (232)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:
  • It is possible to build a MERGE table on the top of one or more MyISAM tables.

Comments

Comments loading...
Content reproduced on this site is the property of its respective owners, and this content is not reviewed in advance by MariaDB. The views, information and opinions expressed by this content do not necessarily represent those of MariaDB or any other party.