MyISAM is the non-transactional storage engine for MySQL. It provides high-speed storage and retrieval, as well as fulltext searching capabilities. In addition, it is the default storage engine type for versions of MySQL prior to 5.5.
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:
.frm(Format File).MYD(MyISAM Data)- .
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
- 2's-complements 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 diskspace and speed of all SQL commands. MyISAM provides for fulltext indexing on TEXT fields. It provides neither transactional support nor referential integrity (via constrasints). However, MyISAM can prevent deadlocking in a low-read, low-write environment because each DML commands executed against a MyISAM table performs a full table lock on a first-come, first-serve 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)
Using MyISAM tables can be very useful in read-heavy envinronments, low-write envinronments, and as read-only tables in replication slaves.
