MySQL INNODB vs ISAM

[nemonoman]

I would very much like to hear about the pros and cons of INNODB vs ISAM tabletypes from anyone with real-world experience.

[[deleted]]

InnoDB has row-level locking and transactions, myisam does not.

This makes innodb faster and more reliable. You can undo-queries when there is an error, which means your data doesn't get F-ed up.

[nemonoman]

That's what's in the manual. There are some rather oblique but nonetheless scary references to runaway rollbacks and huge tablespaces. Any experience you'd care to share?

[[deleted]]

As for huge tablespaces, innodb creates datafiles of a given size which it fills as required. Yes you do get huge files that are 90% empty at first, but the space is reserved, so if something else is hogging all the disk-space then innodb can still write new records even if there is no free disk space.

As for runaway rollbacks, I've never had them, but then again I've never had a mysql/innodb combination crash and recover.

I suggest you look at PostgreSQL, which is also free. It is a transactional database with infinately more functionality than mysql.