MyISAM

MyISAM is very fast, and provides the best reading speed of all storage engines available in MySQL. 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.

Handy Backup supports "live" backup and recovery of MyISAM tables that can be done in real time and doesn′t expect you to stop your MySQL server. To learn more, please see the automatic MySQL backup tool feature page for details.

Since MySQL 5.5, default storage engine changed to InnoDB. This doesn′t mean that InnoDB is better than MyISAM: as MySQL developers say, this was done because most users never change default settings, and InnoDB is more of general purpose engine.

At Handy Backup testing lab, we have done a simple comparative test. When there were no applications using MySQL, MyISAM was almost ten times faster than InnoDB:

However, when we put the database under load and the tables started receiving updates, InnoDB showed no significant reduction of speed, but MyISAM slowed down to 0.5 seconds and more.

Creating MyISAM Tables in MySQL 5.5 and Higher

In the last versions of MySQL, to create a table with MyISAM as a storage engine you need to add ENGINE=MyISAM to your CREATE TABLE statements, e.g:

CREATE TABLE table1 (I INT) ENGINE=MyISAM;

Or you can change the default engine back to MyISAM by issuing SET default_storage_engine=MyISAM.