Faster Access to data through Memory Storage Engine

MEMORY storage engine provides a platform, where memory of the computer can be utilized to store the tables along with their data. This approach provides MySQL users with a temporary work area, where they can bring in their desired tables and data, access and update those tables and data at a much faster speed. But the downside of this approach is that once computer gets crashed or it suddenly stops because of power failure or any other problem then all the data will vanish from the memory, when next time computer starts. So it is always advisable to use this storage engine only when you need to work on a particular set of data frequently. All the frequently used data can be brought in to the temporary tables residing in your computer memory, use it as long as you desire and no heartburn is caused even if computer crashes out in the middle.

But one should be sure that even the frequently used data being brought into memory is not critical or sensitive because there is a huge risk of loosing this invaluable data when MEMORY storage engine is being used. Usually this storage engine should be used to store temporary data like session variables or data which you want to be cached and could be frequently and quickly accessed. Though MEMORY is used for faster access, but care should be taken that your RAM has sufficient space to store your data and that it should not lead to operating system swapping out virtual memory pages.

Now that we have discussed how MEMORY can be effectively utilized to reap the benefits of better access speed in some situations, where there is no risk of harming our critical data in any manner. We should also look into the other side of the picture in order to also evaluate the weakness of MEMORY storage engine. When it comes to transaction operations then MEMORY should not be used because it offers no support for ACID based features. MEMORY is particularly ineffective when it comes to multi user concurrency because there is no support for row level locking and multi-thread operations. Also you can not use variable length data types in case of MEMORY.

So these are the pros and cons associated with MEMORY storage engine. If you want to enjoy better features along with the faster memory access benefits of the MEMORY storage engine then you can give MySQL Cluster a very serious consideration.

Author: ucavik