10.18 MySQL table types.

Table types are introduced in MySQL 3.23!

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.

You can convert tables between different types with the ALTER TABLE statement. ALTER TABLE.

• ISAM This is the original MySQL table type. This 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. 13.4 Using isamchk for crash recovery ISAM tables are not binary portable across OS/Platforms. 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; Fast but is machine/OS dependent.
• MyISAM MyISAM is the default table type in MySQL 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 extension and the data is stored in file with the .MYD extension. You can check/repair ISAM tables with the myisamchk utility. 13.4 Using isamchk for crash recovery The following is new in MyISAM:
  • Supports for big files (63 bit) on OSes that support big files.
  • All data are stored with low byte first. This makes the data machine and OS independent.
  • All number keys are stored with high byte first to give better 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 and old numbers will not be reused.
  • 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 by default. In cases of keys longer than 250, 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 now mark tables as checked. 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 NISAM).
  • 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 (called pack_isam in NISAM) 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.)
• HEAP 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 usable as temporary tables!

  CREATE TABLE test TYPE=HEAP SELECT ip,SUM(downloads) as down FROM log_table GROUP BY ip;
  SELECT COUNT(ip),AVG(down) from test;
  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 uses 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 (not that normal with hashed tables).
  • HEAP tables are shared between all clients (just like any other table).
  • 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 will be reused when you insert new data into the table.
  • To free memory, you should execute DELETE FROM heap_table or DROP TABLE heap_table.
  • To ensure that you accidentally don't do anything stupid, you can't create HEAP tables bigger than max_heap_table_size.