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.ExampleCREATE 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, 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.

MEMORY tables can have up to 64 indexes per table

MEMORY tables can have up to 16 columns per index

MEMORY tables can have maximum key length of 3072 bytes.

Space for MEMORY tables is allocated in small blocks. Tables use 100% dynamic hashing for inserts.

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

MEMORY tables can have nonunique keys.

Columns that are indexed can contain NULL values.

MEMORY tables use a fixed-length row-storage format. Variable-length types such as VARCHAR are stored using a fixed length.

MEMORY tables cannot contain BLOB or TEXT columns.

MEMORY includes support for AUTO_INCREMENT columns.

The server needs sufficient memory to maintain all MEMORY tables that are in use at the same time.