DDL Operations for MEMORY Tables-opracowanie

DDL Operations for MEMORY Tables
To create a MEMORY table, specify the clause ENGINE=MEMORY on the CREATE TABLE statement.
CREATE TABLE t (i INT) ENGINE = MEMORY;
As indicated by the engine name, MEMORY tables are stored in memory. They use hash indexes by
default, which makes them very fast for single-value lookups, 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;
The maximum size of MEMORY tables is limited by the max_heap_table_size [519] system variable,
which has a default value of 16MB. To enforce different size limits for MEMORY tables, change the value
of this variable. The value in effect for CREATE TABLE, or a subsequent ALTER TABLE or TRUNCATE
TABLE, is the value used for the life of the table. A server restart also sets the maximum size of existing
MEMORY tables to the global max_heap_table_size [519] value. You can set the size for individual
tables as described later in this section.
... zobacz całą notatkę