Share via:


We covered MyISAM & InnoDB in previous article.


In this article we will cover one more important storage engine called MEMORY STORAGE.ENGINE.



Memory storage engine creates tables in memory. It is the fastest engine. It provides table-level locking. It does not support transactions. Memory storage engine is ideal for creating temporary tables or quick lookups. The data is lost when the database is restarted.

Characteristics of Memory Storage Engines :

  • Represented by .frm file.
  • Table data and indexes stored in memory
  • Storage very fast
  • fixed-lengths rows
  • Table contents do not servive restart
  • Can limit file size using –max-heap-table-size 
  • Table level locking
  • Cannot obtain TEXT or BLOB
  • Memory storage engine formally called Heap engine


Let’s create database db3 and some objects into it.

Check at  physical layer

Only t1.frm file will created .

The data will be stored in memory only

Will try to insert data into t1  table.

Here in t3 table we have 3 records but when we restart the service this table data will flushed out from memory.

Now check the records in t1 table.

Data will be flushed out .But structure remains same because of .frm file


What size will be stored in memory ?

The table data will be stored in temp table in the database.

To check the size of temp table follow below command.

The temp table size is 16MB  and maximum size will be 32 MB

When to Use MEMORY storage engine:

  • Operations involving transient, non-critical data such as session management or caching.
  • In-memory storage for fast access and low latency. Data volume can fit entirely in memory without causing the operating system to swap out virtual memory pages.
  • By default, an index key for a single-column index can be up to 767 bytes. The same length limit applies to any index key prefix.
  • The InnoDB internal maximum key length is 3500 bytes, but MySQL itself restricts this to 3072 bytes (combined index key in a multi-column index).
  • The maximum row length except for variable-length columns (VARBINARY, VARCHAR, BLOB and TEXT), is about 8000 bytes for the default page size of 16KB.
  • Internally InnoDB supports row sizes larger than 65,535 bytes, but MySQL itself imposes a row-size limit of 65,535 for the combined size of all columns.
  • The maximum tablespace size is four billion database pages (64TB) and the minimum tablespace size is slightly larger than 10MB.


ARCHIVE Storage Engine

The ARCHIVE storage engine is used to store large amounts of unindexed data in a very small footprint. The storage engine is included in MySQL binary distributions. To enable this storage engine (if you build MySQL from source), invoke CMake with the -DWITH_ARCHIVE_STORAGE_ENGINE option. When you create an ARCHIVE table, the server creates a table format file (.frm extension) in the database directory.

Features of ARCHIVE storage engine:

Storage limits None Transactions No Locking granularity Table
MVCC No Geospatial data type support Yes Geospatial indexing support No
B-tree indexes No T-tree indexes No Hash indexes No
Full-text search indexes No Clustered indexes No Data caches No
Index caches No Compressed data Yes Encrypted data Yes
Cluster database support No Replication support Yes Foreign key support No
Backup / point-in-time recovery Yes Query cache support Yes Update statistics for data dictionary Yes

ARCHIVE storage engine supports

  • ORDER BY operations
  • BLOB columns
  • AUTO_INCREMENT column attribute. The AUTO_INCREMENT column can have either a unique or nonunique index.
  • AUTO_INCREMENT table option in CREATE TABLE statements

ARCHIVE storage engine does not support

  • Inserting a value into an AUTO_INCREMENT column less than the current maximum column value.

ARCHIVE storage engine: Storage & Retrieval

  • The ARCHIVE engine uses zlib lossless data compression (see
  • Rows are compressed as they are inserted.
  • On retrieval, rows are uncompressed on demand; there is no row cache.

MySQL: EXAMPLE Storage Engine

The EXAMPLE storage engine is a stub engine that does nothing and serve as an example in the MySQL source code that clarify how to begin writing new storage engines. To examine the source for the EXAMPLE engine, look in the storage/example directory of a MySQL source distribution. When you create an EXAMPLE table :

  • The server creates a table format file (.frm extension) in the database directory.
  • No other files are created
  • No data can be stored into the table.
  • Retrievals return an empty result.
  • Does not support indexing.

To enable the EXAMPLE storage engine if you build MySQL from source, invoke CMake with the -DWITH_EXAMPLE_STORAGE_ENGINE option.


Thank you for giving your valuable time to read the above information. Please click here to subscribe for further updates

KTEXPERTS is always active on below social media platforms.


Facebook :
LinkedIn :
Twitter :
YouTube :
Instagram :

Share via:
Note: Please test scripts in Non Prod before trying in Production.
1 Star2 Stars3 Stars4 Stars5 Stars (2 votes, average: 5.00 out of 5)

Add Comment