Memory - MySQL Storage Engine (Formerly Heap Storage Engine)

Keywords: MySQL, Storage Engines, Memory (Heap) Storage Engine, Engines

Memory - MySQL Storage Engine (Formerly Heap Storage Engine):

RESTART == FORGET. 

One of the interesting MySQL Storage engine is - Memory (Heap). 

Memory (Heap) is a special table (or storage engine) type because of its unique behaviour.

This table type simply says, “Please don’t restart the server, I will forget the data”.

• Tables created using Memory (Heap) type are stored in the memory hence whenever the server is restarted the data will be lost, but the table definition will be there forever as the .frm file is associated with every Memory (Heap) table type

• Ex: If you create a table “xyz”, and inserting some records (say 50+) into that. After some time you’re restarting your server. The moment you restarted your server is when you lost the data.

• Memory (Heap) tables are useful whenever you need to create a temporary table as this will reduce your effort of destructing those tables.
• Memory (Heap) table type uses hash indexes by default and hence it yields the results much faster comparatively with other table types.
• Memory (Heap) table definitions are stored in the .frm files, so there will be no loss of table definition when you restart your server. The moment you restart the server, is the moment it will forget the data stored in it.
• To create a Memory (Heap) type of mysql table, use the following syntax:

• mysql> create table table_name (columns) ENGINE=MEMORY

• Memory (Heap) storage engine implements both HASH and BTREE indexes in nature. You shall specify the required one like in the following:
mysql> create table table_name (column_name column_type, INDEX USING HASH (column_name));

mysql> create table table_name (column_name column_type, INDEX USING BTREE (column_name));
• Table size of the Memory (Heap) storage engine shall be set using the MySQL system variable “MAX_HEAP_TABLE_SIZE”. The syntax for setting the Memory (Heap) table size is as follows:

• mysql> set MAX_ HEAP_TABLE_SIZE = SIZE(IN MB);

• Data of the Memory (Heap) tables shall be deleted by executing "DELETE" or "TRUNCATE" statements, if the contents are no longer required. 
• Memory (Heap) tables shall also be dropped at any given time by using a "DROP" statement.
• Memory (Heap) table/storage_engine is different from "Blackhole Storage Engine" due to the behaviourial differences. i.e. The data will never be stored "Blackhole Storage Engine" whereas in Memory (Heap) it will be stored, but deleted after a restart.
• Availability of “Memory (Heap)” Storage engine shall be found using the following command:

• mysql> SHOW ENGINES; 

For more information, pls visit the following links: 

• http://dev.mysql.com/doc/refman/6.0/en/memory-storage-engine.html
  
• http://kathyravan.blogspot.com/2008/12/blackhole-mysql-storage-engine_04.html
  
• http://kathyravan.blogspot.com/2008/03/xml-in-mysql.html
  

Keywords: MySQL, Storage Engines, Memory (Heap) Storage Engine, Engines