7.4 HEAP Tables

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!

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 use 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.
• Prior to MySQL 4.0.2, 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 are allocated in small blocks. The tables are 100% dynamic (on inserting). No overflow areas and no extra key space are needed. Deleted rows are put in a linked list and are reused when you insert new data into the table.
• You need enough extra memory for all HEAP tables that you want to use at the same time.
• To free memory, you should execute DELETE FROM heap_table, TRUNCATE heap_table or DROP TABLE heap_table.
• MySQL cannot find out approximately how many rows there are between two values (this is used by the range optimiser to decide which index to use). This may affect some queries if you change a MyISAM table to a HEAP table.
• To ensure that you accidentally don't do anything foolish, you can't create HEAP tables bigger than max_heap_table_size.

The memory needed for one row in a HEAP table is:

SUM_OVER_ALL_KEYS(max_length_of_key + sizeof(char*) * 2)
+ ALIGN(length_of_row+1, sizeof(char*))

sizeof(char*) is 4 on 32-bit machines and 8 on 64-bit machines.

User Comments

I have benchmarked inserts and deletes on MyISAM
and HEAP tables. HEAP tables appear to be at
least 30% faster on inserts but only 15% faster
when selecting single records. MyISAM table
caching is pretty fast. I also did some
benchmarks on Oracle for comparison. See
http://php.weblogs.com/oracle_mysql_performance
for further details.

Intel's Itanium and the latest Itanium 2 based
x86 machines have the capacity of supporting
memory much bigger than 64GB as they are 64
bit based processor.

Add your own comment.