Skip to content
Tutorials Center
My learning

Topics

Lesson 40 of 75 ยท Storage Engines and Internals

Storage Engines Overview

Learn what MySQL storage engines are, compare InnoDB, MyISAM, MEMORY and ARCHIVE, see why InnoDB is the default, and avoid the traps of mixing engines in one database.

When you run INSERT or SELECT, MySQL does two jobs. The server layer parses your SQL, checks privileges and decides how to run the query. A storage engine then does the actual work of storing rows on disk or in memory, finding them again, locking them and recovering after a crash. MySQL's architecture is pluggable: the server talks to engines through a common interface, and each table can use a different one.

For modern applications the answer to "which engine?" is almost always InnoDB. This lesson still teaches the others, because you will meet them in old schemas, in SHOW ENGINES output and in interview questions, and because knowing what they lack explains why InnoDB won. Engines are a MySQL server concept (SQLite has a single built-in storage layer), so the MySQL statements are read-only code. Live examples use SQLite to demonstrate the behavior that makes a transactional engine valuable.

Server layer, engine layer

Server layer Parser, optimizer, privileges, caches.
Storage engine Rows, indexes, locks, crash recovery.
Pluggable Chosen per table with ENGINE=.

Same SQL on top, very different behavior underneath.

1 / 2

Seeing what you have

Three commands show the engines your server knows and the ones your tables use:

SQL
SHOW ENGINES;

SELECT TABLE_NAME, ENGINE, TABLE_ROWS
FROM information_schema.TABLES
WHERE TABLE_SCHEMA = 'shop';

SHOW CREATE TABLE orders\G

SHOW ENGINES lists each engine with a Support value: DEFAULT for the engine used when you do not say otherwise, YES for available, NO or DISABLED for not compiled in or turned off. It also has Transactions, XA and Savepoints columns, which is a quick way to see who supports what. SHOW CREATE TABLE ends with the table's ENGINE=... option.

You choose an engine in CREATE TABLE and can change it later:

SQL
CREATE TABLE audit_log (
  id      BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  message VARCHAR(500) NOT NULL
) ENGINE = InnoDB;

ALTER TABLE legacy_table ENGINE = InnoDB;   -- rebuilds the whole table

If you omit ENGINE, the server uses the variable default_storage_engine, which is InnoDB out of the box. Changing an engine copies every row into a new table, so on a big table treat it as a maintenance job (ALTER TABLE and Online DDL). Keep the SQL mode NO_ENGINE_SUBSTITUTION on (it is in the default mode): without it, asking for an engine the server lacks silently gives you another one (SQL Modes and Strictness).

InnoDB: the default

InnoDB has been the default since MySQL 5.5 and is what you should assume everywhere. It provides:

  • Transactions with COMMIT and ROLLBACK, savepoints and full ACID guarantees (Transactions and ACID in InnoDB).
  • Row-level locking and MVCC (multi-version concurrency control), so readers do not block writers and many sessions work on different rows at once.
  • Foreign keys with ON DELETE and ON UPDATE actions.
  • Crash recovery: after a power loss, the redo log replays committed work and the undo log rolls back unfinished work, with no manual repair step.
  • A clustered index: the table's rows are stored in primary-key order, which makes primary-key lookups and range scans very fast (InnoDB Architecture).
  • Online DDL for many ALTER TABLE operations, FULLTEXT and spatial indexes, JSON support and compression options.

In MySQL 8.0 and later the system tables and the data dictionary live in InnoDB as well. The server no longer depends on MyISAM at all.

MyISAM: the legacy engine

MyISAM was the default before 5.5, and old tutorials and old schemas still use it. Its design trades safety for simplicity:

  • No transactions and no foreign keys. Each statement takes effect immediately and cannot be rolled back.
  • Table-level locking. A write locks the whole table, so one slow UPDATE blocks every other query on that table. Concurrent writes scale badly.
  • Not crash safe. After a crash the table may be marked corrupt and need REPAIR TABLE, and recent writes can be lost.
  • Compact on disk, and fast for read-mostly tables with a single user, which is why it was popular for reporting and for simple sites a long time ago.

InnoDB now matches or beats it in most workloads, including full-text search, which InnoDB has supported since 5.6. There are very few reasons to create a new MyISAM table today. If you inherit one, plan to convert it with ALTER TABLE ... ENGINE = InnoDB after checking the table's indexes and any code that relied on table locks.

MEMORY: tables in RAM

The MEMORY engine keeps rows in memory only:

  • The table definition survives a restart, but the rows are gone. It is empty again when the server comes back.
  • Indexes are hash indexes by default, which are very fast for equality (=, IN) and useless for ranges or sorting. You can ask for USING BTREE instead.
  • Rows have a fixed length, so a VARCHAR(255) takes the full space of a CHAR(255), and BLOB and TEXT columns are not allowed.
  • Table-level locking, and size limited by max_heap_table_size.

It fits scratch lookups that can be rebuilt on demand. For caching and sessions, most teams use Redis or Memcached instead, and for temporary results a CREATE TEMPORARY TABLE on InnoDB is usually fine. Be aware that the server uses internal temporary tables for sorting and grouping. In MySQL 8.0 these default to the TempTable engine in memory and spill to InnoDB files on disk, a separate mechanism from tables you create.

ARCHIVE and the specialists

ARCHIVE stores rows compressed with zlib and supports only INSERT and SELECT. You cannot UPDATE or DELETE. It suits write-once log or audit data that you rarely read, although partitioned and compressed InnoDB tables are the more common solution now.

A few others you may see in SHOW ENGINES:

Engine What it is
CSV Each table is a plain comma-separated text file. No indexes. Handy for exchanging data.
BLACKHOLE Accepts writes and throws them away. Used for replication relays and benchmarks.
FEDERATED / FEDERATEDX Reads a table on another MySQL server. Disabled by default.
NDB (NDBCLUSTER) The engine of MySQL Cluster, a distributed in-memory system. A different product with its own rules.
MERGE Presents several identical MyISAM tables as one. Legacy.
PERFORMANCE_SCHEMA Holds the server's own performance tables.

What a transactional engine buys you

The difference between InnoDB and MyISAM shows up the first time something fails halfway. Take a money transfer made of two updates. In a transactional engine, either both happen or neither does. SQLite is transactional too, so it can show the InnoDB side of the story:

SQL All or nothing: a rolled-back transfer

Press Run (or Ctrl + Enter) to see the result here.

Inside the transaction Ana shows 70, but after ROLLBACK she is back to 100 and Ben never changed. On an InnoDB table the same script behaves identically. On a MyISAM table, BEGIN and ROLLBACK are accepted without error, but the first UPDATE is already permanent, so Ana's 30 would disappear for good. The server would only raise a warning that some non-transactional changes could not be rolled back.

Mixing engines: why it hurts

MySQL lets one database contain tables of different engines, and a query can join them. That flexibility is a trap:

  • Transactions stop at the engine boundary. If a transaction touches an InnoDB table and a MyISAM table, ROLLBACK undoes the first and cannot undo the second. The server only warns (warning 1196).
  • Foreign keys cannot link an InnoDB table to a table of another engine.
  • Backups are inconsistent. mysqldump --single-transaction gives a point-in-time copy only of transactional tables, and non-transactional tables need locks (Backup and Restore).
  • Crash recovery differs per table: after a crash InnoDB tables are fine, while a MyISAM table may need repair, and the result can be a database whose tables disagree about what happened.
  • Locking behavior is surprising: a long query on a MyISAM table can block writers, and a join across engines inherits the weakest guarantees.

The practical rule: use InnoDB for every table unless you can name a specific reason not to. Audit an existing database with the information_schema.TABLES query above, and convert stragglers.

A quick decision table

The capabilities above are small enough to keep in a table and query. This is plain data in SQLite, not a MySQL feature, but it makes the trade-offs easy to filter:

SQL Which engine meets which requirement?

Press Run (or Ctrl + Enter) to see the result here.

Only InnoDB passes the first test, and it also scores 3 out of 3 in the last query. Only MEMORY loses its rows on restart. The point of the exercise: if you need transactions, constraints and durability together, InnoDB is the only built-in engine that gives all of them, and that is the whole story behind the default.

What ran live here

The rolled-back transfer and the decision table ran in SQLite, which is transactional like InnoDB. SHOW ENGINES, ENGINE= table options, the behavior of MyISAM, MEMORY and ARCHIVE, and the warnings about mixed engines are MySQL server features and are shown as read-only code and text.

Check your understanding

Quick check ยท 5 questions

of 5 answered You scored / 5 ยท perfect!

  1. 1. Which engine is the default in MySQL 8?

    InnoDB has been the default since MySQL 5.5, and in 8.0 the system tables use it too.
  2. 2. What happens to the rows of a MEMORY table when the server restarts?

    MEMORY keeps data in RAM only. After a restart the table exists but is empty.
  3. 3. A transaction updates an InnoDB table and a MyISAM table, then runs ROLLBACK. What is the result?

    MyISAM cannot roll back. The InnoDB change is undone, the MyISAM change stays, and a warning is raised.
  4. 4. Which statement about MyISAM is true?

    MyISAM locks the whole table for writes, lacks transactions and foreign keys, and is not crash safe.
  5. 5. How do you see which engine each table in a schema uses?

    information_schema.TABLES has an ENGINE column. SHOW CREATE TABLE also shows the ENGINE option for one table.

Summary

  • The server layer parses and plans queries. A pluggable storage engine stores, locks and recovers rows, chosen per table with ENGINE=.
  • InnoDB is the default: transactions, row-level locks, MVCC, foreign keys, crash recovery and a clustered primary key.
  • MyISAM is legacy: no transactions or foreign keys, table-level locks, and no crash safety.
  • MEMORY loses its rows on restart, ARCHIVE is compressed insert-and-select only, and CSV, BLACKHOLE, FEDERATED and NDB are specialists.
  • Do not mix engines casually: rollbacks, backups, foreign keys and recovery all suffer. Use InnoDB unless you can name a reason not to.

Next: InnoDB Architecture.