MEMORY storage engine creates tables with
contents that are stored in memory. Formerly, these were known as
MEMORY is the
preferred term, although
HEAP remains supported
for backward compatibility.
MEMORY storage engine associates each table
with one disk file. The file name begins with the table name and has
an extension of
.frm to indicate that it stores
the table definition.
To specify that you want to create a
table, indicate that with an
ENGINE table option:
CREATE TABLE t (i INT) ENGINE = MEMORY;
The older term
TYPE is supported as a synonym for
ENGINE for backward compatibility, but
ENGINE is the preferred term and
TYPE is deprecated.
As indicated by the engine name,
are stored in memory. They use hash indexes by default, which makes
them very fast, 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
This example shows how you might create, use, and remove a
CREATE TABLE test ENGINE=MEMORY->
SELECT ip,SUM(downloads) AS down->
FROM log_table GROUP BY ip;mysql>
SELECT COUNT(ip),AVG(down) FROM test;mysql>
DROP TABLE test;
MEMORY tables have the following characteristics:
MEMORYtables 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.
MEMORYtables also have none of the problems commonly associated with deletes plus inserts in hashed tables.
MEMORYtables can have up to 64 indexes per table, 16 columns per index and a maximum key length of 3072 bytes.
MEMORYstorage engine supports both
BTREEindexes. You can specify one or the other for a given index by adding a
USINGclause as shown here:
CREATE TABLE lookup (id INT, INDEX USING HASH (id)) ENGINE = MEMORY; CREATE TABLE lookup (id INT, INDEX USING BTREE (id)) ENGINE = MEMORY;
For general characteristics of B-tree and hash indexes, see Section 8.3.1, “How MySQL Uses Indexes”.
MEMORYtable 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
BTREEindex to avoid this problem.
MEMORYtables can have nonunique keys. (This is an uncommon feature for implementations of hash indexes.)
Columns that are indexed can contain
MEMORYtables use a fixed-length row-storage format. Variable-length types such as
VARCHARare stored using a fixed length.
MEMORYincludes support for
MEMORYtables are shared among all clients, just like any other non-
MEMORYtable contents are stored in memory, which is a property that
MEMORYtables share with internal temporary tables that the server creates on the fly while processing queries. However, the two types of tables differ in that
MEMORYtables are not subject to storage conversion, whereas internal temporary tables are:
MEMORYtables are never converted to disk tables. If an internal temporary table becomes too large, the server automatically converts it to on-disk storage, as described in Section 8.4.4, “Internal Temporary Table Use in MySQL”.
The maximum size of
MEMORYtables is limited by the
max_heap_table_sizesystem variable, which has a default value of 16MB. To have larger (or smaller)
MEMORYtables, you must change the value of this variable. The value in effect for
CREATE TABLEis the value used for the life of the table. (If you use
TRUNCATE TABLE, the value in effect at that time becomes the new maximum size for the table. A server restart also sets the maximum size of existing
MEMORYtables to the global
max_heap_table_sizevalue.) You can set the size for individual tables as described later in this section.
The server needs sufficient memory to maintain all
MEMORYtables that are in use at the same time.
Memory is not reclaimed if you delete individual rows from a
MEMORYtable. Memory is reclaimed only when the entire table is deleted. Memory that was previously used for rows that have been deleted will be re-used for new rows only within the same table. To free up the memory used by rows that have been deleted, use
ALTER TABLE ENGINE=MEMORYto force a table rebuild.
If you want to populate a
MEMORYtable when the MySQL server starts, you can use the
--init-fileoption. For example, you can put statements such as
INSERT INTO ... SELECTor
LOAD DATA INFILEinto this file to load the table from a persistent data source. See Section 5.1.3, “Server Command Options”, and Section 13.2.6, “LOAD DATA INFILE Syntax”.
MEMORYtables become empty when it is shut down and restarted. However, if the server is a replication master, its slave are not aware that these tables have become empty, so they returns out-of-date content if you select data from these tables. To handle this, when a
MEMORYtable is used on a master for the first time since it was started, a
DELETEstatement is written to the master's binary log automatically, thus synchronizing the slave to the master again. Note that even with this strategy, the slave still has outdated data in the table during the interval between the master's restart and its first use of the table. However, if you use the
--init-fileoption to populate the
MEMORYtable on the master at startup, it ensures that this time interval is zero.
The memory needed for one row in a
MEMORYtable is calculated using the following expression:
max_length_of_key+ sizeof(char*) × 4) + SUM_OVER_ALL_HASH_KEYS(sizeof(char*) × 2) + ALIGN(
ALIGN()represents a round-up factor to cause the row length to be an exact multiple of the
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
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
created by all clients.) The following example creates two
MEMORY tables, with a maximum size of 1MB and
SET max_heap_table_size = 1024*1024;Query OK, 0 rows affected (0.00 sec) mysql>
CREATE TABLE t1 (id INT, UNIQUE(id)) ENGINE = MEMORY;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) mysql>
CREATE TABLE t2 (id INT, UNIQUE(id)) ENGINE = MEMORY;Query OK, 0 rows affected (0.00 sec)
Both tables will revert to the server's global
max_heap_table_size value if the
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
value, which still acts as a constraint on maximum table size. For
maximum flexibility in being able to use
max_heap_table_size at least as
high as the value to which you want each
table to be able to grow.
A forum dedicated to the
MEMORYstorage engine is available at http://forums.mysql.com/list.php?92.