Questions tagged [myisam]

MyISAM is the default storage engine for the MySQL RDBMS since version 3.23 up to version 5.5.5. It provides a simple structure for a table using 3 files:

1. .frm (Format File)
2. .MYD (MyISAM Data)
3. .MYI (MyISAM Index)

It is good to keep in mind that three (3) file handles are required to open one MyISAM table.

MyISAM tables are totally portable to other servers and other operating systems, provided the target server has the following chipset characteristics:

• IEEE floating-point arithmetic
• Two's-complement arithmetic

Different row format options provide for either of the following:

• fixed rows
• variable-length rows
• full table compression.

SQL Commands such as ALTER TABLE tblname ROW_FORMAT=[Fixed|Dynamic] and the utility myisampack make row formats/compression that affect both the disk space and speed of all SQL commands. MyISAM provides for full text indexing on TEXT fields. It provides neither transactional support nor referential integrity (via constraints). However, MyISAM can prevent deadlocking in a low-read, low-write environment because each DML command executed against a MyISAM table performs a full table lock on a first-come, first-served basis.

The startup options for MyISAM can include

• setting up buffers for caching index pages
• bulking load buffering
• table sizing options
• concurrent insert (rapid appending to the table)

Since MyISAM tables can be rendered "crashed" (state where a file handle count >0 is stuck in the header of the MyISAM table) or corrupt if MySQL crashes, command-line utilities are also provided for checksumming and table repair.

Using MyISAM tables can be very useful in read-heavy environments, low-write environments, and as read-only tables in replication slaves.