To attain the fastest response time possible, the logical storage media is system memory. MySQL’s MEMORY storage engine was created with one goal in mind: speed. As of version 4.1, this storage engine was renamed from HEAP to MEMORY. However, HEAP remains a synonym of MEMORY.
Although storing table data in memory does indeed offer impressive performance, keep in mind that if the mysqld daemon crashes, all MEMORY data will be lost.
Memory Storage Engine Drawbacks
- If the mysqld daemon crashes, all MEMORY data will be lost.
- MEMORY tables do not support the VARCHAR, BLOB, or TEXT datatypes, because this table type is stored in fixedrecord-length format
- If you're using a version of MySQL prior to 4.1.0, automatically incrementing columns (via the AUTO_INCREMENT attribute) are not supported.
- Of course, you should keep in mind that MEMORY tables are intended for a specific scope, and aren't intended for long-term storage of data.
- If you're using MySQL 4.1 or earlier, key searches of MEMORY tables are less efficient than searches of MyISAM tables, because MEMORY tables support only hashed indexes, which require use of the entire key for searching. However, for versions newer than 4.1, both hashed and B-tree indexes are supported. The advantage of B-tree indexes over hashes is that partial and wildcard queries can be used, and operators such as <, >, and >= can be used to facilitate data mining.
Memory Engine Related FAQs