HEAP Tables

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. (If you would like more information about what this means, you can read more about indexing in Chapter 19.)

• Indexes will be used only to find rows with queries that use the = or <=> operators in the search clause.