MySQL MyISAM Storage Engine

MyISAM Storage Engine

MyISAM has three files associated with it, because the three files represent a table they can be simply copied to another server and used, however to avoid corruption you should take down mysql server before copying. The other server must have the same endian format as the source server, so you cannot copy from linux X86 server to a Sparc server for instance.

.frm - The table format file
.MYD - The data file
.MYI - The index file

MyISAM has the following features:

- Non-transactional
- No foreign key support
- FULLTEXT indexes for text matching
- No data cache
- Index caches can be specified by name
- Implements both HASH and BTREE indexes
- Table level locking
- Very fast read activity, suitable for data warehouses
- Compressed data (with myisampack)
- Online backup with mysqlhotcopy
- Maximum of 64 indexes per table

You can set a number of parameters within the my.cnf configuration file that relate to the MyISAM engine:

key_buffer_size
Determines the size of the memory cache used for storing MyISAM indexes, the default is 8MB and the maximum is 4GB

concurrent_insert
Determines the behavior of concurrent inserts
0 - disables concurrent inserts
1 (default) - concurrent inserts with no data gaps are enabled
2 - concurrent inserts with data gaps are enabled

delay_key_write
Delays updating indexes for MyISAM tables until table are closed.
ON (default) - MyISAM tables that have delay_key_write option defined will delay index updates
OFF - disable delayed index writes entirely
ALL - enable delayed index writes entirely

max_write_lock_count
Determines how many writes to a table take precedence over reads, this helps with read starvation due to constant writes to a table, the default is 4294967295 I will cover this more in SQL Extensions section.

preload_buffer_size
Determines the size of the buffer used for index preloading of the key cache, the default is 32KB.

There are three utility programs that can be used with MyISAM tables:

myisamchk - used to analyze, optimize and repair tables, to avoid data corruption you should shutdown mysql when performing any of the below actions.
# check a table
myisamchk /var/lib/mysql/.MYI

# Repair a table
myisamchk -r /var/lib/mysql/.MYI

myisampack - used to create compressed, read-only tables, to avoid data corruption you should shutdown mysql when performing any of the below actions.
# compress a table
myisampack .MYI

# Rebuild the indexes for optimal performance
myisampack --rq --sort-index -analyze .MYI

myisam_ftdump - used to display information about fulltext fields in tables
# First you need the program to analyze, use show create table command
show create table employees\G
# Now you can dump the table, notice the index number at the end,
myisam_ftdump employees 1

We will talk about the Merge Storage engine as it is related to the MyISAM one, it is actually a sort of wrapper around MyISAM tables with the same schemas. All underlying tables can be queried at once by querying the Merged table. This is one way on how to implement partitioning in MySQL, the merged tables use the same buffers and configurations as the underlying MyISAM tables.
Two files are created .frm file contains the table format and the second file .MRG contains the names of the underlying MyISAM tables, as a side note you cannot use the replace statement with merged tables.
The benefits of merged tables are better management of tables and better performance, using merged tables with smaller underlying tables not only speeds up these operations because of the smaller table size but it will allow you to rotate out the table from use by modifying the merge table definition to exclude it while maintenance is occurring.