GNU Info

(mysql.info)MyISAM

---

Next: MERGE Prev: Table types Up: Table types

---

Enter node , (file) or (file)node

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. Note:
Crash recovery. You can compress `MyISAM' tables with `myisampack' to
take up much less space. Note: myisampack.

The following is new in `MyISAM':

   * There is a flag in the `MyISAM' file that indicates whether or not
     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 data file, at the same time other threads are
     reading from the table (concurrent insert).  An 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 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
     behavior 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
     utilization 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'). Note: CREATE TABLE.

`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 when inserting data into
a compressed index.

The following options to `mysqld' can be used to change the behavior of
`MyISAM' tables. Note: SHOW VARIABLES.

*Option*                      *Meaning*
`--myisam-recover=#'          Automatic recover of crashed tables.
`-O                           Buffer used when recovering tables.
myisam_sort_buffer_size=#'    
`--delay-key-write-for-all-tables'Don't flush key buffers between writes for
                              any MyISAM table
`-O                           Used to help MySQL to decide when to use
myisam_max_extra_sort_file_size=#'the slow but safe key cache index create
                              method. *NOTE* that this parameter is
                              given in megabytes!
`-O                           Don't use the fast sort index method to
myisam_max_sort_file_size=#'  created index if the temporary file would
                              get bigger than this.  *NOTE* that this
                              paramter is given in megabytes!

The automatic recovery is activated if you start `mysqld' with
`--myisam-recover=#'. Note: 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-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 data file) of the table.

   * If the repair fails because of an error in the data file (for
     example a duplicate key error), we try again, but this time we
     re-create the data file.

   * 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.

Note: Command-line options.

Key space

Space needed for keys

MyISAM table formats

MyISAM table formats

MyISAM table problems

MyISAM table problems.