Independent Digital

MyISAM storage engine

MyISAM is the default storage engine since MySQL version 3.23.0. It allows you to store your data in a machine-independent format, enabling you to use the same files on any machine, be it a mac, linux, windows, unix, etc. The table contents are stored in 3 separate files:

1. table_name.frm which contains the table definition
2. table_name.MYD which contains the data
3. table_name.MYI which contains the index

The storage engine can be set on table creation (usually unnecessary as it is the default unless this has been changed on server startup) as follows:

CREATE TABLE table_name (I INT) ENGINE=MYISAM;

(Use TYPE instead of ENGINE for versions prior to 5)

Characteristics of the MyISAM storage engine

• Maximum indexes per table is 64 *
• Maximum columns per index is 16
• Maximum key length is 1000 bytes *
• Can index BLOB and TEXT columns
• NULL values are allowed in indexed columns
• One auto-increment column per table $
• Dynamic sized rows are less fragmented ?
• Allows concurrent inserts
• Can increase speed by placing index and data files in different directories
• Character columns (CHAR, VARCHAR, TEXT) can have different character sets
• UNIQUE can be on for any number of columns
• VARCHAR tables can have fixed or dynamic length records
• VARCHAR and CHAR columns can be up to 64KB in length

* Can change by recompiling

? As it uses adjacent deleted blocks of storage space

$ The values are not reused unless they are in the last column of a multi-column index. Values can be reset with ALTER TABLE or myisamchk

Table storage formats

There are 3 storage formats for the MyISAM storage engine:

1. Fixed length (static)
2. Dynamic length
3. Compressed

Both fixed and dynamic storage is automatically chosen, depending on the contents of the columns. If the column contains variable length data then the dynamic storage option is automatically selected.

Static (fixed-length) table characteristics

This is the default and is used if none of the columns are of variable length (VARCHAR, CHAR, BLOB). The rows are of fixed length. It is the simplest and most secure format (least prone to corruption and generally quite accurate in rebuilding a crashed table with minimum data loss) and also the fastest option. Static tables are:

• Fast
• Easy to cache
• Easy to recover from a crash
• Space hungry - they use more disk space than the dynamic format

Dynamic table characteristics

This format is automatically chosen if the columns contain variable length data (VARCHAR, CHAR, BLOB) or if the table is created with the:

ROW_FORMAT=DYNAMIC

option. One can also use OPTIMIZE TABLE or myisamchk to defragment the table. Dynamic storage:

• Allows variable length columns
• Uses less disk space
• Is difficult to rebuild after a crash

Compressed format characteristics

It is a read-only format generated by the myisampack tool and can be unpacked with myisamchk. The compressed format:

• Uses little disk space
• Compresses records separately (low access overhead)
• Can handle fixed and dynamic length records

Problems with MyISAM tables

Although the MyISAM storage engine is reliable, corruption can occur for the following reasons:

• The Mysqld process was killed during a write
• The computer was turned off
• Hardware failure
• Using an external program while the server was accessing the table
• Software bugs

These are some of the symptoms of a corrupted table:

• An error message is displayed during a query ? ?Incorrect key file for table: ??
• Can?t find rows in table
• Returns incomplete data

You can check the health of a table with CHECK TABLE and repair it with REPAIR TABLE. If mysqld is not running, you can do this with the utility program, myisamchk.

If you get this warning when running either of the above:

Clients are using or haven?t closed the table properly

it may not mean that the table is corrupted but it does warrant that you examine the table. The table index (table_name.MYI) has a counter flag which indicates whether the table was closed properly. This flag could go out of sync for many reasons, some of which are:

• The tables were copied without first issuing FLUSH TABLE or LOCK TABLE
• MySQL crashed between an update and close
• The table was modified while being used by mysqld
• Multiple mysqld servers were using the table and one issued a REPAIR TABLE or CHECK TABLE

Other storage engines

There are a number of other storage engines. Some are listed below and will be covered in future articles:

• MERGE
• MEMORY
• BDB
• InnoDB
• EXAMPLE
• ARCHIVE
• CSV
• FEDERATED
• ISAM

If you are hosting your site on someone elses server then it would be a good idea to check with them first to see which storage engine they support before you create your tables.

Sorry, there are no related posts but check these out

• Web statistics - what do they mean?
• MySql and the top reasons for using it
• Eleven hot tips for making money with google. Tip 3
• 3 Basic Steps to Search Engine Optimization
• Eleven hot tips for making money with google. Tip 7