Comparison of MySQL database engines: Difference between revisions

Revision as of 07:52, 29 December 2018

Revision as of 17:14, 30 March 2019

Line 63:

#When operating in fully [[ACID]]-compliant modes, InnoDB must do a flush to disk at least once per transaction, though it will combine flushes for inserts from multiple connections. For typical hard drives or arrays, this will impose a limit of about 200 update transactions per second. For applications which require higher transaction rates, disk controllers with write caching and battery backup will be required in order to maintain transactional integrity. InnoDB also offers several modes which reduce this effect, naturally leading to a loss of transactional integrity guarantees, though still retaining greater reliability than MyISAM. MyISAM has none of this overhead, but only because it does not support transactions.

#MyISAM uses table-level locking on updates and deletes to any existing row, with an option to append new rows instead of taking a lock and inserting them into free space. InnoDB uses row-level locking. For large database applications where many rows are often updated, row-level locking is crucial because a single table-level lock significantly reduces concurrency in the database.

#Both InnoDB and MyISAM support [[Full text search|full-text search]], with InnoDB gaining full-text index support in MySQL 5.6.4,<ref>{{cite web|url=https://dev.mysql.com/doc/relnotes/mysql/5.6/en/news-5-6-4.html|title=Changes in MySQL 5.6.4 (2011-12-20, Milestone 7)|publisher=Oracle|date=December 12, 2011}}</ref> but the results can be notably different.<ref>{{cite web|url=http://www.mysqlperformanceblog.com/2013/03/04/innodb-full-text-search-in-mysql-5-6-part-2-the-queries/|title=InnoDB Full-text Search in MySQL 5.6: Part 2, The Queries!|publisher=MySQL Performance Blog|date=March 4, 2013}}</ref>

== References ==