Today we will discuss on the HEAP tables in MySQL.
HEAP tables are present in memory and they are used for high-speed storage on the temporary basis.You need to specify the type while at creation. Basically, heap tables are also called memory tables.If the Mysql Database crashed then you will loose all data stored in the heap tables.
mysql> CREATE TABLE test_table_heap TYPE = HEAP SELECT ip, SUM(downloads) AS down FROM log_table GROUP BY ip;
mysql> SELECT COUNT(ip), AVG(down) FROM test_table_heap;
mysql> DROP TABLE test_table_heap;
- BLOB and TEXT type of data storage are not supported by Heap tables.
- AUTO_INCREMENT columns are not supported by the HEAP tables.
- You must have extra memory to store data in heap tables.
- If you want to free data from a table then you need to use DELETE FROM table_name , TRUNCATE table_name or DROP TABLE table_name .
- You can not create the table size more then max_heap_table_size .
- HEAP table uses the HASH and BTREE indexes.
- HEAP tables do not have problems with insert and delete.