8 MySQL table types

As of MySQL Version 3.23.6, you can choose between three basic table formats. 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.

The default table type in MySQL is MyISAM. If you are trying to use a table type that is not incompiled or activated, MySQL will instead create a table of type MyISAM.

You can convert tables between different types with the ALTER TABLE statement. See section 7.8 ALTER TABLE Syntax.

Note that MySQL supports two different kind of tables. Transactions safe tables (BDB) and not transaction safe tables (ISAM, MERGE, MyISAM and HEAP).

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 has taken place are permanent)

Advantages of not transaction safe tables (NTST):

• Much faster as there is no transcation 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.

8.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 file with the .MYD (MYData) extension. You can check/repair MyISAM tables with the myisamchk utility. See section 15.4 Using myisamchk for crash recovery.

The following is new in MyISAM:

• If mysqld is started with --myisam-recover, MyISAM tables will automaticly be repaired on open if the table wasn't closed properly.
• You can INSERT new rows in a table without deleted rows, while other threads are reading from the table.
• 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 a 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 now 500 bytes by default. 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 enlarged to 32 as default. This can be enlarged to 64 without having to recompile myisamchk.
• There is a flag in the MyISAM file that indicates whether or not the table was closed correctly. This will soon be used for automatic repair in the MySQL server.
• 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.

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 compressed index.

The following options to mysqld can be used to change the behavior of MyISAM tables:

The automatic recovery is activated if you start mysqld with --myisam-recover=#. See section 4.16.4 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 recreating 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 recreate 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 automaticly moves file with names like `tablename-datetime.BAK' from the database directories to a backup media.

See section 4.16.4 Command-line Options.

8.1.1 Space needed for keys

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 above 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 which have an identical prefix when the numbers are stored high-byte first.

8.1.2 MyISAM table formats

MyISAM supports 3 different table types. 2 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.

8.1.2.1 Static (Fixed-length) table characteristics

This is the default format. It's used when the table contains no VARCHAR, BLOB or TEXT columns.

This format 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 comes from if your computer crashes when writing to a fixed size MyISAM file, 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 reorganized (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.

8.1.2.2 Dynamic table characteristics

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 litte 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 bit map 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 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.

8.1.2.3 Compressed table characteristics

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 minimizes 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 above compressions.
• Can handle fixed or dynamic length records, but not BLOB or TEXT columns.
• Can be uncompressed with myisamchk.

8.2 MERGE tables

MERGE tables are new in MySQL Version 3.23.25; The code is still in alpha, but should stabilize soon! The one thing that is currently missing is a way from the SQL prompt to say which tables are part of the MERGE table.

A MERGE table is a collection of identical MyISAM tables that can be used as one. You can only SELECT, DELETE and UPDATE from the collection of tables. If you DROP the MERGE table, you are only dropping the MERGE specification.

With identical tables we mean that all tables are created with identical column information. Some of the tables can be compressed with myisampack. See section 14.8 The MySQL compressed read-only table generator..

When you create a MERGE table, you will get a .frm table definition file and a .MRG table list file. The .MRG just contains a list of the index files (.MYI files) that should be used as one.

For the moment you need to have SELECT, UPDATE and DELETE privileges on the tables you map to a MERGE table.

MERGE tables can help you solve the following problems:

• Easily manage a set of log tables; For example you can put data from different months into separate files, compress some of them with myisampack and then create a MERGE to use these as one.
• Give you more speed; You can split a big read-only table based on some criteria and then put the different table part on different disks. A MERGE table on this could be much faster than using the big table. (You can of course also use a RAID to get the same kind of benefits).
• Do more efficient searches: If you know exactly what you are looking after, you can search in just one of the split tables for some queries and use MERGE table for others. You can even have many different MERGE tables active, which possible overlapping files.
• More efficient repairs; It's easier to repair the individual files that are mapped to a MERGE file than trying to repair a real big file.
• Instant mapping of many files as one; A MERGE table uses the index of the individual tables; It doesn't need an index of its one. This makes MERGE table collections VERY fast to make or remap.
• If you have a set of tables which you join to a big tables on demand or batch, you should instead create a MERGE table on them on demand. This is much faster and will save a lot of disk space.
• Go around the file size limit for the operating system.

The disadvantages with MERGE tables are:

• You can't use INSERT on MERGE tables, as MySQL can't know in which of the tables we should insert the row.
• You can only use identical MyISAM tables for a MERGE table.
• MERGE tables uses more file descriptors: If you are using a MERGE that maps over 10 tables and 10 users are using this, you are using 10*10 + 10 file descriptors. (10 data files for 10 users and 10 shared index files).
• Key reads are slower; When you do a read on a key, the MERGE handler will need to issue a read on all underlying tables to check which on most closely matches the given key. If you then do a 'read-next' then the merge table handler will need to search the read buffers to find the next key; Only when one key buffer is used up, the handler will need to read the next key block. This makes MERGE keys much slower on eq_ref searches, but not much slower on ref searches. See section 7.29 EXPLAIN syntax (Get information about a SELECT).
• You can't yet easily map the MERGE table from withing MySQL.

The following example shows you how to use MERGE tables:

CREATE TABLE t1 (a INT AUTO_INCREMENT PRIMARY KEY, message CHAR(20));
CREATE TABLE t2 (a INT AUTO_INCREMENT PRIMARY KEY, message CHAR(20));
INSERT INTO t1 (message) VALUES ("Testing"),("table"),("t1");
INSERT INTO t2 (message) VALUES ("Testing"),("table"),("t2");
CREATE TABLE total (a INT NOT NULL, message CHAR(20), KEY(a)) TYPE=MERGE UNION=(t1,t2);

Note that we didn't create an UNIQUE or PRIMARY KEY in the total table as the key isn't going to be unique in the total table.

Note that you can also manipulate the .MRG file directly from the outside of the MySQL server:

shell> cd /mysql-data-directory/current-database
shell> ls -1 t1.MYI t2.MYI > total.MRG

Now you can do things like:

mysql> select * from total;
+---+---------+
| a | message |
+---+---------+
| 1 | Testing |
| 2 | table   |
| 3 | t1      |
| 1 | Testing |
| 2 | table   |
| 3 | t2      |
+---+---------+

To remap a MERGE table you must either DROP it and recreate it, use ALTER TABLE with a new UNION specification or change the .MRG file and issue a FLUSH TABLE on the MERGE table and all underlying tables to force the handler to read the new definition file.

8.3 ISAM tables

You can also use the deprecated ISAM table type. This will disappear rather soon because MyISAM is a better implementation of the same thing. ISAM uses a B-tree index. The index is stored in a file with the .ISM extension and the data is stored in file with the .ISD extension. You can check/repair ISAM tables with the isamchk utility. See section 15.4 Using myisamchk for crash recovery.

ISAM has the following features/properties:

• Compressed and fixed length keys
• Fixed and dynamic record length
• 16 keys with 16 key parts / key
• Max key length 256 (default)
• Data is stored in machine format; this is fast, but is machine/OS dependent.

Most of the things for MyISAM tables are also true for ISAM tables. See section 8.1 MyISAM tables. The major differences compared to MyISAM tables are:

• ISAM tables are not binary portable across OS/Platforms.
• Can't handle tables > 4G.
• Only support prefix compression on strings
• Smaller key limits.
• Dynamic tables gets more fragmented.
• Tables are compressed with pack_isam rather than with myisampack.

8.4 HEAP tables

HEAP tables use a hashed index and are stored in memory. This makes them very fast, but if MySQL crashes you will lose all data stored in them. HEAP is very useful for temporary tables!

The MySQL internal HEAP tables uses 100% dynamic hashing without overflow areas. There is no extra space needed for free lists. HEAP tables also don't have problems with delete + inserts, which normally is common with hashed tables..

mysql> CREATE TABLE test TYPE=HEAP SELECT ip,SUM(downloads) as down
        FROM log_table GROUP BY ip;
mysql> SELECT COUNT(ip),AVG(down) FROM test;
mysql> DROP TABLE test;

Here are some things you should consider when you use HEAP tables:

• You should always use specify MAX_ROWS in the CREATE statement to ensure that you accidently do not use all memory.
• Indexes will only be used with = and <=> (but are VERY fast).
• HEAP tables can only use whole keys to search for a row; compare this to MyISAM tables where any prefix of the key can be used to find rows.
• HEAP tables use a fixed record length format.
• HEAP doesn't support BLOB/TEXT columns.
• HEAP doesn't support AUTO_INCREMENT columns.
• HEAP doesn't support an index on a NULL column.
• You can have non-unique keys in a HEAP table (this isn't common for hashed tables).
• HEAP tables are shared between all clients (just like any other table).
• You can't search for the next entry in order (that is to use the index to do a ORDER BY).
• Data for HEAP tables are allocated in small blocks. The tables are 100% dynamic (on inserting). No overflow areas and no extra key space is needed. Deleted rows are put in a linked list and are reused when you insert new data into the table.
• You need enough extra memory for all HEAP tables that you want to use at the same time.
• To free memory, you should execute DELETE FROM heap_table, TRUNCATE heap_table or DROP TABLE heap_table.
• MySQL cannot find out how approximately many rows there are between two values (this is used by the range optimizer to decide which index to use). This may affect some queries if you change a MyISAM table to a HEAP table.
• To ensure that you accidentally don't do anything stupid, you can't create HEAP tables bigger than max_heap_table_size.

Memory needed for one row in a HEAP table is:

SUM_OVER_ALL_KEYS(max_length_of_key + sizeof(char*) * 2)
+ ALIGN(length_of_row+1, sizeof(char*))

sizeof(char*) is 4 on 32 bit machines and 8 on 64 bit machines.

8.5 BDB or Berkeley_db tables

Berkeley DB (http://www.sleepycat.com) has provided MySQL with a transaction safe table handler. This will survive crashes and also provides COMMIT and ROLLBACK on transactions. In order to build MySQL Version 3.23.x (BDB support first appeared in Version 3.23.15) with support for BDB tables, you will need Berkeley DB 3.1.14 or newer which can be downloaded from http://www.mysql.com/downloads/mysql-3.23.html; or also from Sleepycat's download page at http://www.sleepycat.com/download.html.

To install Berkeley DB first uncompress the BDB distribution and follow the instructions in the README provided in the distiribution directory. Basically what you need to do is:

cd build_[your_os]
../dist/configure
make
make install

Please refer to the manual provided by BDB distribution for more/updated information.

After this you need to configure your MySQL with --with-berkeley-db=DIR. The directory is the one where you installed BDB binaries with make install. (Usually it is /usr/local/BerkeleyDB.3.1/) You can give additional options to MySQL configure, --with-berkeley-db-includes=DIR and --with-berkeley-db-libs=DIR, if the BDB includes and/or libs directory is not under the first directory, by default they are. Then complete the MySQL installation as normal.

Even if Berkeley DB is in itself very tested and reliably, the MySQL interface is still very alpha, but we are actively improving and optimizing it to get it this stable real soon.

If you are running with AUTOCOMMIT=0 then your changes in BDB tables will not be updated until you execute COMMIT. Instead of commit you can execute ROLLBACK to forget your changes. See section 7.31 BEGIN/COMMIT/ROLLBACK syntax.

The following options to mysqld can be used to change the behavior of BDB tables:

If you use --skip-bdb, MySQL will not initialize the Berkeley DB library and this will save a lot of memory. You can of course not use BDB tables if you are using this option.

Normally you should start mysqld with --bdb-recover if you intend to use BDB tables. This may however give you problems when you try to start mysqld if the BDB log files are corrupted. See section 4.16.2 Problems Starting the MySQL Server.

Some characteristic of BDB tables:

• MySQL requires a PRIMARY KEY in each BDB table to be able to refer to previously read rows; If you don't create on, MySQL will create an maintain a hidden PRIMARY KEY for you. The hidden key has a length of 5 bytes and is incremented for each insert attempt.
• If all columns you access in a BDB tables is part of the same index or part of the primary key then MySQL can execute the query without having to access the actual row. In a MyISAM table the above holds only if the columns are part of the same index.
• The PRIMARY KEY will be faster than any other key, as the PRIMARY KEY is stored together with the row data. As the other keys are stored as the key data + the PRIMARY KEY, its important to keep the PRIMARY KEY as short as possible to save disk and get better speed.
• LOCK TABLES works on BDB tables as with other tables. If you don't use LOCK TABLE, MYSQL will issue an internal multiple write lock on the table to ensure that the table will be properly locked if one another thread issues a table lock.
• ALTER TABLE doesn't yet work on BDB tables.
• Internal locking in BDB tables are done on page level.
• SELECT COUNT(*) FROM table_name is slow as BDB tables doesn't maintain a count of the number of rows in the table.
• Scanning is slower than with MyISAM tables as one has data in BDB tables is stored in B-trees and not in a separate data file.
• One must in the application always be prepared to handle the case that any change of a BDB table may make an automatic rollback and any read may fail with a deadlock error.
• Keys are not compressed to previous keys as with ISAM or MyISAM tables. In other words, the key information will take a little more space in BDB tables compared to MyISAM tables with don't use PACK_KEYS=0.
• One must do a FLUSH LOGS from time to time to sync to get checkpoints for the BDB tables.
• As transaction logs take more place than ordinary logs it's more important to rotate and remove old logs when using BDB tables than using other table types.

Some things that we have to fix in the near future:

• Remove the requirement that BDB tables must have a primary key. This will be fixed by having an automatic hidden auto_increment column for the tables without a primary key.
• LOCK TABLES should work as for other MySQL tables.
• ALTER TABLE doesn't yet work.
• SHOW TABLE STATUS doesn't yet provide that much information for BDB tables.

Go to the first, previous, next, last section, table of contents.