The
MEMORY
(
HEAP
) Storage Engine
1305
• The maximum number of rows in a
MERGE
table is 2
32
(~4.295E+09; the same as for a
MyISAM
table). It is not possible to merge multiple
MyISAM
tables into a single
MERGE
table that would have
more than this number of rows. However, if you build MySQL using the
--with-big-tables
[123]
option, then the maximum number of rows is increased to 2
64
(1.844E+19); for more information, see
Section 2.17.3, “MySQL Source-Configuration Options”
.
Note
As of MySQL 5.0.4, all standard binaries are built with this option.
• The
MERGE
storage engine does not support
INSERT DELAYED
statements.
• Using
MERGE
on underlying
MyISAM
tables that have different row formats is possible.
• In some cases, differing
PACK_KEYS
table option values among the
MERGE
and underlying
tables cause unexpected results if the underlying tables contain
CHAR
or
BINARY
columns. As a
workaround, use
ALTER TABLE
to ensure that all involved tables have the same
PACK_KEYS
value.
(Bug #50646)
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.
The
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
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 engine 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 64 indexes per table, 16 columns per index and a maximum key
length of 3072 bytes.
• 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:
Summary of Contents for 5.0
Page 1: ...MySQL 5 0 Reference Manual ...
Page 18: ...xviii ...
Page 60: ...40 ...
Page 396: ...376 ...
Page 578: ...558 ...
Page 636: ...616 ...
Page 844: ...824 ...
Page 1234: ...1214 ...
Page 1427: ...MySQL Proxy Scripting 1407 ...
Page 1734: ...1714 ...
Page 1752: ...1732 ...
Page 1783: ...Configuring Connector ODBC 1763 ...
Page 1793: ...Connector ODBC Examples 1773 ...
Page 1839: ...Connector Net Installation 1819 2 You must choose the type of installation to perform ...
Page 2850: ...2830 ...
Page 2854: ...2834 ...
Page 2928: ...2908 ...
Page 3000: ...2980 ...
Page 3122: ...3102 ...
Page 3126: ...3106 ...
Page 3174: ...3154 ...
Page 3232: ...3212 ...
