Misplaced Pages

MyISAM was the default storage engine for the MySQL relational database management system versions prior to 5.5 released in December 2009. It is based on the older ISAM code, but it has many useful extensions.

Filesystem

Each MyISAM table is stored on disk in three files (if it is not partitioned). The files have names that begin with the table name and have an extension to indicate the file type. MySQL uses a .frm file to store the definition of the table, but this file is not a part of the MyISAM engine; instead it is a part of the server. The data file has a .MYD (MYData) extension. The index file has a .MYI (MYIndex) extension. The index file, if lost, can always be recreated by recreating indexes.

Files format depends on the ROW_FORMAT table option. The following formats are available:

• FIXED: Fixed is a format where all data (including variable-length types) have a fixed length. This format is faster to read, and improves corrupted tables repair. If a table contains big variable-length columns (BLOB or TEXT) it cannot use the FIXED format.
• DYNAMIC: Variable-length columns do not have a fixed length size. This format is a bit slower to read but saves some space on disk.
• COMPRESSED: Compressed tables can be created with a dedicated tool while MySQL is not running, and they are read-only. While this usually makes them a non-viable option, the compression rate is generally sensibly higher than alternatives.

MyISAM files do not depend on the system and, since MyISAM is not transactional, their content does not depend on current server workload. Therefore it is possible to copy them between different servers.

Features

MyISAM is optimized for environments with heavy read operations, and few writes, or none at all. A typical area in which one could prefer MyISAM is data warehouse because it involves queries on very big tables, and the update of such tables is done when the database is not in use (usually at night).

The reason MyISAM allows for fast reads is the structure of its indexes: each entry points to a record in the data file, and the pointer is offset from the beginning of the file. This way records can be quickly read, especially when the format is FIXED. Thus, the rows are of constant length. Inserts are easy too because new rows are appended to the end of the data file. However, delete and update operations are more problematic: deletes must leave an empty space, or the rows' offsets would change; the same goes for updates, as the length of the rows becomes shorter; if the update makes the row longer, the row is fragmented. To defragment rows and claim empty space, the OPTIMIZE TABLE command must be executed. Because of this simple mechanism, MyISAM index statistics are usually quite accurate.

However, the simplicity of MyISAM has several drawbacks. The major deficiency of MyISAM is the absence of transactions support. Also, foreign keys are not supported. In normal use cases, InnoDB seems to be faster than MyISAM.

Versions of MySQL 5.5 and greater have switched to the InnoDB engine to ensure referential integrity constraints, and higher concurrency.

MyISAM supports FULLTEXT indexing and OpenGIS data types.

Forks

MariaDB has a storage engine called Aria, which is described as a "crash-safe alternative to MyISAM". However, the MariaDB developers still work on MyISAM code. The major improvement is the "Segmented Key Cache". If it is enabled, MyISAM indices' cache is divided into segments. This improves the concurrency because threads rarely need to lock the entire cache.

In MariaDB, MyISAM also supports virtual columns.

Drizzle does not include MyISAM.

See also

• Comparison of MySQL database engines

Notes

1. "MySQL 5.5 Reference Manual :: 13 Storage Engines :: 13.6 The InnoDB Storage Engine". 2009-05-10. Archived from the original on 2010-11-20. Retrieved 2021-03-16.
2. "MySQL Performance: InnoDB vs MyISAM in 5.6". 2012-11-16. Retrieved 2021-03-16.
3. "Aria FAQ". MariaDB. 2010-08-15. Retrieved 2021-03-16.
4. "Segmented Key Cache". MariaDB. 2010-08-17. Retrieved 2021-03-16.

External links

• MySQL Documentation on MyISAM Storage Engine
• MyISAM's open files limit and table-cache problem explained
• The article about problems which will occur in using MyISAM
• MySQL Engines - MyISAM vs Innodb by Rackspace (Archived on 2015-02-08)
• Convert your MySQL database from MyISAM to InnoDB, and get ready for Drupal 7 at the same time (Archived on 2013-03-08)
• Converting Tables from MyISAM to InnoDB

• Database engines
• MySQL