MyISAM is the default storage engine of MySQL until version 5.5. This engine is an implementation of the well defined database storage ISAM architecture and has been available since MySQL 3.x.
Key features
• Non transactional
• Fast insert rate
• Btree based indexes
• Supports FULLTEXT index
• 16k data pages
• 4k index pages
Limitations
• Does not support transactions
• Table level locking on DML and DDL statements
• Not crash safe
• Relies on File System level cache to cache data pages
Important Parameters
• key_buffer_size This buffer is used for holding data from MyISAM indexes. MyISAM can support multiple index caches and enables pinning of specific table indexes per named buffer.
• table_cache This buffer holds information of open tables that are used when running queries. Will applicable to all storage engines, due to additional files, it is important to tune this parameter when there a lot of tables and complex queries.
• bulk_insert_buffer_size This buffer is used for improving INSERT statements with a large number of VALUES or INSERT . . . SELECT as well as LOAD DATA INFILE.
• myisam_recover This parameter defines the storage engine default recovery mode. A recommended value is FORCE, BACKUP
A MyISAM table is represented as 3 separate files in the file system located in the defined data directory for the MySQL instance. These files are:
• table.frm This is the table format definition file
• table.MYD This is the MyISAM data file
• table.MYI This is the MyISAM index file
MyISAM has three different row formats. By default MyISAM will determine whether to use Fixed or Dynamic format based on the column definitions specified in the table. The Fixed row format provides a calculation to determine the offset of the row within the data file, and can provide a small improvement in performance. You can force MyISAM to used Fixed format however this can lead to an increased disk footprint.
