Memory storage engine

The Memory storage engine, also known historically as the Heap storage engine, is an in-memory
only table that does not provide data persistence. The Memory storage engine is actually used internally
by the mysql kernel when a temporary table is required.
Key Features of Memory storage engine:

  1. Very fast, in memory
  2. Btree indexes also supported
  3. Non-transactional
  4. Ideal for primary key lookups
  5. Supports the hash index by default

 

Limitations Memory storage engine:

  1. Does not support transactions
  2. Data not persistent
  3. Table level locking on DML and DDL statements
  4. Does not support TEXT/BLOB data types
  5. No ability to limit the total amount of memory for all Memory tables
  6. Fixed row widthImportant Parameters of Memory storage engine :

 

  1. init_file: Defines a SQL file of commands that are executed when the MySQL instance is started. Used as a means to seed Memory tables.
  2. tmp_table_size: Defines the maximum size of the table when used for internally temporary tables.
  3. max_heap_table_size: Defines the maximum size of a single Memory table.When using large memory tables, a consideration is table-level locking. While adding an index is generally considered a method for performance tuning SQL queries, with memory tables you need to factor the size of the memory table, the cost of maintaining the index, and the type of index.