Chapter 14. MyISAM Tables

This chapter describes the MyISAM table type that is supported by the MyISAM storage engine. It covers the following topics:

• Features of the MyISAM storage engine that are especially useful or that are unique to MyISAM tables

• Table processing optimizations you can use with MyISAM tables

• How MySQL handles locking for MyISAM tables

• Backup and recovery options for MyISAM tables

• How to check, repair, and maintain MyISAM tables

Questions on the material in this chapter make up approximately 10% of the exam.

MyISAM tables use the indexed sequential access method for indexing, as did the older ISAM table format. But MyISAM offers better performance and more features than ISAM (for example, you can index columns that include NULL values, and the maximum table size is larger). For these reasons, MyISAM should be preferred over ISAM. Should you want to convert an older ISAM table to MyISAM format, it's very easy; just use ALTER TABLE as follows:

ALTER TABLE table_name TYPE = MyISAM;

MyISAM was introduced in MySQL 3.23.0 and has been the default table type since. Because MyISAM is the default type, the MyISAM storage engine is always available and cannot be disabled.

On disk, MySQL represents an MyISAM table using three files: a format file that stores the definition of the table structure, a datafile that stores the contents of table rows, and an index file that stores any indexes on the table. These files are distinguished from one another by their suffixes. For example, the format, data, and index files for a table named mytable are called mytable.frm, mytable.MYD, and mytable.MYI. MySQL normally stores all three files in the database directory for the database that contains the table, although some variation is possible through the use of symlinking and RAID options.