Documentation Home
MySQL 5.6 Reference Manual
Related Documentation Download this Manual
PDF (US Ltr) - 31.0Mb
PDF (A4) - 31.0Mb
PDF (RPM) - 29.2Mb
HTML Download (TGZ) - 7.2Mb
HTML Download (Zip) - 7.2Mb
HTML Download (RPM) - 6.2Mb
Man Pages (TGZ) - 175.7Kb
Man Pages (Zip) - 286.0Kb
Info (Gzip) - 3.0Mb
Info (Zip) - 3.0Mb
Excerpts from this Manual

MySQL 5.6 Reference Manual  /  Alternative Storage Engines  /  The MEMORY Storage Engine

15.3 The MEMORY Storage Engine

The MEMORY storage engine (formerly known as HEAP) creates special-purpose tables with contents that are stored in memory. Because the data is vulnerable to crashes, hardware issues, or power outages, only use these tables as temporary work areas or read-only caches for data pulled from other tables.

Table 15.4 MEMORY Storage Engine Features

Feature Support
B-tree indexes Yes
Backup/point-in-time recovery (Implemented in the server, rather than in the storage engine.) Yes
Cluster database support No
Clustered indexes No
Compressed data No
Data caches N/A
Encrypted data (Implemented in the server via encryption functions. Data-at-rest tablespace encryption is available in MySQL 5.7 and later.) Yes
Foreign key support No
Full-text search indexes No
Geospatial data type support No
Geospatial indexing support No
Hash indexes Yes
Index caches N/A
Locking granularity Table
Replication support (Implemented in the server, rather than in the storage engine.) Limited (See the discussion later in this section.)
Storage limits RAM
T-tree indexes No
Transactions No
Update statistics for data dictionary Yes

When to Use MEMORY or NDB Cluster

Developers looking to deploy applications that use the MEMORY storage engine for important, highly available, or frequently updated data should consider whether NDB Cluster is a better choice. A typical use case for the MEMORY engine involves these characteristics:

  • Operations involving transient, non-critical data such as session management or caching. When the MySQL server halts or restarts, the data in MEMORY tables is lost.

  • In-memory storage for fast access and low latency. Data volume can fit entirely in memory without causing the operating system to swap out virtual memory pages.

  • A read-only or read-mostly data access pattern (limited updates).

NDB Cluster offers the same features as the MEMORY engine with higher performance levels, and provides additional features not available with MEMORY:

  • Row-level locking and multiple-thread operation for low contention between clients.

  • Scalability even with statement mixes that include writes.

  • Optional disk-backed operation for data durability.

  • Shared-nothing architecture and multiple-host operation with no single point of failure, enabling 99.999% availability.

  • Automatic data distribution across nodes; application developers need not craft custom sharding or partitioning solutions.

  • Support for variable-length data types (including BLOB and TEXT) not supported by MEMORY.

Performance Characteristics

MEMORY performance is constrained by contention resulting from single-thread execution and table lock overhead when processing updates. This limits scalability when load increases, particularly for statement mixes that include writes.

Despite the in-memory processing for MEMORY tables, they are not necessarily faster than InnoDB tables on a busy server, for general-purpose queries, or under a read/write workload. In particular, the table locking involved with performing updates can slow down concurrent usage of MEMORY tables from multiple sessions.

Depending on the kinds of queries performed on a MEMORY table, you might create indexes as either the default hash data structure (for looking up single values based on a unique key), or a general-purpose B-tree data structure (for all kinds of queries involving equality, inequality, or range operators such as less than or greater than). The following sections illustrate the syntax for creating both kinds of indexes. A common performance issue is using the default hash indexes in workloads where B-tree indexes are more efficient.

Characteristics of MEMORY Tables

The MEMORY storage engine associates each table with one disk file, which stores the table definition (not the data). The file name begins with the table name and has an extension of .frm.

MEMORY tables have the following characteristics:

  • Space for MEMORY tables is allocated in small blocks. Tables use 100% dynamic hashing for inserts. No overflow area or extra key space is needed. No extra space is needed for free lists. Deleted rows are put in a linked list and are reused when you insert new data into the table. MEMORY tables also have none of the problems commonly associated with deletes plus inserts in hashed tables.

  • MEMORY tables use a fixed-length row-storage format. Variable-length types such as VARCHAR are stored using a fixed length.

  • MEMORY tables cannot contain BLOB or TEXT columns.

  • MEMORY includes support for AUTO_INCREMENT columns.

  • Non-TEMPORARY MEMORY tables are shared among all clients, just like any other non-TEMPORARY table.

DDL Operations for MEMORY Tables

To create a MEMORY table, specify the clause ENGINE=MEMORY on the CREATE TABLE statement.


As indicated by the engine name, MEMORY tables are stored in memory. They use hash indexes by default, which makes them very fast for single-value lookups, and very useful for creating temporary tables. However, when the server shuts down, all rows stored in MEMORY tables are lost. The tables themselves continue to exist because their definitions are stored in .frm files on disk, but they are empty when the server restarts.

This example shows how you might create, use, and remove a MEMORY table:

    ->     SELECT ip,SUM(downloads) AS down
    ->     FROM log_table GROUP BY ip;
mysql> SELECT COUNT(ip),AVG(down) FROM test;
mysql> DROP TABLE test;

The maximum size of MEMORY tables is limited by the max_heap_table_size system variable, which has a default value of 16MB. To enforce different size limits for MEMORY tables, change the value of this variable. The value in effect for CREATE TABLE, or a subsequent ALTER TABLE or TRUNCATE TABLE, is the value used for the life of the table. A server restart also sets the maximum size of existing MEMORY tables to the global max_heap_table_size value. You can set the size for individual tables as described later in this section.


The MEMORY storage engine supports both HASH and BTREE indexes. You can specify one or the other for a given index by adding a USING clause as shown here:

    (id INT, INDEX USING HASH (id))
    (id INT, INDEX USING BTREE (id))

For general characteristics of B-tree and hash indexes, see Section 8.3.1, “How MySQL Uses Indexes”.

MEMORY tables can have up to 64 indexes per table, 16 columns per index and a maximum key length of 3072 bytes.

If a MEMORY table hash index has a high degree of key duplication (many index entries containing the same value), updates to the table that affect key values and all deletes are significantly slower. The degree of this slowdown is proportional to the degree of duplication (or, inversely proportional to the index cardinality). You can use a BTREE index to avoid this problem.

MEMORY tables can have nonunique keys. (This is an uncommon feature for implementations of hash indexes.)

Columns that are indexed can contain NULL values.

User-Created and Temporary Tables

MEMORY table contents are stored in memory, which is a property that MEMORY tables share with internal temporary tables that the server creates on the fly while processing queries. However, the two types of tables differ in that MEMORY tables are not subject to storage conversion, whereas internal temporary tables are:

Loading Data

To populate a MEMORY table when the MySQL server starts, you can use the --init-file option. For example, you can put statements such as INSERT INTO ... SELECT or LOAD DATA INFILE into this file to load the table from a persistent data source. See Section 5.1.6, “Server Command Options”, and Section 13.2.6, “LOAD DATA INFILE Syntax”.

For loading data into MEMORY tables accessed by other sessions concurrently, MEMORY supports INSERT DELAYED. See Section, “INSERT DELAYED Syntax”.

MEMORY Tables and Replication

A server's MEMORY tables become empty when it is shut down and restarted. If the server is a replication master, its slaves are not aware that these tables have become empty, so you see out-of-date content if you select data from the tables on the slaves. To synchronize master and slave MEMORY tables, when a MEMORY table is used on a master for the first time since it was started, a DELETE statement is written to the master's binary log, to empty the table on the slaves also. The slave still has outdated data in the table during the interval between the master's restart and its first use of the table. To avoid this interval when a direct query to the slave could return stale data, use the --init-file option to populate the MEMORY table on the master at startup.

Managing Memory Use

The server needs sufficient memory to maintain all MEMORY tables that are in use at the same time.

Memory is not reclaimed if you delete individual rows from a MEMORY table. Memory is reclaimed only when the entire table is deleted. Memory that was previously used for deleted rows is re-used for new rows within the same table. To free all the memory used by a MEMORY table when you no longer require its contents, execute DELETE or TRUNCATE TABLE to remove all rows, or remove the table altogether using DROP TABLE. To free up the memory used by deleted rows, use ALTER TABLE ENGINE=MEMORY to force a table rebuild.

The memory needed for one row in a MEMORY table is calculated using the following expression:

SUM_OVER_ALL_BTREE_KEYS(max_length_of_key + sizeof(char*) * 4)
+ SUM_OVER_ALL_HASH_KEYS(sizeof(char*) * 2)
+ ALIGN(length_of_row+1, sizeof(char*))

ALIGN() represents a round-up factor to cause the row length to be an exact multiple of the char pointer size. sizeof(char*) is 4 on 32-bit machines and 8 on 64-bit machines.

As mentioned earlier, the max_heap_table_size system variable sets the limit on the maximum size of MEMORY tables. To control the maximum size for individual tables, set the session value of this variable before creating each table. (Do not change the global max_heap_table_size value unless you intend the value to be used for MEMORY tables created by all clients.) The following example creates two MEMORY tables, with a maximum size of 1MB and 2MB, respectively:

mysql> SET max_heap_table_size = 1024*1024;
Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.01 sec)

mysql> SET max_heap_table_size = 1024*1024*2;
Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Both tables revert to the server's global max_heap_table_size value if the server restarts.

You can also specify a MAX_ROWS table option in CREATE TABLE statements for MEMORY tables to provide a hint about the number of rows you plan to store in them. This does not enable the table to grow beyond the max_heap_table_size value, which still acts as a constraint on maximum table size. For maximum flexibility in being able to use MAX_ROWS, set max_heap_table_size at least as high as the value to which you want each MEMORY table to be able to grow.

Additional Resources

A forum dedicated to the MEMORY storage engine is available at

User Comments
  Posted by Shelby Moore on January 16, 2005
I think the slowdown documented above is entirely unnecessary and the slowdown is not directly correlated to cardinality:

"...The degree of slowdown is proportional to the degree of duplication...You can use a BTREE index to avoid this problem."

Only a very simple "MTF" optimization needs to be made to the HEAP storage engine:

BTREEs are much slower than hashing (about 5 to 6 times at least), and are necessary only when non-equality (range) indexing is required. See the research paper quoted at above link for benchmarks.

So consider the above advice to use BTREEs to solve performance issues as incorrect because they are 5 - 6 times slower. BTREEs are a way to get 5 - 6 times slower performance than a correctly optimized HASH indexing. BTREEs may be faster in some cases than an *UN*optimized HASH index.

As for the issue of slowdown correlation to cardinality, see comment "16 Jan 9:32pm" in above link.
  Posted by Shelby Moore on January 16, 2005
Current HASH key implementation is unoptimized and much slower than it needs to be for the case where most queries result in non-match:

In this case, it is possible that BTREE is faster until HASH is optimized.

  Posted by Stein Haugan on March 2, 2007
Insertion into HASH indexed columns is somewhat vulnerable to degenerate cases of "bad" data sets, which can cause insertion to be painfully slow (two orders of magnitude slower than a "normal" data set). See the examples (with suggestions for application-level fixes) below:

Create a table n:

mysql> create temporary table n (n int unsigned auto_increment primary key);

mysql> insert into n select NULL from SQ_SIMILAR2; -- a 1-million-row-table
Query OK, 1115156 rows affected (4.40 sec)
Records: 1115156 Duplicates: 0 Warnings: 0

Ok, now we have numbers 1-1e6 in table n.

mysql> create temporary table sq (sq int unsigned, key sq) engine memory;

Ok, now we're set. Look at the timings in the two insert statements:

mysql> insert into sq select floor(n/64*1024)*n from n;
Query OK, 1115156 rows affected, 65535 warnings (2.80 sec)
Records: 1115156 Duplicates: 0 Warnings: 1098773

mysql> truncate table sq;
Query OK, 0 rows affected (0.01 sec)

mysql> insert into sq select floor(n/(64*1024-1))*n from n;
Query OK, 1115156 rows affected (2 min 59.34 sec)
Records: 1115156 Duplicates: 0 Warnings: 0

In other words, a slow-down factor of 64! Obviously something weird is
going on that throws the adaptive cache algorithm to the ground!

Part of the problem can be solved by e.g. random reordering before
inserts (after truncating the table, of course):

mysql> insert into sq select floor(n/(64*1024-1))*n from n order by rand();
Query OK, 1115156 rows affected (52.64 sec)
Records: 1115156 Duplicates: 0 Warnings: 0

Now we're down to "only" a factor of about 20. But we can do even better:

mysql> insert into sq select floor(n/(64*1024-1))*n from n order by n desc;
Query OK, 1115156 rows affected (2.60 sec)
Records: 1115156 Duplicates: 0 Warnings: 0

Whee! Great.

Our actual data were a little different. The table SQ_SIMILAR2 contains
1.1 million non-unique numbers - about 180,000 distinct values between 1
and 1.1 million - in a, well, special [by accident] order. Here are some
timings (table sq is truncated before each insert):

mysql> insert into sq select SQ_SIMILAR2 from SQ_SIMILAR2;
Query OK, 1115156 rows affected (4 min 39.07 sec)
Records: 1115156 Duplicates: 0 Warnings: 0

I.e. a little worse than the test case above. Random ordering seems a tiny
bit worse. And ordering in ascending order is really, really bad:

mysql> insert into sq select SQ_SIMILAR2 from SQ_SIMILAR2 order by SQ_SIMILAR2;
Query OK, 1115156 rows affected (8 min 31.24 sec)
Records: 1115156 Duplicates: 0 Warnings: 0

Yikes, a slow-down factor of 182 compared to the floor(n/64*1024)*n
example above. Sorting in descending order gets back within the realm of
the reasonable again:

mysql> insert into sq select SQ_SIMILAR2 from SQ_SIMILAR2 order by SQ_SIMILAR2 $
Query OK, 1115156 rows affected (4.54 sec)
Records: 1115156 Duplicates: 0 Warnings: 0

But with non-unique data, can you do better? Try this:

mysql> insert into sq select distinct SQ_SIMILAR2 from SQ_SIMILAR2;
Query OK, 181272 rows affected (0.61 sec)
Records: 181272 Duplicates: 0 Warnings: 0

mysql> insert into sq select SQ_SIMILAR2 from SQ_SIMILAR2;
Query OK, 1115156 rows affected (1.50 sec)
Records: 1115156 Duplicates: 0 Warnings: 0

Alltogether only 2.11 sec, half the time of the descending sort order,
although further table manipulations are necessary to delete the spurious
duplicates that have been created.

  Posted by Eric Walker on December 6, 2007
When joining a column in a MEMORY table against one in an InnoDB table, the kind of indexes on the columns is important.

In my case, when a column on a MEMORY table was of type HASH and the corresponding column in the InnoDB table of type BTREE, the query optimizer was not able to make use of the indexes and queries were taking a long time. A fix in this instance was to convert the default HASH index on the MEMORY table column to BTREE.
Sign Up Login You must be logged in to post a comment.