Lately, I have been doing a lot of database stuff and during my odyssey I found lots of exciting stuff in MySQL. Memory storage in engine is one of those exciting stuff, which can be really useful at times, if used on right time at right place.
Memory (heap) for the name itself signifies what I am talking about. MySQL, the famous database allows you to store your tables in the Memory or formerly known as Heap storage. The tables which are created in the memory engine are exceptionally faster and can increase the overall performance of your database operations. The reason is that the data is stored in the temporary memory which can be accessed faster in comparison to data stored in the disk drive.
To create a table using the memory engine, use the following SQL syntax.
create table temp (name varchar(30)) Engine = Memory; |
Notice the Engine= Memory clause in the above statement, which forces to store the table in the memory instead of using the usual storage system i.e. your disk drive.
When you create a table use memory engine as a backbone, a corresponding file is created in the disk. This file has the same name as of the table and has .frm as extension. As I mentioned earlier, the data is stored in the memory so when the system is restarted all the data gets clear from the memory. However, the table structure exists in the above mentioned disk file. You might be wondering then what is the significance of using this type of storage engine, which cannot safeguard your data permanently. The benefits of using Memory storage is that the tables use hash indexes by default which in result make the data accessibility faster.
So, an ideal scenario to use this engine would be when you need to access data more than the insertion and the data is created temporarily. It makes the queries faster due to the reasons discussed above. You can read more about memory tables at MySQL documentation.
Hope this helps.
Stay Digified !!
Sachin Khosla
