29.5. The MEMORY Engine

29.5. The MEMORY Engine

The MEMORY storage engine manages tables that have the following characteristics:

• Each MEMORY table is represented on disk by an .frm format file in the database directory. Table data and indexes are stored in memory.

• In-memory storage results in very fast performance.

• MEMORY table contents do not survive a restart of the server. The table structure itself survives, but the table contains zero data rows after a restart.

• MEMORY tables use up memory (obviously), so they should not be used for large tables.

• MySQL manages query contention for MEMORY tables using table-level locking. Deadlock cannot occur.

• MEMORY tables cannot contain TEXT or BLOB columns.

The MEMORY stored engine formerly was called the HEAP engine. You might still see HEAP in older SQL code, and MySQL Server still recognizes HEAP for backward compatibility.

29.5.1. MEMORY Indexing Options

The MEMORY storage engine supports two indexing algorithms, HASH and BTREE:

• MEMORY tables use hash indexes by default. This index algorithm provides very fast lookups for all operations that use a unique index. However, hash indexes are usable only for comparisons that use the = or <=> operator.

• The BTREE index algorithm is preferable if the indexed column will be used with comparison operators other than = or <=>. For example, BTREE can be used for range searches such as id < 100 or id BETWEEN 200 AND 300.

The syntax for indicating which algorithm to use when creating a MEMORY table index is given in Section 8.6.3, "Choosing an Indexing Algorithm."