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 the MySQL data dictionary, 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  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  value. You can set the size for individual tables as described later in this section. 

## Indexes 

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: 

CREATE TABLE lookup (id INT, INDEX USING HASH (id)) ENGINE = MEMORY; CREATE TABLE lookup (id INT, INDEX USING BTREE (id)) ENGINE = MEMORY; 

For general characteristics of B-tree and hash indexes, see Section 8.3.1, “How MySQL Uses Indexes” . 

MEMORY  tables can have up to 64 indexes per table, 16 columns per index and a maximum key length of 3072 bytes. 

If a MEMORY  table hash index 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. 

MEMORY  tables can have nonunique keys. (This is an uncommon feature for implementations of hash indexes.) 

## User-Created and Temporary Tables 

Columns that are indexed can contain NULL  values. 

MEMORY  table contents are stored in memory, which is a property that MEMORY  tables share with internal temporary 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 temporary tables are: 

•If an internal temporary table becomes too large, the server automatically converts it to on-disk storage, as described in Section 8.4.4, “Internal Temporary Table Use in MySQL” . 

