HEAP Tables in MySQL

Solutions

Drivers

Others

An introduction to HEAP Tables in MySQL

This is really not a problem but a concept. When I came from Ms-Access study to MySQL, I could not understand what is a heap table. Hence I searched the Net and whatever I found there, I am putting here for your use.

HEAP tables are extremely fast tables that are stored wholly in memory. They use a hashed indexing scheme that is responsible for their speed.

The downside to having tables stored completely in memory is, of course, that if you have any power issues, your HEAP data is gone forever. They are, however, great for storing temporary tables.

You can create a HEAP table like this:
create table testHeap (id int not null primary key, data char(100)) type=heap max_rows = 100;

As you can see, we have specified the table type as HEAP. We have also followed a good practice guideline here by limiting the maximum number of rows in the table. If your HEAP tables grow large, you can easily run out of memory. The number of rows can also be limited by the configuration directive max_heap_table_size.

• HEAP tables have a few limitations:
• They don't support AUTO_INCREMENT.
• They don't support BLOB or TEXT types.
• HEAP tables cannot use the leftmost prefix of an index to find rows.
• Indexes will be used only to find rows with queries that use the = or <=> operators in the search clause.