Other Engines: MEMORY, ARCHIVE, CSV and More

Besides InnoDB, MySQL ships several special-purpose engines. They exist for narrow jobs. Knowing what they do helps you recognise them, and know when not to use them.

MEMORY: tables in RAM

A MEMORY table keeps its rows in memory. It is fast, and loses all rows when the server restarts (the table definition stays, the data does not).

1
2
3
4
5
6
7
8

CREATE TABLE session_cache (
    token CHAR(16) NOT NULL PRIMARY KEY,
    user_id INT NOT NULL,
    note VARCHAR(10) NULL
) ENGINE = MEMORY;
INSERT INTO session_cache VALUES ('abc123', 7, 'x');
SELECT * FROM session_cache;
SELECT table_name, engine, row_format FROM information_schema.tables WHERE table_name = 'session_cache';

1
2
3
4
5
6
7
8
9
10
11
12
13

+--------+---------+------+
| token  | user_id | note |
+--------+---------+------+
| abc123 |       7 | x    |
+--------+---------+------+
1 row in set

+---------------+--------+------------+
| TABLE_NAME    | ENGINE | ROW_FORMAT |
+---------------+--------+------------+
| session_cache | MEMORY | Fixed      |
+---------------+--------+------------+
1 row in set

Limits: it does not support TEXT or BLOB, rows have a fixed length (a VARCHAR(10) always takes the full width, which is why row_format is Fixed), it uses table-level locking, and its size is limited by max_heap_table_size. It supports hash indexes (USING HASH).

Use it for: small lookup caches that you can rebuild, scratch tables during an import. In modern systems, a real cache (Redis, Memcached) or a normal InnoDB table in the buffer pool does the job better. Do not store anything you need to keep.

(MySQL itself uses internal MEMORY/TempTable tables for temporary results during queries.)

ARCHIVE: compressed, insert-only

ARCHIVE stores rows compressed, and supports only INSERT and SELECT:

1
2
3

CREATE TABLE audit_archive (id INT NOT NULL, note VARCHAR(20)) ENGINE = ARCHIVE;
INSERT INTO audit_archive VALUES (1, 'login'), (2, 'logout');
SELECT * FROM audit_archive;

1
2
3
4
5
6
7

+----+--------+
| id | note   |
+----+--------+
|  1 | login  |
|  2 | logout |
+----+--------+
2 rows in set

1

UPDATE audit_archive SET note = 'changed' WHERE id = 1;

1

ERROR 1031 (HY000): Table storage engine for 'audit_archive' doesn't have this option

Rows can never be updated or deleted individually, and there is no transaction support. It was meant for log and audit data that is written once and rarely read. Today a compressed InnoDB table (ROW_FORMAT=COMPRESSED, or page compression) or a partitioned table you can drop by date does the same job and stays transactional and recoverable.

CSV: plain text files

A CSV table is stored as a comma-separated text file that other programs (a spreadsheet) can read:

1
2
3

CREATE TABLE export_rows (id INT NOT NULL, label VARCHAR(10) NOT NULL) ENGINE = CSV;
INSERT INTO export_rows VALUES (1, 'a'), (2, 'b');
SELECT * FROM export_rows ORDER BY id;

1
2
3
4
5
6
7

+----+-------+
| id | label |
+----+-------+
|  1 | a     |
|  2 | b     |
+----+-------+
2 rows in set

It has no indexes, no NULL values (columns must be NOT NULL), no transactions. It is a niche tool for data exchange. SELECT ... INTO OUTFILE and LOAD DATA are usually better (LOAD DATA).

BLACKHOLE: accepts writes and throws them away

A BLACKHOLE table accepts inserts and stores nothing. A SELECT always returns an empty result. It is used in replication setups: a relay server with BLACKHOLE tables still writes to the binary log, so that other servers can copy the changes without storing the data. It can also measure the cost of the binary log alone.

FEDERATED: tables on another server

FEDERATED makes a table that forwards queries to a table on a remote MySQL server. It is disabled by default (FEDERATED shows NO in SHOW ENGINES, and the engine is not even loaded in the builds used to test this course). It is slow (every query is a network call), fragile and has limited features. Prefer replication, an ETL job, or an application-level join across services.

NDB / NDB Cluster

NDB is the engine of MySQL NDB Cluster, a distributed, in-memory, shared-nothing cluster for very high availability and low latency (telecom, gaming). It is a different product with its own installation. You will not meet it in an ordinary MySQL server (it shows as NO in SHOW ENGINES).

MERGE (MRG_MYISAM)

A way of presenting several identical MyISAM tables as one (for example one per month). Legacy. Partitioning or a UNION ALL view does the job with InnoDB (Partitioning).

PERFORMANCE_SCHEMA

The engine behind performance_schema tables. You do not create tables with it (performance_schema).

Summary

A rule for engine questions

If someone suggests a non-InnoDB engine, ask: “What problem does InnoDB fail to solve here, measured on our data?” For almost every web application the honest answer is “none”, and the engine stays InnoDB.