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:
- Very fast, in memory
- Btree indexes also supported
- Non-transactional
- Ideal for primary key lookups
- Supports the hash index by default
Limitations Memory storage engine:
- Does not support transactions
- Data not persistent
- Table level locking on DML and DDL statements
- Does not support TEXT/BLOB data types
- No ability to limit the total amount of memory for all Memory tables
- Fixed row widthImportant Parameters of Memory storage engine :
- 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.
- tmp_table_size: Defines the maximum size of the table when used for internally temporary tables.
- 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.
