7 MySQL Table Types
 
MySQL Version 3.x.x has three basic not transaction-safe table formats (NTST): ISAM, HEAP and MyISAM.
Newer versions of MySQL support additional transaction-safe table types (TST): InnoDB, or BDB,
depending on how you compile it.
When you create a new table, you can tell MySQL what type of table to create. The default table type is usually MyISAM.
MySQL will always create a `.frm' file to hold the table and column definitions.
The table's index and data will be stored in one or more other files, depending on the table type:
ISAM: `.ISD' and '.ISM'
         MyISAM : `.MYD' and`.MYI'
If you try to use a table type that is not compiled-in or activated, MySQL will instead create a table of type MyISAM. This behaviour is convenient when you want to copy tables between MySQL servers that support different table types.
You can convert tables between different types with the ALTER TABLE statement.
 
ISAM table format:
This deprecated ISAM table type will disappear in MySQL version 5.0. MyISAM is a better implementation of the same thing.
To convert an ISAM table to a MyISAM : mysql> ALTER TABLE tbl_name TYPE = MYISAM;
You can check/repair ISAM tables with the isamchk utility.
If you use myisamchk to repair or optimise tables, you must always ensure that the mysqld server is not using the table.
How to Repair Tables: http://dev.mysql.com/doc/refman/5.0/en/repair.html
HEAP table format: HEAP tables use hashed indexes 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!
 
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;
 
InnoDB table format: Transaction-safe (ACID compliant) storage engine with commit, rollback, and crash recovery capabilities.
It does locking on row level and also provides an Oracle-style consistent non-locking read in SELECTs.

Advantages of not transaction-safe tables (NTST):

The TST and NTST tables can be combined in the same statements to get the best of both worlds.