MENU


MEMORY

The MEMORY storage engine (formerly known as HEAP) stores table contents entirely in memory. Because the data is vulnerable to crashes, hardware issues, or power outages, MEMORY tables should be used only as temporary work areas or read-only caches for data pulled from other tables. See Storage Engines for how it compares to the others.

MEMORY performance is limited by contention: single-thread execution and table-lock overhead when processing updates restrict scalability as load increases, especially for statement mixes that include writes. Despite processing entirely in memory, MEMORY tables are not necessarily faster than InnoDB tables on a busy server, for general-purpose queries, or under a read/write workload – the table locking involved in updates can slow down concurrent access from multiple sessions.

The maximum size of a MEMORY table is governed by the max_heap_table_size system variable, which defaults to 16MB.

FeatureMEMORY
Storage limitsRAM (bounded by max_heap_table_size)
TransactionsNo
Locking granularityTable
MVCCNo
Geospatial data type supportNo
Geospatial indexing supportNo
B-tree indexesYes
T-tree indexesNo
Hash indexesYes
Full-text search indexesNo
Clustered indexesNo
Data cachesN/A
Index cachesN/A
Compressed dataNo
Encrypted dataYes
Cluster database supportNo
Replication supportYes
Foreign key supportNo
Backup / point-in-time recoveryYes
Query cache supportYes
Update statistics for data dictionaryYes

-- A hash-indexed lookup table kept entirely in RAM
CREATE TABLE session_lookup (
    session_id CHAR(32) PRIMARY KEY,
    user_id INT NOT NULL,
    INDEX (user_id) USING HASH
) ENGINE = MEMORY;

-- Cap the table's size explicitly for this session
SET SESSION max_heap_table_size = 64 * 1024 * 1024; -- 64MB

INSERT INTO session_lookup VALUES ('a1b2c3d4e5f6', 42);
SELECT user_id FROM session_lookup WHERE session_id = 'a1b2c3d4e5f6';

-- Data is lost on restart -- MEMORY tables are for caches/scratch space only

Query OK, 1 row affected
user_id
42