Comparison of MySQL database engines: Difference between revisions

Revision as of 17:14, 30 March 2019

Revision as of 23:13, 7 July 2019

Line 58:

#InnoDB, with innodb_flush_log_at_trx_commit set to 1, flushes the transaction log after each transaction, greatly improving reliability.<ref name=trx_commit>[http://dev.mysql.com/doc/refman/5.5/en/innodb-parameters.html#sysvar_innodb_flush_log_at_trx_commit MySQL 5.5 Reference Manual - InnoDB Startup Options and System Variables]</ref> MyISAM has to be run on top of a fully [[Journaling file system|journaled filesystem]], such as [[ext4]] mounted with data=journal, to provide the same resilience against data file corruption. (The journal can be put on an [[Solid-state drive|SSD]] device for improved MyISAM performance, similarly, the InnoDB log can be placed on a non-journaled filesystem such as [[ext2]] running on an SSD for a similar performance boost. Reliability is not sacrificed in either case.)

#InnoDB can be run in a mode where it has lower reliability but in some cases higher performance. Setting innodb_flush_log_at_trx_commit to 0 switches to a mode where transactions are not committed to disk before control is returned to the caller. Instead, disk flushes happen on a timer.<ref name=trx_commit/>

#InnoDB automatically groups together multiple concurrent inserts and flushes them to disk at the same time.<ref>[{{Cite web |url=http://dev.mysql.com/doc/refman/5.5/en/innodb-performance-group_commit.html |title=MySQL 5.5 Reference Manual - InnoDB Group Commit] |access-date=2011-10-08 |archive-url=https://web.archive.org/web/20111104041340/http://dev.mysql.com/doc/refman/5.5/en/innodb-performance-group_commit.html |archive-date=2011-11-04 |dead-url=yes }}</ref> MyISAM relies on the filesystem block cache for caching reads to the data rows and indexes, while InnoDB does this within the engine itself, combining the row caches with the index caches.<ref>{{cite web|url=http://dev.mysql.com/doc/refman/5.5/en/innodb-storage-engine.html|title=MySQL 5.5 Reference Manual - The InnoDB Storage Engine|accessdate=28 May 2015}}</ref>

#InnoDB will store rows in [[primary key]] order if present, else first [[unique key]] order. This can be significantly faster if the key is chosen to be good for common operations.{{Citation needed|date=May 2009}} If there is no primary key or unique key InnoDB will use an internally generated unique integer key and will physically store records in roughly insert order, as MyISAM does. Alternatively, an autoincrementing primary key field can be used to achieve the same effect.

#InnoDB provides updatable [[Lempel–Ziv–Welch|LZW]] compressed page storage for both data and indexes. MyISAM compressed tables can't be updated.<ref>[http://dev.mysql.com/doc/refman/5.5/en/myisampack.html MySQL 5.5 Reference Manual - myisampack — Generate Compressed, Read-Only MyISAM Tables]</ref>