7.1 MyISAM Tables

MyISAM is the default table type in MySQL Version 3.23. It's based on the ISAM code and has a lot of useful extensions.

The index is stored in a file with the `.MYI' (MYIndex) extension, and the data is stored in a file with the `.MYD' (MYData) extension. You can check/repair MyISAM tables with the myisamchk utility. See section 4.4.6.7 Using myisamchk for Crash Recovery. You can compress MyISAM tables with myisampack to take up much less space. See section 4.7.4 myisampack, The MySQL Compressed Read-only Table Generator.

The following is new in MyISAM:

• There is a flag in the MyISAM file that indicates whether the table was closed correctly. If mysqld is started with --myisam-recover, MyISAM tables will automatically be checked and/or repaired on open if the table wasn't closed properly.
• You can INSERT new rows in a table that doesn't have free blocks in the middle of the datafile, at the same time other threads are reading from the table (concurrent insert). A free block can come from an update of a dynamic length row with much data to a row with less data or when deleting rows. When all free blocks are used up, all future inserts will be concurrent again.
• Support for big files (63-bit) on filesystems/operating systems that support big files.
• All data is stored with the low byte first. This makes the data machine and OS independent. The only requirement for binary portability is that the machine uses two's-complement signed integers (as every machine for the last 20 years has) and IEEE floating-point format (also totally dominant among mainstream machines). The only area of machines that may not support binary compatibility are embedded systems (because they sometimes have peculiar processors). There is no big speed penalty in storing data low byte first; the bytes in a table row is normally unaligned and it doesn't take that much more power to read an unaligned byte in order than in reverse order. The actual fetch-column-value code is also not time critical compared to other code.
• All number keys are stored with high byte first to give better index compression.
• Internal handling of one AUTO_INCREMENT column. MyISAM will automatically update this on INSERT/UPDATE. The AUTO_INCREMENT value can be reset with myisamchk. This will make AUTO_INCREMENT columns faster (at least 10%) and old numbers will not be reused as with the old ISAM. Note that when an AUTO_INCREMENT is defined on the end of a multi-part-key the old behaviour is still present.
• When inserted in sorted order (as when you are using an AUTO_INCREMENT column) the key tree will be split so that the high node only contains one key. This will improve the space utilisation in the key tree.
• BLOB and TEXT columns can be indexed.
• NULL values are allowed in indexed columns. This takes 0-1 bytes/key.
• Maximum key length is 500 bytes by default (can be changed by recompiling). In cases of keys longer than 250 bytes, a bigger key block size than the default of 1024 bytes is used for this key.
• Maximum number of keys/table is 32 as default. This can be enlarged to 64 without having to recompile myisamchk.
• myisamchk will mark tables as checked if one runs it with --update-state. myisamchk --fast will only check those tables that don't have this mark.
• myisamchk -a stores statistics for key parts (and not only for whole keys as in ISAM).
• Dynamic size rows will now be much less fragmented when mixing deletes with updates and inserts. This is done by automatically combining adjacent deleted blocks and by extending blocks if the next block is deleted.
• myisampack can pack BLOB and VARCHAR columns.
• You can use put the datafile and index file on different directories to get more speed (with the DATA/INDEX DIRECTORY="path" option to CREATE TABLE). See section 6.5.3 CREATE TABLE Syntax.

MyISAM also supports the following things, which MySQL will be able to use in the near future:

• Support for a true VARCHAR type; a VARCHAR column starts with a length stored in 2 bytes.
• Tables with VARCHAR may have fixed or dynamic record length.
• VARCHAR and CHAR may be up to 64K. All key segments have their own language definition. This will enable MySQL to have different language definitions per column.
• A hashed computed index can be used for UNIQUE. This will allow you to have UNIQUE on any combination of columns in a table. (You can't search on a UNIQUE computed index, however.)

Note that index files are usually much smaller with MyISAM than with ISAM. This means that MyISAM will normally use less system resources than ISAM, but will need more CPU time when inserting data into a compressed index.

The following options to mysqld can be used to change the behaviour of MyISAM tables. See section 4.5.7.4 SHOW VARIABLES.

The automatic recovery is activated if you start mysqld with --myisam-recover=#. See section 4.1.1 mysqld Command-line Options. On open, the table is checked if it's marked as crashed or if the open count variable for the table is not 0 and you are running with --skip-external-locking. If either of the above is true the following happens.

• The table is checked for errors.
• If we found an error, try to do a fast repair (with sorting and without re-creating the datafile) of the table.
• If the repair fails because of an error in the datafile (for example a duplicate key error), we try again, but this time we re-create the datafile.
• If the repair fails, retry once more with the old repair option method (write row by row without sorting) which should be able to repair any type of error with little disk requirements..

If the recover wouldn't be able to recover all rows from a previous completed statement and you didn't specify FORCE as an option to myisam-recover, then the automatic repair will abort with an error message in the error file:

Error: Couldn't repair table: test.g00pages

If you in this case had used the FORCE option you would instead have got a warning in the error file:

Warning: Found 344 of 354 rows when repairing ./test/g00pages

Note that if you run automatic recover with the BACKUP option, you should have a cron script that automatically moves file with names like `tablename-datetime.BAK' from the database directories to a backup media.

See section 4.1.1 mysqld Command-line Options.

Subsections

• 7.1.1 Space Needed for Keys
• 7.1.2 MyISAM Table Formats
• 7.1.2.1 Static (Fixed-length) Table Characteristics
• 7.1.2.2 Dynamic Table Characteristics
• 7.1.2.3 Compressed Table Characteristics
• 7.1.3 MyISAM Table Problems
• 7.1.3.1 Corrupted MyISAM Tables
• 7.1.3.2 Clients is using or hasn't closed the table properly

User Comments

I'd like to understand why the storing of the data is a
machine and OS independent. I know that Intel
Machines stores data in RAM with the high byte first
viceversa Motorola Machines stores data with the
low one. Andrea

Maximum key length is 500 bytes by default (can be
changed by recompiling). In cases of keys longer
than 250 bytes, a bigger key block size than the
default of 1024 bytes is used for this key.

how can i recompile to enlarge maximum key
length???

i want to create table like this...

create table test( field1 text not null,primary key
(field1(1024)) )

that is, i want primary key length to be more than
1024...

answer me... plz...ASAP

i think the documentation for this section should contain an overview on MyISAM tables which can give better idea of their usage. something similar to InnoDB tables overview

"Note that index files are usually much smaller with MyISAM than with ISAM. This means that MyISAM will normally use less system resources than ISAM, but will need more CPU time when inserting data into a compressed index."
Shouldn't the index be uncompressed for MyISAM as that would make it faster. I dont think disk space is much of a concern for me on a server, especially if it is few KB or MB.

Tommy - The data, however, must be read from the the disk. Disk reads are very slow. The less data read from the disk, the better. A few CPU cycles to [de]compress pales in comparison to the extra data being transfered to/from disk.

Add your own comment.