A Slow Bulk Load Sent Me Back to ACID: MyISAM vs InnoDB

Reflecting on some recent challenges, I spent a week revisiting concepts I thought I already understood — and was glad I did.

We migrated a MySQL database from MyISAM to InnoDB. On paper it looked routine. Then bulk CSV ingestion slowed down significantly after the migration, and I realised this wasn't something to fix by reciting best practices. I needed to actually understand why the two storage engines behave so differently.

That sent me back to the foundations: ACID.

The four guarantees, revisited

• Atomicity — every operation in a transaction either completes fully or not at all. No partial updates left behind if something fails midway.
• Consistency — the database only ever moves from one valid state to another, with foreign keys, constraints, and rules consistently enforced.
• Isolation — concurrent transactions don't interfere with each other. InnoDB delivers this with row-level locks for writes and MVCC snapshot reads for reads, so readers and writers aren't fighting over the same coarse lock.
• Durability — once a transaction commits, it survives a crash. The redo log ensures committed changes make it to disk promptly, so a power loss a millisecond after commit doesn't lose your data.

Every one of those guarantees costs something at write time. Locks have to be taken and released. MVCC has to track row versions. The redo log has to be written and flushed. That overhead is the price of safety — and it's paid on every single insert.

Why MyISAM looked "faster"

MyISAM bypasses these principles. No transactions, no row-level locking, no redo log in the InnoDB sense. That's precisely why it can rip through a bulk load faster: it isn't doing the bookkeeping that keeps data correct under concurrency and crashes.

So the post-migration slowdown wasn't a defect to undo. It was InnoDB doing its job — trading some raw bulk-insert throughput for the integrity guarantees you actually want behind a production application managing real data. MyISAM's speed on bulk loads comes from skipping exactly the protections that make a database trustworthy.

Framed that way, the "regression" stops looking like a problem and starts looking like a correctly-priced tradeoff. The right response isn't to mourn MyISAM's speed; it's to work with InnoDB's transactional model rather than against it.

The takeaway

It's easy to operate on best-practice autopilot — "use InnoDB for production," and move on. But a best practice you don't understand is just cargo-culting until the day it surprises you.

Sometimes a blocker is exactly what it takes to deepen your understanding of a concept you thought you'd already filed away. This one pushed me from knowing InnoDB is the right default to actually understanding what that default buys — and what it costs.