15.2 The MyISAM Storage Engine

 CREATE TABLE t (i INT) ENGINE = MYISAM;

15.2.1 MyISAM Startup Options

The following options to note 'mysqld': mysqld. can be used to change the behavior of 'MyISAM' tables. For additional information, see note server-options::.

MyISAM Option and Variable Reference

Name Cmd-Line Option System Status Var Scope Dynamic File Var Var

bulk_insert_buffer_size

Yes Yes Yes Both Yes

concurrent_insert

Yes Yes Yes Global Yes

delay_key_write

Yes Yes Yes Global Yes

have_rtree_keys

                       Yes                     Global      No
                                                           

key_buffer_size

Yes Yes Yes Global Yes

log-isam

Yes Yes

myisam-block-size

Yes Yes

myisam_data_pointer_size

Yes Yes Yes Global Yes

myisam_max_sort_file_size

Yes Yes Yes Global Yes

myisam_mmap_size

Yes Yes Yes Global No

myisam_recover_options

Yes Yes Yes Global No

myisam_repair_threads

Yes Yes Yes Both Yes

myisam_sort_buffer_size

Yes Yes Yes Both Yes

myisam_stats_method

Yes Yes Yes Both Yes

myisam_use_mmap

Yes Yes Yes Global Yes

tmp_table_size

Yes Yes Yes Both Yes

The following system variables affect the behavior of 'MyISAM' tables. For additional information, see *note server-system-variables::.

• 'bulk_insert_buffer_size'

  The size of the tree cache used in bulk insert optimization.

  Note:

  This is a limit per thread!

• 'delay_key_write=ALL'

  Don't flush key buffers between writes for any 'MyISAM' table.

  Note:

  If you do this, you should not access 'MyISAM' tables from another program (such as from another MySQL server or with *note 'myisamchk': myisamchk.) when the tables are in use. Doing so risks index corruption. Using '--external-locking' does not eliminate this risk.

• 'myisam_max_sort_file_size'

  The maximum size of the temporary file that MySQL is permitted to use while re-creating a 'MyISAM' index (during note 'REPAIR TABLE': repair-table, note 'ALTER TABLE': alter-table, or *note 'LOAD DATA': load-data.). If the file size would be larger than this value, the index is created using the key cache instead, which is slower. The value is given in bytes.

• 'myisam_recover_options=MODE'

  Set the mode for automatic recovery of crashed 'MyISAM' tables.

• 'myisam_sort_buffer_size'

  Set the size of the buffer used when recovering tables.

Automatic recovery is activated if you start *note 'mysqld': mysqld. with the 'myisam_recover_options' system variable set. In this case, when the server opens a 'MyISAM' table, it checks whether the table is marked as crashed or whether the open count variable for the table is not 0 and you are running the server with external locking disabled. If either of these conditions is true, the following happens:

• The server checks the table for errors.

• If the server finds an error, it tries to do a fast table repair (with sorting and without re-creating the data file).

• If the repair fails because of an error in the data file (for example, a duplicate-key error), the server tries again, this time re-creating the data file.

• If the repair still fails, the server tries once more with the old repair option method (write row by row without sorting). This method should be able to repair any type of error and has low disk space requirements.

If the recovery wouldn't be able to recover all rows from previously completed statements and you didn't specify 'FORCE' in the value of the 'myisam_recover_options' system variable, automatic repair aborts with an error message in the error log:

 Error: Couldn't repair table: test.g00pages

If you specify 'FORCE', a warning like this is written instead:

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

If the automatic recovery value includes 'BACKUP', the recovery process creates files with names of the form 'TBL_NAME-DATETIME.BAK'. You should have a 'cron' script that automatically moves these files from the database directories to backup media.

 File: manual.info.tmp, Node: key-space, Next: myisam-table-formats, Prev: myisam-start, Up: myisam-storage-engine

15.2.2 Space Needed for Keys

'MyISAM' tables use B-tree indexes. You can roughly calculate the size for the index file as '(key_length+4)/0.67', summed over all keys. This is for the worst case when all keys are inserted in sorted order and the table does not have any compressed keys.

String indexes are space compressed. If the first index part is a string, it is also prefix compressed. Space compression makes the index file smaller than the worst-case figure if a string column has a lot of trailing space or is a *note 'VARCHAR': char. column that is not always used to the full length. Prefix compression is used on keys that start with a string. Prefix compression helps if there are many strings with an identical prefix.

In 'MyISAM' tables, you can also prefix compress numbers by specifying the 'PACK_KEYS=1' table option when you create the table. Numbers are stored with the high byte first, so this helps when you have many integer keys that have an identical prefix.

 File: manual.info.tmp, Node: myisam-table-formats, Next: myisam-table-problems, Prev: key-space, Up: myisam-storage-engine

15.2.3 MyISAM Table Storage Formats

• Menu:

• static-format:: Static (Fixed-Length) Table Characteristics
• dynamic-format:: Dynamic Table Characteristics

• compressed-format:: Compressed Table Characteristics

'MyISAM' supports three different storage formats. Two of them, fixed and dynamic format, are chosen automatically depending on the type of columns you are using. The third, compressed format, can be created only with the note 'myisampack': myisampack. utility (see note myisampack::).

When you use note 'CREATE TABLE': create-table. or note 'ALTER TABLE': alter-table. for a table that has no note 'BLOB': blob. or note 'TEXT': blob. columns, you can force the table format to 'FIXED' or 'DYNAMIC' with the 'ROW_FORMAT' table option.

See *note create-table::, for information about 'ROW_FORMAT'.

You can decompress (unpack) compressed 'MyISAM' tables using note 'myisamchk': myisamchk. '--unpack'; see note myisamchk::, for more information.

 File: manual.info.tmp, Node: static-format, Next: dynamic-format, Prev: myisam-table-formats, Up: myisam-table-formats

15.2.3.1 Static (Fixed-Length) Table Characteristics ....................................................

Static format is the default for 'MyISAM' tables. It is used when the table contains no variable-length columns (note 'VARCHAR': char, note 'VARBINARY': binary-varbinary, note 'BLOB': blob, or note 'TEXT': blob.). Each row is stored using a fixed number of bytes.

Of the three 'MyISAM' storage formats, static format is the simplest and most secure (least subject to corruption). It is also the fastest of the on-disk formats due to the ease with which rows in the data file can be found on disk: To look up a row based on a row number in the index, multiply the row number by the row length to calculate the row position. Also, when scanning a table, it is very easy to read a constant number of rows with each disk read operation.

The security is evidenced if your computer crashes while the MySQL server is writing to a fixed-format 'MyISAM' file. In this case, *note 'myisamchk': myisamchk. can easily determine where each row starts and ends, so it can usually reclaim all rows except the partially written one. 'MyISAM' table indexes can always be reconstructed based on the data rows.

Note:

Fixed-length row format is only available for tables without note 'BLOB': blob. or note 'TEXT': blob. columns. Creating a table with these columns with an explicit 'ROW_FORMAT' clause does not raise an error or warning; the format specification is ignored.

Static-format tables have these characteristics:

• note 'CHAR': char. and note 'VARCHAR': char. columns are space-padded to the specified column width, although the column type is not altered. note 'BINARY': binary-varbinary. and note 'VARBINARY': binary-varbinary. columns are padded with '0x00' bytes to the column width.

• 'NULL' columns require additional space in the row to record whether their values are 'NULL'. Each 'NULL' column takes one bit extra, rounded up to the nearest byte.

• Very quick.

• Easy to cache.

• Easy to reconstruct after a crash, because rows are located in fixed positions.

• Reorganization is unnecessary unless you delete a huge number of rows and want to return free disk space to the operating system. To do this, use note 'OPTIMIZE TABLE': optimize-table. or note 'myisamchk -r': myisamchk.

• Usually require more disk space than dynamic-format tables.

• The expected row length in bytes for static-sized rows is calculated using the following expression:

    row length = 1
                 + (SUM OF COLUMN LENGTHS)
                 + (NUMBER OF NULL COLUMNS + DELETE_FLAG + 7)/8
                 + (NUMBER OF VARIABLE-LENGTH COLUMNS)

  DELETE_FLAG is 1 for tables with static row format. Static tables use a bit in the row record for a flag that indicates whether the row has been deleted. DELETE_FLAG is 0 for dynamic tables because the flag is stored in the dynamic row header.

 File: manual.info.tmp, Node: dynamic-format, Next: compressed-format, Prev: static-format, Up: myisam-table-formats

15.2.3.2 Dynamic Table Characteristics ......................................

Dynamic storage format is used if a 'MyISAM' table contains any variable-length columns (note 'VARCHAR': char, note 'VARBINARY': binary-varbinary, note 'BLOB': blob, or note 'TEXT': blob.), or if the table was created with the 'ROW_FORMAT=DYNAMIC' table option.

Dynamic format is a little more complex than static format because each row has a header that indicates how long it is. A row can become fragmented (stored in noncontiguous pieces) when it is made longer as a result of an update.

You can use note 'OPTIMIZE TABLE': optimize-table. or note 'myisamchk -r': myisamchk. to defragment a table. If you have fixed-length columns that you access or change frequently in a table that also contains some variable-length columns, it might be a good idea to move the variable-length columns to other tables just to avoid fragmentation.

Dynamic-format tables have these characteristics:

• All string columns are dynamic except those with a length less than four.

• Each row is preceded by a bitmap that indicates which columns contain the empty string (for string columns) or zero (for numeric columns). This does not include columns that contain 'NULL' values. If a string column has a length of zero after trailing space removal, or a numeric column has a value of zero, it is marked in the bitmap and not saved to disk. Nonempty strings are saved as a length byte plus the string contents.

• 'NULL' columns require additional space in the row to record whether their values are 'NULL'. Each 'NULL' column takes one bit extra, rounded up to the nearest byte.

• Much less disk space usually is required than for fixed-length tables.

• Each row uses only as much space as is required. However, if a row becomes larger, it is split into as many pieces as are required, resulting in row fragmentation. For example, if you update a row with information that extends the row length, the row becomes fragmented. In this case, you may have to run note 'OPTIMIZE TABLE': optimize-table. or note 'myisamchk -r': myisamchk. from time to time to improve performance. Use *note 'myisamchk -ei': myisamchk. to obtain table statistics.

• More difficult than static-format tables to reconstruct after a crash, because rows may be fragmented into many pieces and links (fragments) may be missing.

• The expected row length for dynamic-sized rows is calculated using the following expression:

    3
    + (NUMBER OF COLUMNS + 7) / 8
    + (NUMBER OF CHAR COLUMNS)
    + (PACKED SIZE OF NUMERIC COLUMNS)
    + (LENGTH OF STRINGS)
    + (NUMBER OF NULL COLUMNS + 7) / 8

  There is a penalty of 6 bytes for each link. A dynamic row is linked whenever an update causes an enlargement of the row. Each new link is at least 20 bytes, so the next enlargement probably goes in the same link. If not, another link is created. You can find the number of links using note 'myisamchk -ed': myisamchk. All links may be removed with note 'OPTIMIZE TABLE': optimize-table. or *note 'myisamchk -r': myisamchk.

 File: manual.info.tmp, Node: compressed-format, Prev: dynamic-format, Up: myisam-table-formats

15.2.3.3 Compressed Table Characteristics .........................................

Compressed storage format is a read-only format that is generated with the note 'myisampack': myisampack. tool. Compressed tables can be uncompressed with note 'myisamchk': myisamchk.

Compressed tables have the following characteristics:

• Compressed tables take very little disk space. This minimizes disk usage, which is helpful when using slow disks (such as CD-ROMs).

• Each row is compressed separately, so there is very little access overhead. The header for a row takes up one to three bytes depending on the biggest row in the table. Each column is compressed differently. There is usually a different Huffman tree for each column. Some of the compression types are:

  * Suffix space compression.
  
  * Prefix space compression.
  
  * Numbers with a value of zero are stored using one bit.
  
  * If values in an integer column have a small range, the column
    is stored using the smallest possible type.  For example, a
    *note 'BIGINT': integer-types. column (eight bytes) can be
    stored as a *note 'TINYINT': integer-types. column (one byte)
    if all its values are in the range from '-128' to '127'.
  
  * If a column has only a small set of possible values, the data
    type is converted to *note 'ENUM': enum.
  
  * A column may use any combination of the preceding compression
    types.

• Can be used for fixed-length or dynamic-length rows.

Note:

While a compressed table is read only, and you cannot therefore update or add rows in the table, DDL (Data Definition Language) operations are still valid. For example, you may still use 'DROP' to drop the table, and *note 'TRUNCATE TABLE': truncate-table. to empty the table.

 File: manual.info.tmp, Node: myisam-table-problems, Prev: myisam-table-formats, Up: myisam-storage-engine

15.2.4 MyISAM Table Problems

• Menu:

• corrupted-myisam-tables:: Corrupted MyISAM Tables

• myisam-table-close:: Problems from Tables Not Being Closed Properly

The file format that MySQL uses to store data has been extensively tested, but there are always circumstances that may cause database tables to become corrupted. The following discussion describes how this can happen and how to handle it.

 File: manual.info.tmp, Node: corrupted-myisam-tables, Next: myisam-table-close, Prev: myisam-table-problems, Up: myisam-table-problems

15.2.4.1 Corrupted MyISAM Tables ................................

Even though the 'MyISAM' table format is very reliable (all changes to a table made by an SQL statement are written before the statement returns), you can still get corrupted tables if any of the following events occur:

• The *note 'mysqld': mysqld. process is killed in the middle of a write.

• An unexpected computer shutdown occurs (for example, the computer is turned off).

• Hardware failures.

• You are using an external program (such as *note 'myisamchk': myisamchk.) to modify a table that is being modified by the server at the same time.

• A software bug in the MySQL or 'MyISAM' code.

Typical symptoms of a corrupt table are:

• You get the following error while selecting data from the table:

    Incorrect key file for table: '...'. Try to repair it

• Queries don't find rows in the table or return incomplete results.

You can check the health of a 'MyISAM' table using the note 'CHECK TABLE': check-table. statement, and repair a corrupted 'MyISAM' table with note 'REPAIR TABLE': repair-table. When note 'mysqld': mysqld. is not running, you can also check or repair a table with the note 'myisamchk': myisamchk. command. See note check-table::, note repair-table::, and *note myisamchk::.

If your tables become corrupted frequently, you should try to determine why this is happening. The most important thing to know is whether the table became corrupted as a result of an unexpected server exit. You can verify this easily by looking for a recent 'restarted mysqld' message in the error log. If there is such a message, it is likely that table corruption is a result of the server dying. Otherwise, corruption may have occurred during normal operation. This is a bug. You should try to create a reproducible test case that demonstrates the problem. See note crashing::, and note debugging-mysql::.

 File: manual.info.tmp, Node: myisam-table-close, Prev: corrupted-myisam-tables, Up: myisam-table-problems

15.2.4.2 Problems from Tables Not Being Closed Properly .......................................................

Each 'MyISAM' index file ('.MYI' file) has a counter in the header that can be used to check whether a table has been closed properly. If you get the following warning from note 'CHECK TABLE': check-table. or note 'myisamchk': myisamchk, it means that this counter has gone out of sync:

 clients are using or haven't closed the table properly

This warning does not necessarily mean that the table is corrupted, but you should at least check the table.

The counter works as follows:

• The first time a table is updated in MySQL, a counter in the header of the index files is incremented.

• The counter is not changed during further updates.

• When the last instance of a table is closed (because a 'FLUSH TABLES' operation was performed or because there is no room in the table cache), the counter is decremented if the table has been updated at any point.

• When you repair the table or check the table and it is found to be okay, the counter is reset to zero.

• To avoid problems with interaction with other processes that might check the table, the counter is not decremented on close if it was zero.

In other words, the counter can become incorrect only under these conditions:

• A 'MyISAM' table is copied without first issuing *note 'LOCK TABLES': lock-tables. and 'FLUSH TABLES'.

• MySQL has crashed between an update and the final close. (The table may still be okay because MySQL always issues writes for everything between each statement.)

• A table was modified by note 'myisamchk --recover': myisamchk. or note 'myisamchk --update-state': myisamchk. at the same time that it was in use by *note 'mysqld': mysqld.

• Multiple note 'mysqld': mysqld. servers are using the table and one server performed a note 'REPAIR TABLE': repair-table. or note 'CHECK TABLE': check-table. on the table while it was in use by another server. In this setup, it is safe to use note 'CHECK TABLE': check-table, although you might get the warning from other servers. However, *note 'REPAIR TABLE': repair-table. should be avoided because when one server replaces the data file with a new one, this is not known to the other servers.

  In general, it is a bad idea to share a data directory among multiple servers. See *note multiple-servers::, for additional discussion.

 File: manual.info.tmp, Node: memory-storage-engine, Next: csv-storage-engine, Prev: myisam-storage-engine, Up: storage-engines