Re: Transactional vs. NonTranscational For The Layman?
> So is the any reason now to choose MyISAM over InnoDB?
--- Possible showstoppers that would force you to use MyISAM:
* SPATIAL INDEX is recognized in InnoDB, but not used.
* MyISAM is 2x-3x smaller on disk than InnoDB.
* AUTO_INCREMENT in the second column of the PRIMARY KEY.
--- Ways where MyISAM might be beneficial:
* Galera (Hence PXC) will not replicate MyISAM table, hence they are useful for temporary tables when computing things for insertion. Even without Galera, I tend to use MyISAM or MEMORY for 'temp' tables.
* MyISAM _may_ be faster when fetching row(s) via a secondary key.
* You can manipulate (delete, copy, etc) a MyISAM table by moving its 3 files around. This is virtually impossible in InnoDB. (5.6 has some improvements.)
--- Differences that you can probably workaround 'easily' and minor annoyances:
* Per column in an index: MyISAM allows 1000 bytes, InnoDB allows 767 bytes.
* Certain combinations of columns in a table definition can be 'too big' for InnoDB, but will work for MyISAM. (Lots of VARCHAR(255)s.)
* In MyISAM, COUNT(*) without a WHERE clause is fast and exact.
* In MyISAM, SHOW TABLE STATUS (and the equivalent in information_schema) has the exact number of rows.
* There are subtle differences in AUTO_INCREMENT when deleting, crashing, etc. (InnoDB is more likely to burn ids.)
* Replication of MyISAM statements is ordered by the ending of the statements; for InnoDB it is ordered by the COMMITs.
For upgrading to InnoDB:
http://mysql.rjweb.org/doc.php/myisam2innodb