As of MySQL Version 3.23.6, you can choose between three basic table formats: ISAM, HEAP, and MyISAM. Newer MySQL may support additional table types (InnoDB, or BDB), depending on how you compile it.
When you create a new table, you can tell MySQL which table
type it should use for the table. MySQL will always create a
.frm file to hold the table and column definitions. Depending on
the table type, the index and data will be stored in other files.
Note that to use InnoDB tables you have to use at least the innodb_data_file_path startup option. See Section 7.5.2.
The default table type in MySQL is MyISAM. If you are trying to use a table type that is not compiled-in or activated, MySQL will instead create a table of type MyISAM. This is a very useful feature when you want to copy tables between different SQL servers that support different table types (like copying tables to a slave that is optimised for speed by not having transactional tables). This automatic table changing can, however, also be very confusing for new MySQL users. We plan to fix this by introducing warnings in MySQL 4.0 and giving a warning when a table type is automatically changed.
You can convert tables between different types with the ALTER TABLE statement. See Section 6.5.4.
Note that MySQL supports two different kinds of tables: transaction-safe tables (InnoDB and BDB) and non-transaction-safe tables (HEAP, ISAM, MERGE, and MyISAM).
Advantages of transaction-safe tables (TST):
Safer. Even if MySQL crashes or you get hardware problems, you can get your data back, either by automatic recovery or from a backup + the transaction log.
You can combine many statements and accept these all in one go with the COMMIT command.
You can execute ROLLBACK to ignore your changes (if you are not running in auto commit mode).
If an update fails, all your changes will be restored. (With NTST tables all changes that have taken place are permanent)
Advantages of non-transaction-safe tables (NTST):
Much faster, as there is no transaction overhead.
Will use less disk space, as there is no overhead of transactions.
Will use less memory to do updates.
You can combine TST and NTST tables in the same statements to get the best of both worlds.
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. You can compress MyISAM tables with
myisampack to take up much less space. See Section 4.7.4.
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 data file, 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 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 type 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 are 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 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 put the data file 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.
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 lengths.
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 fewer system resources than ISAM, but will need more CPU space time when inserting data into a compressed index.
The following options to mysqld can be used to change the behavior of MyISAM tables. See Section 4.5.6.4.
Option | Description |
|---|---|
|
--myisam-recover=# |
Automatic recovery of crashed tables. |
|
-O myisam_sort_buffer_size=# |
Buffer used when recovering tables. |
|
--delay-key-write-for-all-tables |
Don’t flush key buffers between writes for any MyISAM table. |
|
-O myisam_max_extra_sort_file_size=# |
Used to help MySQL decide when to use the slow but safe key cache index create method. Note that this parameter is given in megabytes! |
|
-O myisam_max_sort_file_size=# |
Don’t use the fast sort index method to create index if the temporary file would get bigger than this. Note that this parameter is given in megabytes! |
|
-O myisam_bulk_insert_tree_size=# |
Size of tree cache used in bulk insert optimisation. Note that this is a limit per thread! |
The automatic recovery is activated if you start mysqld with --myisam-recover=#. See Section 4.1.1. 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 these is true the following happens.
The table is checked for errors.
If an error is found, 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), try again, but this time 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, 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 gotten 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 files with names
like tablename-datetime.BAK from the database directories to backup media.
MySQL can support different index types, but the normal type is ISAM or MyISAM. These use a B-tree index, and 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 we don’t have any compressed keys.)
String indexes are space-compressed. If the first index part is a string, it will also be prefix-compressed. Space compression makes the index file smaller than the preceding figures if the string column has a lot of trailing space or is a VARCHAR 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 PACK_KEYS=1 when you create the table. This helps when you have many integer keys that have an identical prefix when the numbers are stored high byte first.
MyISAM supports 3 different table types. Two of them are chosen automatically depending on the type of columns you are using. The third, compressed tables, can only be created with the myisampack tool.
When you CREATE or ALTER a table you can, for tables that don’t have BLOBs, force the table format to DYNAMIC or FIXED with the ROW_FORMAT=# table option. In the future you will be able to compress/decompress tables by specifying ROW_FORMAT=compressed | default to ALTER TABLE. See Section 6.5.3.
This is the default format. It’s used when the table contains no VARCHAR, BLOB, or TEXT columns.
This is the simplest and most secure format. It is also the fastest of the on-disk formats. The speed comes from the easy way data can be found on disk. When looking up something with an index and static format it is very simple. Just multiply the row number by the row length.
Also, when scanning a table it is very easy to read a constant number of records with each disk read.
The security is evidenced if your computer crashes when writing to a fixed-size MyISAM file, in which case myisamchk can easily figure out where each row starts and ends. So it can usually reclaim all records except the partially written one. Note that in MySQL all indexes can always be reconstructed:
All CHAR, NUMERIC, and DECIMAL columns are space-padded to the column width.
Very quick.
Easy to cache.
Easy to reconstruct after a crash because records are located in fixed positions.
Doesn’t have to be reorganised (with myisamchk) unless a huge number of records are deleted and you want to return free disk space to the operating system.
Usually requires more disk space than dynamic tables.
This format is used if the table contains any VARCHAR, BLOB, or TEXT columns or if the table was created with ROW_FORMAT=dynamic.
This format is a little more complex because each row has to have a header that says how long it is. One record can also end up at more than one location when it is made longer at an update.
You can use OPTIMIZE table or myisamchk to defragment a table. If you have static data that you access/change a lot in the same table as some VARCHAR or BLOB columns, it might be a good idea to move the dynamic columns to other tables just to avoid fragmentation:
All string columns are dynamic (except those with a length less than 4).
Each record is preceded by a bitmap indicating which columns are empty (") for string columns, or zero for numeric columns. (This isn’t the same as columns containing NULL values.) If a string column has a length of zero after removal of trailing spaces, or a numeric column has a value of zero, it is marked in the bitmap and not saved to disk. Non-empty strings are saved as a length byte plus the string contents.
Usually takes much less disk space than fixed-length tables.
Each record uses only as much space as is required. If a record becomes larger, it is split into as many pieces as are required. This results in record fragmentation.
If you update a row with information that extends the row length, the row will be fragmented. In this case, you may have to run myisamchk -r from time to time to get better performance. Use myisamchk -ei tbl_name for some statistics.
Not as easy to reconstruct after a crash because a record may be fragmented into many pieces and a link (fragment) may be missing.
The expected row length for dynamic-sized records is:
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 record is linked whenever an update causes an enlargement of the record. Each new link will be at least 20 bytes, so the next enlargement will probably go in the same link. If not, there will be another link. You may check how many links there are with myisamchk -ed. All links may be removed with myisamchk -r.
This is a read-only type that is generated with the optional myisampack tool (pack_isam for ISAM tables):
All MySQL distributions, even those that existed before MySQL went GPL, can read tables that were compressed with myisampack.
Compressed tables take very little disk space. This minimises disk usage, which is very nice when using slow disks (like CD-ROMs).
Each record is compressed separately (very little access overhead). The header for a record is fixed (1-3 bytes) depending on the biggest record in the table. Each column is compressed differently. Some of the compression types are:
There is usually a different Huffman table for each column.
Suffix space compression.
Prefix space compression.
Numbers with value 0 are stored using 1 bit.
If values in an integer column have a small range, the column is stored using the smallest possible type. For example, a BIGINT column (8 bytes) may be stored as a TINYINT column (1 byte) if all values are in the range 0 to 255.
If a column has only a small set of possible values, the column type is converted to ENUM.
A column may use a combination of the preceding compressions.
Can handle fixed- or dynamic-length records.
Can be uncompressed with myisamchk.
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.
Even if the MyISAM table format is very reliable (all changes to a table are written before the SQL statements returns), you can still get corrupted tables if some of the following things happen:
The mysqld process is killed in the middle of a write.
Unexpected shutdown of the computer (for example, if the computer is turned off).
A hardware error.
You are using an external program (like myisamchk) on a live table.
A software bug in the MySQL or MyISAM code.
Typical symptoms for a corrupt table are:
You get the error Incorrect key file for table: '...'. Try to repair it while selecting data from the table.
Queries don’t find rows in the table or return incomplete data.
You can check if a table is okay with the command CHECK TABLE. See Section 4.4.4.
You can repair a corrupted table with REPAIR TABLE. See Section 4.4.5. You can also repair a table when mysqld is not running with the myisamchk command, myisamchk syntax.
If your tables get corrupted a lot you should try to find the reason for this! See Section A.4.1.
In this case the most important thing to know is if the table got corrupted if the mysqld died (one can easily verify this by checking if there is a recent row restarted mysqld in the mysqld error file). If this isn’t the case, you should try to make a test case of this. See Section D.1.6.
Each MyISAM .MYI file has in the header a counter that can
be used to check if a table has been closed properly.
If you get the following warning from CHECK TABLE or myisamchk:
# client is using or hasn't closed the table properly
this means that this counter has come out of sync. This doesn’t mean that the table is corrupted, but means that you should at least do a check on the table to verify that it’s okay.
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 of a FLUSH or because there isn’t 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 was okay, the counter is reset to 0.
To avoid problems with interaction with other processes that may do a check on the table, the counter is not decremented on close if it was 0.
In other words, the only ways this can go out of sync are:
The MyISAM tables are copied without a LOCK and FLUSH TABLES.
MySQL has crashed between an update and the final close. (Note that the table may still be okay, as MySQL always issues writes for everything between each statement.)
Someone has done a myisamchk --repair or myisamchk --update-state on a table that was in use by mysqld.
Many mysqld servers are using the table and one has done a REPAIR or CHECK of the table while it was in use by another server. In this setup the CHECK is safe to do (even if you will get the warning from other servers), but REPAIR should be avoided, as it currently replaces the data file with a new one, which is not signaled to the other servers.
Get MySQL Reference Manual now with the O’Reilly learning platform.
O’Reilly members experience books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.
