Source code editor What Is Ajax
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 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
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:
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.
MEMORY storage engine implements both
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 Section 7.4.5, “How MySQL Uses 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
MEMORY tables use a fixed-length row storage format.
MEMORY tables cannot contain
MEMORY includes support for
You can use
INSERT DELAYED with
MEMORY tables. See Section 126.96.36.199, “
INSERT DELAYED Syntax”.
MEMORY tables are shared among all clients (just like any other non-
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
TRUNCATE TABLE, or remove the table altogether using
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 Section 5.2.2, “Command Options”, and Section 13.2.5, “
LOAD DATA INFILE Syntax”.
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:
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
char pointer size.
sizeof(char*) is 4 on 32-bit machines and 8 on 64-bit machines.
A forum dedicated to the
MEMORY storage engine is available at http://forums.mysql.com/list.php?92.
Source code editor What Is Ajax