The MyISAM storage engine is one of more important components of MySQL. MyISAM has many optimizing features. One of other useful features of MyISAM is that it is capable to create table of three formats: static, dynamic, and compressed. MySQL automatically applies the best type (from two of them) depending on the type of columns you are using. The third, compressed tables, can only be created with the myisampack tool.
Static (also known as Fixed-length) is the default format. MySQL automatically uses the static MyISAM variant if the table contains no VARCHAR, BLOB, or TEXT columns.
- All CHAR, NUMERIC, and DECIMAL columns are space-padded to the column width.
- Very quick.
- Easy to cache.
- Easy to reconstruct after a crash, because records are located in fixed positions.
- Doesn't have to be reorganised (with myisamchk) unless a huge number of records are deleted and you want to return free disk space to the operating system.
- Usually requires more disk space than dynamic tables.
This format is used if the table contains any VARCHAR, BLOB, or TEXT columns or if the table was created with ROW_FORMAT=dynamic. Although a MyISAM-dynamic table consumes less space than MyISAM-static. If a field’s contents change, then the location will likely need to be moved, causing fragmentation. As the data set becomes increasingly fragmented, data access performance will suffer accordingly. To fix this problem, three remedies are available:
- Use static data types whenever possible.
- Use OPTIMIZE TABLE statement or myisamchk to defragment a table statement on a regular basis, which defragments tables and recovers space lost over time due to table updates and deletions.
This is a read-only type that is generated with the optional myisampack tool. Sometimes you would need to create tables that are intended as read only throughout the lifetime of your application. In this case, you can significantly reduce their size by converting them into MyISAM-compressed tables using the myisampack utility. In this way, giving certain hardware configurations, performance savings could be significant.