What is the purpose of using a HEAP table in MySQL?

The table which uses a hashed index and stores in the memory is called the HEAP table. It works as a temporary table and it uses the indexes that make it faster than another table type.

When MySQL crashes for any reason then all the data stored in this table can be lost. It uses fixed-length data types. Hence BLOB and TEXT data types are not supported by this table. It is a useful table for those MySQL tasks where speed is the most important factor and temporary data is used.

The MySQL internal HEAP tables use 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;