In the previous post, I had explained the basic theory on the Heap Table of Database Technology.
In this post, I am going to explain the Heap table terminology of MySQL.
MySQL Memory Storage Engine also called as Heap Storage structure.
Using Memory Storage Engine, you can store a table of data in server memory for fast retrieval.
This table of data also supports B-Tree index so you can get a result in a best-optimized way. Auto_Increment also supported by the Tables of Memory Engine.
There might be chances of loss data when any hardware crashes or power outage of Server Memory.
You can store and use only those data which are in temporary use and make sure that data in memory engine has always back up in physical stored table.
When to use Memory Engine of MySQL ?
If data are non-critical, need fast retrieval and only for read-only access with a minimum of updates.
The volume of data can be fit into Memory area because it internally uses the swap out of virtual memory pages.
Brief details of Memory Engine Mechanism:
The table of Memory Engine stores it’s table definition into one disk file, and extension of this file is .frm.
All other data stores in a specified area of memory.
The data in memory table is not only for one single connection, it is available for all running related connections and sessions of MySQL Server.
Create sample table using Memory Engine:
CREATE TABLE Test (TestNo INT,TestName VARCHAR(50)) ENGINE = MEMORY;
This all basic about MySQL Memory or Heap Engine.
Any confusion, like difference between Temporary table and Memory table.
If yes, then please visit next post.