MySQL Reference Manual

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 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;

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

• You should always specify MAX_ROWS in the CREATE statement to ensure that you accidentally 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 an ORDER BY).

• Data for HEAP tables is allocated in small blocks. The tables are 100% dynamic (on inserting). No overflow ...