Understanding MySQL Storage Engines: InnoDB, MyISAM, and MEMORY Explained

1. Storage Engine Concept

In simple terms, a storage engine in MySQL is a technology for storing and retrieving data. It determines how data is stored, processed, and retrieved in the database.

2. MySQL Storage Engines

MySQL operates storage engines per table; each table can use a different engine. Common engines include:

InnoDB : default since MySQL 5.5, supports transactions, row-level locking, foreign keys, designed to minimize disk I/O.

MyISAM : default before MySQL 5.5, does not support transactions or row-level locking, performs well for read‑intensive workloads.

MEMORY : stores data in memory for fast temporary access; data is lost on restart.

3. Where MySQL Stores Data

Data is stored as files in the filesystem according to the database and table's storage engine.

Database: each database corresponds to a directory under MySQL's data directory, named after the database.

Table: each table's files reside in its database directory; file types and count depend on the storage engine.

4. InnoDB Tables

Since MySQL 5.5, InnoDB is the default engine. By default MySQL uses a shared tablespace (ibdata1) where all InnoDB tables store data and indexes.

MySQL also supports “file‑per‑table” mode, where each InnoDB table has its own .ibd file, allowing finer‑grained management.

Shared Tablespace Mode

All InnoDB tables store data and indexes in a single shared tablespace file (ibdata1).

Storage method: data and indexes are not stored per database directory but centrally.

File size: the shared file can grow large as data accumulates.

Backup and recovery: whole‑instance backup is simplified, but per‑table backup is more complex because data is mixed.

File‑Per‑Table Mode

Each InnoDB table uses its own .ibd file, stored in the respective database directory.

How to Switch Mode

Set the innodb_file_per_table option in mysqld configuration.

# Affects tables created after this change; existing tables keep their current storage unless recreated or migrated.
[mysqld]
innodb_file_per_table=1

5. MyISAM Tables

Before MySQL 5.5, MyISAM was the default engine. Each table consists of three files: .frm (table definition), .MYD (data), and .MYI (indexes).

6. Changing the Default Storage Engine

Modify the default-storage-engine setting in the mysqld section.

[mysqld]
default-storage-engine=engine_name

Viewing a Table’s Storage Engine

Use SHOW TABLE STATUS LIKE 'tb_name'; to see the engine type.

SHOW TABLE STATUS LIKE 'tb_name';

To change a table’s engine:

ALTER TABLE tablename ENGINE = new_engine;