What are the differences between InnoDB and MyISAM?

Storage Engine:

• InnoDB: transactional (ACID-compliant), supports foreign keys, row-level locking.
• MyISAM: non-transactional, does not support foreign keys, table-level locking.

Reliability/Recovery:

• InnoDB: high, uses logging for crash recovery.
• MyISAM: low, vulnerable to data corruption on crashes.

Performance:

• InnoDB: better suited for high-write and concurrent access (OLTP).
• MyISAM: better for read-heavy operations (OLAP).

Foreign key support:

• InnoDB: Yes.
• MyISAM: No.

Locking:

• InnoDB: Row-level, allows concurrent writes to different rows.
• MyISAM: Table-level, locks the entire table during writes.

Full-text search support:

• InnoDB: Yes (from version 5.6).
• MyISAM: Yes.

Disk files:

• InnoDB: Usually one or more .ibd files for data and indexes, plus log files.
• MyISAM: Three files per table: .MYD (data), .MYI (indexes), .frm (table definition).

Table size:

• InnoDB: Virtually unlimited.
• MyISAM: Limited by the maximum file size of the operating system (default 256 TB).

Memory usage:

• InnoDB: Uses buffer pool for caching data and indexes.
• MyISAM: Uses key buffer for caching only indexes.

Row count:

• InnoDB: Counts rows during COUNT(*), which can be slow for large tables.
• MyISAM: Stores an exact row count, COUNT(*) is very fast.