(mysql.info) memory-storage-engine
Info Catalog
(mysql.info) merge-storage-engine
(mysql.info) storage-engines
(mysql.info) bdb-storage-engine
14.4 The `MEMORY' (`HEAP') Storage Engine
=========================================
The `MEMORY' storage engine creates tables with contents that are
stored in memory. Formerly, these were known as `HEAP' tables. `MEMORY'
is the preferred term, although `HEAP' remains supported for backward
compatibility.
Each `MEMORY' table is associated with one disk file. The filename
begins with the table name and has an extension of `.frm' to indicate
that it stores the table definition.
To specify explicitly that you want to create a `MEMORY' 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 name, `MEMORY' tables 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 server restarts.
This example shows how you might create, use, and remove a `MEMORY'
table:
mysql> 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:
* 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 can have up to 32 indexes per table, 16 columns
per index and a maximum key length of 500 bytes.
* The `MEMORY' storage engine implements both `HASH' and `BTREE'
indexes. You can specify one or the other for a given index by
adding a `USING' clause 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;
General characteristics of B-tree and hash indexes are described
in mysql-indexes.
* You can have non-unique keys in a `MEMORY' table. (This is an
uncommon feature for implementations of hash indexes.)
* If you have a hash index on a `MEMORY' table that 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.
* Columns that are indexed can contain `NULL' values.
* `MEMORY' tables use a fixed-length row storage format.
* `MEMORY' tables cannot contain `BLOB' or `TEXT' columns.
* `MEMORY' includes support for `AUTO_INCREMENT' columns.
* You can use `INSERT DELAYED' with `MEMORY' tables. See
insert-delayed.
* `MEMORY' tables are shared among all clients (just like any other
non-`TEMPORARY' table).
* `MEMORY' table contents are stored in memory, which is a property
that `MEMORY' tables share with internal 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 tables are:
* If an internal table becomes too large, the server
automatically converts it to an on-disk table. The size limit
is determined by the value of the `tmp_table_size' system
variable.
* `MEMORY' tables are never converted to disk tables. To ensure
that you don't accidentally do anything foolish, you can set
the `max_heap_table_size' system variable to impose a maximum
size on `MEMORY' tables. For individual tables, you can also
specify a `MAX_ROWS' table option in the `CREATE TABLE'
statement.
* The server needs sufficient memory to maintain all `MEMORY' tables
that are in use at the same time.
* To free memory used by a `MEMORY' table when you no longer require
its contents, you should execute `DELETE' or `TRUNCATE TABLE', or
remove the table altogether using `DROP TABLE'.
* If you want 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
server-options, and load-data.
* If you are using replication, the master server's `MEMORY' tables
become empty when it is shut down and restarted. However, a slave
is not aware that these tables have become empty, so it returns
out-of-date content if you select data from them. When a `MEMORY'
table is used on the master for the first time since the master
was started, a `DELETE' statement 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-file option to populate the `MEMORY' table on the
master at startup, it ensures that this time interval is zero.
* 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.
*Additional resources*
* A forum dedicated to the `MEMORY' storage engine is available at
`http://forums.mysql.com/list.php?92'.
Info Catalog
(mysql.info) merge-storage-engine
(mysql.info) storage-engines
(mysql.info) bdb-storage-engine
automatically generated byinfo2html