some facts about MyISAM file handling and .MYI file In MySQL

Some facts about MyISAM File handling:

• If a table is never updated, MySQL will never touch the table files, so it would never be
marked as closed or corrupted.

• If a table is marked readonly by the OS, it will only be opened in readonly mode. Any
updates to it will fail.

• When a normal table is opened for reading by a SELECT, MySQL will open it in read/write
mode, but will not write anything to it.

• A table can be closed during one of the following events:
− Out of space in table cache
− Someone executed flush tables
− MySQL was shut down
− flush time expired (which causes an automatic flush-tables to be executed)

• When MySQL opens a table, it checks if the table is clean. If it isn’t and the server was
started with the –myisam-recover option, check the table and try to recover it if it’s
crashed.

Some facts about .MYI file:

• Each key contains the entire contents of all the columns, including trailing spaces in CHAR
columns. There is no front truncation. There is no back truncation.

• For fixed-row tables: The pointer is a fixed-size (4-byte) number which contains an ordinal
row number. The first row is Record #0000. This item is analogous to the ROWID, or
RID (row identifier), which other DBMSs use. For dynamic-row tables: The pointer is an
offset in the ‘.MYD’ file.

• The normal block length is 0×0400 (1024) bytes.

• If a key value is NULL, then the first byte is 0×00 (instead of 001)and that’s all.
Even for a fixed CHAR(3) column, the size of the key value is only 1 byte.

• Initially the junk at the end of a block is filler bytes, value = 0xA5. If MySQL shifts key
values up after a DELETE, the end of the block is not overwritten.

• A normal block is at least 65% full, and typically 80% full. (This is somewhat denser than
the typical B-tree algorithm would cause, it is thus because myisamchk -rq will make blocks
nearly 100% full.)

• There is a pool of free blocks, which increases in size when deletions occur. If all blocks
have the same normal block length (1024), then MySQL will always use the same pool.

Reference : MySQL Internals

3 thoughts on “some facts about MyISAM file handling and .MYI file In MySQL

  1. For compression of MyISAM indexes see http://dev.mysql.com/doc/refman/4.1/en/key-space.html and essentially the same text in later versions including 5.6. There is leading string prefix and trailing space compression in MyISAM indexes when PACK_KEYS is set to its default 1 value. PACK_KEYS is described at http://dev.mysql.com/doc/refman/5.5/en/create-table.html and the equivalent page in other versions.

    James Day, MySQL Senior Principal Support Engineer, Oracle. View are my own, for an official Oracle opinion seek an Oracle PR person.

Leave a Reply