Memory Storage Engine

Last updated: October 26, 2023

The MEMORY storage engine, also known as the HEAP storage engine, provides a high-performance, in-memory table type. Data stored in MEMORY tables is held in RAM, which allows for very fast data retrieval and manipulation. This engine is ideal for temporary tables, caching, or situations where data persistence is not a primary concern.

Key Characteristics

In-Memory Storage: All data and indexes are stored in RAM.
Speed: Significantly faster than disk-based storage engines due to direct memory access.
No Durability: Data is lost when the database server is shut down or restarted.
Limited Data Types: Supports a subset of SQL data types. BLOB and TEXT types are not supported.
Index Support: Supports HASH and BTREE indexes.
Concurrency: Uses table-level locking.

When to Use the MEMORY Engine

Temporary Tables: For storing intermediate results during complex queries.
Caching: To cache frequently accessed data for faster lookup.
Session Information: Storing temporary session-specific data.
Lookup Tables: Small, static tables used for reference that are loaded frequently.

Note: Due to its in-memory nature, the size of MEMORY tables is limited by the available RAM on the server. Exceeding available memory can lead to performance degradation or server instability.

Table Creation Example

To create a table using the MEMORY storage engine, you specify ENGINE=MEMORY during table creation:

CREATE TABLE sessions (
    session_id VARCHAR(255) NOT NULL PRIMARY KEY,
    user_id INT,
    login_time DATETIME,
    data TEXT -- Note: TEXT is not fully supported, see limitations
) ENGINE=MEMORY;

Warning: While the syntax above might appear to work, the TEXT data type and other large object types (like BLOB) are not fully supported by the MEMORY engine. You should generally avoid them. If you need to store such data, consider a disk-based engine.

Supported Data Types

The MEMORY engine supports most standard numeric, string, and date/time data types. However, it does not support BLOB, TEXT, or spatial data types.

Category	Supported Types	Unsupported Types
Numeric	`TINYINT`, `SMALLINT`, `MEDIUMINT`, `INT`, `BIGINT`, `DECIMAL`, `FLOAT`, `DOUBLE`	N/A
String	`CHAR`, `VARCHAR`, `BINARY`, `VARBINARY`, `ENUM`, `SET`	`TINYBLOB`, `BLOB`, `MEDIUMBLOB`, `LONGBLOB`, `TINYTEXT`, `TEXT`, `MEDIUMTEXT`, `LONGTEXT`
Date/Time	`DATE`, `TIME`, `DATETIME`, `TIMESTAMP`, `YEAR`	N/A
Other	`BOOLEAN` (as `TINYINT(1)`)	Spatial types

Index Types

The MEMORY engine supports the following index types:

HASH: Ideal for equality comparisons (=, IN). Very fast for exact lookups.
BTREE: Supports a wider range of operations, including range scans (<, >, BETWEEN) and sorting.

By default, if you create a PRIMARY KEY or a UNIQUE index on a MEMORY table, it will be created as a HASH index unless you explicitly specify USING BTREE.

-- HASH index (default for PRIMARY KEY)
CREATE TABLE products (
    product_id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(100)
) ENGINE=MEMORY;

-- BTREE index
CREATE TABLE logs (
    log_time DATETIME,
    message VARCHAR(255),
    INDEX idx_log_time (log_time) USING BTREE
) ENGINE=MEMORY;

Limitations and Considerations

No Durability: Data loss on restart is the most significant limitation.
No BLOB/TEXT Support: Cannot store large unstructured data.
Table-Level Locking: All MEMORY tables use table-level locking, which can become a bottleneck for highly concurrent write operations.
Memory Consumption: Tables reside entirely in RAM.
Limited SQL Features: Not all SQL features are supported (e.g., full-text indexes, foreign keys).

Tuning MEMORY Tables

Configuration parameters related to MEMORY tables often involve memory allocation and performance tuning. For example, the max_heap_table_size system variable limits the maximum size of MEMORY tables that can be created or altered.

-- Show current limit
SHOW VARIABLES LIKE 'max_heap_table_size';

-- Set a new limit (e.g., 1GB)
SET GLOBAL max_heap_table_size = 1073741824;

It's important to monitor memory usage and adjust these settings cautiously to avoid impacting overall server stability.

Tip: For optimal performance, use HASH indexes on MEMORY tables when performing equality lookups. Use BTREE indexes for range queries or when order matters.

The MEMORY storage engine is a powerful tool for specific use cases where speed is paramount and data durability is secondary. Understanding its limitations is key to using it effectively.