(mysql.info.gz) MEMORY storage engine
Info Catalog
(mysql.info.gz) MERGE storage engine
(mysql.info.gz) Storage engines
(mysql.info.gz) BDB storage engine
14.3 The `MEMORY' (`HEAP') Storage Engine
=========================================
The `MEMORY' storage engine creates tables with contents that are stored
in memory. Before MySQL 4.1, `MEMORY' tables are called `HEAP' tables.
As of 4.1, `HEAP' is a synonym for `MEMORY', and `MEMORY' is the
preferred term.
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 a `MEMORY' table, indicate that with
an `ENGINE' or `TYPE' table option:
CREATE TABLE t (i INT) ENGINE = MEMORY;
CREATE TABLE t (i INT) TYPE = HEAP;
`MEMORY' tables are stored in memory and use hash indexes by default.
This makes them very fast, and very useful for creating temporary
tables. However, when the server shuts down, all data stored in
`MEMORY' tables is lost. The tables continue to exist because their
definitions are stored in the `.frm' files on disk, but their contents
will be empty when the server restarts.
Here is an example that shows how you might create, use, and remove a
`MEMORY' table:
mysql> CREATE TABLE test TYPE=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. The tables
use 100% dynamic hashing (on inserting). No overflow areas and no
extra key space are needed. There is no extra space 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
don't have problems with deletes plus inserts, which is common
with hashed tables.
* `MEMORY' tables allow up to 32 indexes per table, 16 columns per
index, and a maximum key length of 500 bytes.
* Before MySQL 4.1, the `MEMORY' storage engine implements only hash
indexes. From MySQL 4.1 on, hash indexes are still the default,
but you can specify explicitly that a `MEMORY' table index should
be `HASH' or `BTREE' by adding a `USING' clause:
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 will be significantly slower. The degree of 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 use a fixed record length format.
* `MEMORY' doesn't support `BLOB' or `TEXT' columns.
* `MEMORY' doesn't support `AUTO_INCREMENT' columns before MySQL
4.1.0.
* Prior to MySQL 4.0.2, `MEMORY' doesn't support indexes on columns
that can contain `NULL' values.
* `MEMORY' tables are shared between 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 enough extra memory to maintain all `MEMORY'
tables that are in use at the same time.
* To free memory used by a `MEMORY' table if you no longer require
its contents, you should execute `DELETE' or `TRUNCATE TABLE', or
else remove the table with `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 the file to load the table from some persistent data
source. Server options.
* 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 will
return out-of-date content if you select data from them.
Beginning with MySQL 4.0.18, when a `MEMORY' table is used on the
master for the first time since the master's startup, a `DELETE
FROM' 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 out-of-date
data in the table during the interval between the master's restart
and its first use of the table. But if you use the `--init-file'
option to populate the `MEMORY' table on the master at startup, it
ensures that the failing 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.
Info Catalog
(mysql.info.gz) MERGE storage engine
(mysql.info.gz) Storage engines
(mysql.info.gz) BDB storage engine
automatically generated byinfo2html