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.
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 recoveryISAM 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.
MyISAMMyISAM 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.)
HEAPHEAP 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.