MyISAM Storage Engine, down but not entirely out

MyISAM is a worthy descendant of ISAM storage database engine, which is now defunct. MyISAM is a much improved version of ISAM with lots of new and exciting extensions. Prior to MySQL 5.5, MyISAM used to be the default storage engine for the MySQL. InnoDB has upstaged MyISAM as the default storage engine now. One of the most important reasons of this fall from the grace for MyISAM was lack of support for the transactions, something which has become extremely important and vital in most of the modern and complex information systems.

Each MyISAM table is spread over three files in the disk. A file with the .frm extension stores the table format, data files carries the extension .myd and .myi extension is meant for Index file.

The biggest advantage of MyISAM tables is their simplicity. It is always strongly advised to a MySQL newbie to start with MyISAM. Syntax to create a new MyISAM table is as follows,

CREATE TABLE table_name (List of columns with datatypes) ENGINE = MYISAM;

If you want to create a MyISAM table then it is very important to use the engine along with the CREATE TABLE query because the default storage engine, these days, is InnoDB. In order to be better able to maintain and manage your MyISAM database, some very useful utilities are provided. You can check your MyISAM tables with the help of mysqlcheck utility; myisamchk tool can be used to repair MyISAM tables and myisampack utility is used to compress MyISAM tables space in order to save the invaluable space resource.

You can enhance the speed of the database system by keeping the data file and index file in separate directories and even in separate machines. If you want to further enhance speed of the I/O operations apart from indexing the numerical columns, you can also index the BLOB (Binary Large OBject) and string type column.

You can have no more than 1.844E+19 rows in an ISAM database table. In a MyISAM table, you can not have more than 64 indexes and each index can not be attached to more than 16 columns. In order to facilitate better index compression, a high byte value is first stored in case of numerical key values.

Though MyISAM is very simple, easy and for people intending to acquire a familiarity with the MySQL, this is the perfect storage engine. But in case of production environments, where complex transactions are involved and multi user concurrency is also required to be handled, one should also always prefer InnoDB, as it effectively handles all these complexities.

Author: ucavik


»