MySQL MEMORY Storage Engine

Summary: In this tutorial, you will learn about the MySQL MEMORY storage engine and how to use it to improve the speed of temporary data retrieval.

Introduction to MySQL MEMORY storage engine

The MEMORY storage engine allows you to create a table whose data is stored entirely in the memory of the server.

The MEMORY storage engine is useful when you want to store data in the memory for fast access. For example:

• Caching: You can use MEMORY tables to cache frequently accessed data that rarely change. It can significantly improve the performance of read-heavy applications by serving data directly from memory.
• Session Data: Storing session data of web applications in MEMORY tables can improve the response time because it is faster to read and write data from memory.
• Temporary tables: You can also use the MEMORY tables for temporary storage such as intermediate results of complex queries. They’re often faster than disk-based storage engines.

Note that the MEMORY storage engine was previously called HEAP.

To create a MEMORY table, you use the CREATE TABLE statement and set the ENGINE clause to MEMORY:

CREATE TABLE table_name(
   ... 
) ENGINE = MEMORY;

When you create a MEMORY table, the storage engine saves the table definition in the MySQL database dictionary.

If the MySQL database server restarts, the data in the memory table will be swapped out, while the table definition remains intact.

MySQL MEMORY storage engine example

Let’s take an example of using MySQL MEMORY storage engine.

1) Creating a MEMORY table

The following CREATE TABLE statement creates a table called caches with the MEMORY storage engine:

CREATE TABLE caches (
   id INT
) ENGINE = MEMORY;Code language: SQL (Structured Query Language) (sql)

The caches table has one column with the data type INT.

2) Insert data into a MEMORY table

The following statement inserts three rows into the caches table:

INSERT INTO caches(id) 
VALUES 
  (1), 
  (2), 
  (3);Code language: SQL (Structured Query Language) (sql)

3) Query data from the MEMORY table

The following statement retrieves data from the caches table:

SELECT id FROM caches;Code language: SQL (Structured Query Language) (sql)

Output:

+------+
| id   |
+------+
|    1 |
|    2 |
|    3 |
+------+
3 rows in set (0.00 sec)Code language: JavaScript (javascript)

If you restart the MySQL server, the data in the caches table will be lost, and the above query will return no rows.

Summary

• The MEMORY storage engine stores table data entirely in the memory.
• The data in a MEMORY table will be lost if the MySQL Server restarts, while the table definitions persist in the MySQL dictionary.