Storage engines

MySQL pluggable storage engines isolate how data is stored and locked. InnoDB is the default and the right choice for most OLTP workloads.

| Engine | Notes | |--------|-------| | InnoDB | ACID transactions, row-level locking, crash recovery, FKs | | MyISAM | Legacy; table locks, no transactions — avoid for new apps | | MEMORY | Fast ephemeral tables; lost on restart |

					SHOW TABLE STATUS WHERE Name = 'orders';
-- Engine column shows InnoDB vs others

CREATE TABLE audit_log (
  id BIGINT PRIMARY KEY,
  payload JSON
) ENGINE=InnoDB;
				

InnoDB clusters the primary key with the table (clustered index). Secondary indexes store primary key values as pointers — primary key design affects all lookups. Engine choice impacts backups (logical vs physical), replication, and lock granularity.

On interviews: explain why InnoDB is default, contrast row vs table locking, and mention when a special engine (MEMORY for temp aggregates) might appear.

Common pitfalls: legacy MyISAM tables in production; assuming MyISAM "faster" for reads; mixing engines in one transactional workflow; ignoring engine-specific backup tools.

The trade-off is feature richness and safety (InnoDB) versus marginal micro-benchmark wins from obsolete engines that sacrifice transactions.

Checklist:

• Default new tables to InnoDB.
• Know clustered PK implications.
• Audit legacy SHOW TABLE STATUS outliers.
• Align backup strategy with InnoDB redo/undo behavior.