History, and why MySQL is shaped this way

In 1994 Michael “Monty” Widenius was working at TcX, a Swedish consultancy building data warehousing tools. They had a data store called UNIREG and a need to serve web-scale-for-1994 queries over it. Monty first tried to wire mSQL — a small, popular SQL server of the era — on top of UNIREG's table handler. It was too slow and too limited.

So he wrote his own SQL layer over the same low-level storage code, keeping mSQL's C API almost exactly so existing tools would port easily. That storage code was an ISAM implementation: Indexed Sequential Access Method, a 1960s IBM design where records sit in a file and a separate index gives you keyed access to them.

This origin matters more than it sounds. MySQL was not designed as a database from first principles. It was designed as a SQL interface bolted onto an existing fast file-based table handler, for a workload that was overwhelmingly reads, on a single machine, where losing a little data in a crash was survivable and being slow was not.

The name is domestic rather than technical: Monty's daughter is called My. (His later projects, MariaDB and the Maria storage engine, are named after another daughter, Maria.) The dolphin is called Sakila, picked from a name-the-dolphin contest in 2002.

MyISAM arrived in MySQL 3.23 (2001) as the improved successor to that original ISAM code, and it was the default engine until 5.5 shipped in 2010. For most of MySQL's cultural ascendancy — the LAMP stack, the first decade of PHP — MyISAM was MySQL.

Its defining property: table-level locking. A write to any row takes an exclusive lock on the whole table. Every other writer, and under some conditions every reader, waits.

That sounds indefensible now. In 2001 it was reasonable, because of what the typical workload looked like:

• Reads outnumbered writes by something like 50:1 on a content site.
• Writes were short — an INSERT of one row, not a transaction spanning twenty statements and a network round trip.
• Concurrent connections numbered in the dozens, not the thousands.
• The machine had one or two cores, so contention was limited by hardware anyway.

Under those conditions a table lock held for a millisecond is invisible, and you get in exchange an engine with no transaction overhead, no MVCC bookkeeping, no undo log, and a very compact on-disk footprint. MyISAM's COUNT(*) is instant because it stores the row count in the table header — it can do that precisely because it has no MVCC, so there is exactly one version of the truth.

MyISAM is deprecated and you should never choose it. It appears in this course for two reasons: it explains inherited behavior in the server layer, and its limitations are exactly the design brief that InnoDB was written to answer.

In 1995 a Finnish researcher named Heikki Tuuri founded Innobase Oy to build a transactional storage engine. InnoDB was folded into MySQL in 2001 (version 3.23.34a) as an option, not a default.

InnoDB was, and is, a genuinely different piece of software from the rest of MySQL. It brought the whole textbook: MVCC, row-level locking, a redo log, undo logs, a buffer pool, crash recovery, foreign keys, and the clustered index layout that determines the physical shape of your data. Much of the rest of this course is really a course about InnoDB.

Then the corporate sequence, which is genuinely important:

When Oracle acquired Sun, Monty left and forked MySQL into MariaDB, on the argument that a database owned by the vendor of the leading commercial database could not be trusted to stay open. He campaigned publicly against the acquisition during the EU review.

The fork has diverged much further than people assume. It is not a drop-in replacement any more:

Percona occupies a third position: a services company shipping Percona Server, a drop-in MySQL build with extra instrumentation and performance patches, plus the tooling that most serious MySQL operators actually use — pt-query-digest, pt-online-schema-change, XtraBackup. When this course reaches production operations, several of the tools are Percona's, not Oracle's.

Version trivia is worthless; knowing which release changed a behavior you depend on is not. This is the filtered list — the changes that alter how you design or debug.

-- What are you actually running? Check before you trust any advice.
SELECT VERSION();

-- The defaults that changed most recently and bite hardest.
SHOW VARIABLES WHERE Variable_name IN (
  'default_authentication_plugin',   -- gone in 8.4, replaced below
  'authentication_policy',
  'sql_mode',                          -- STRICT_TRANS_TABLES since 5.7
  'character_set_server',             -- utf8mb4 since 8.0
  'default_storage_engine',
  'innodb_redo_log_capacity'          -- 8.0.30+ only
);

This is the architectural decision that defines MySQL. The server is split in two, with a hard interface between the halves:

Everything above the line — connection handling, parsing, privileges, the optimizer, stored programs, the binary log — is engine-agnostic. Everything below it is the engine's business: how rows are physically stored, whether there are transactions, what locking granularity exists, how indexes work.

The interface itself is a C++ class called handler, with roughly a hundred virtual methods. An engine implements the ones it supports: rnd_next() to scan, index_read() to seek, write_row(), update_row(), delete_row(), plus a bitmask advertising its capabilities.

What this buys, and what it costs

The benefit is obvious: one SQL layer, many storage strategies. You can put an in-memory engine, an archive engine, a federated engine and a full transactional engine behind the same SQL.

The cost is subtler and shows up constantly once you know to look for it. The server layer has to make decisions using only what the interface exposes, which means:

• The optimizer costs a plan using estimates the engine hands over through a narrow interface. It cannot see the actual page layout. This is why EXPLAIN row counts are often wildly wrong — you meet this properly in chapter 78.
• There are two write-ahead logs: InnoDB's redo log below the line, and the server's binary log above it. They must agree on whether a transaction committed, which requires an internal XA two-phase commit on every single commit. That is chapter 90, and it is a real throughput cost.
• Some engines silently ignore what they cannot do. Declare a foreign key on a MyISAM table and it is parsed, accepted, and discarded.

-- Which engines does this server have, and what can they do?
SHOW ENGINES;

-- Watch the seam: the parser accepts a foreign key that
-- the engine will quietly throw away.
CREATE TABLE parent (id INT PRIMARY KEY) ENGINE=MyISAM;
CREATE TABLE child (
  id  INT PRIMARY KEY,
  pid INT,
  FOREIGN KEY (pid) REFERENCES parent(id)
) ENGINE=MyISAM;

SHOW CREATE TABLE child\G
-- The FOREIGN KEY clause is absent. It was accepted and dropped.
-- Now do the same with ENGINE=InnoDB and diff the output.

DROP TABLE child, parent;

You will be asked to compare these. Most answers are a list of features, which is the Senior answer. The Staff answer is that they made two different foundational choices, and nearly every observable difference follows from those choices.

Choice one: where the row lives

InnoDB clusters the table into the primary key's B+tree. The leaf node is the row. There is no separate heap.

Postgres stores rows in an unordered heap and every index, including the primary key, points into it.

Consequences you can derive rather than memorize:

• In InnoDB, primary key order is physical order. Sequential keys append to one page; random keys (UUIDv4) scatter writes across the whole tree. That is chapter 31, and it is one of the highest-impact schema decisions you make.
• In InnoDB, a secondary index stores the primary key, not a row pointer — so a wide primary key inflates every secondary index, and a non-covering lookup costs two tree descents (chapter 26).
• In Postgres, an UPDATE writes a whole new tuple and leaves the old one behind, which is why VACUUM and bloat are Postgres's defining operational concern, and why InnoDB's equivalent concern is instead the undo history list growing under a long transaction (chapter 45).

Choice two: where old row versions go

Both do MVCC; they put the old versions in different places. Postgres keeps them in the heap alongside live rows. InnoDB keeps them in a separate undo log and rebuilds old versions on demand by walking a pointer chain backwards.

That is the archaeology. Part 1 follows a single SELECT from the socket all the way to the bytes on disk, and every part after it zooms into one stage of that path.