MyISAM

MyISAM is a storage engine employed by MySQL database that was used by default prior to MySQL version 5.5 (released in December, 2009). It is based on ISAM (Indexed Sequential Access Method), an indexing algorithm, developed by IBM, that allows retrieving information from large sets of data in a fast way.

Pros and Cons

MyISAM is very fast, and provides the best reading speed of all storage engines available in MySQL.

There are several reasons why that could be an advantage:

• Simplicity;

• Speed;

• Full-text searching;

However, if your application performs simultaneous reading and writing in one table, the engine’s performance falls down dramatically. This is explained by table-level locking: any time an application inserts data or updates a MyISAM table, all other operations are locked out.

Another big critique of MyISAM is the lack of transactions support. Without transactions, you can’t create referential actions, i.e. operations modifying several linked tables at once. For example, say that you have a CUSTOMERS table containing the list of customers, and an ORDERS table containing the list of orders (each order is associated with some customer). The idea of transactions implies that deleting a row in the “parent” table (CUSTOMERS) should delete all rows of the “child” table (ORDERS) that have a matching foreign key, and this operation can’t be stopped in the middle. Transaction-safe storage engines automatically roll back all operations included in a transaction if some of them fail to complete.

MyISAM also supports the following features:

• Support for a true VARCHAR type; a VARCHAR column starts with a length stored in one or two bytes.

• Tables with VARCHAR columns may have fixed or dynamic row length.

• The sum of the lengths of the VARCHAR and CHAR columns in a table may be up to 64KB.

• Arbitrary length UNIQUE constraints.

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

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.