What are the main differences between InnoDB and MyISAM?
First major difference I see is that InnoDB implements row-level lock while MyISAM can do only a table-level lock. You will find better crash recovery in InnoDB. However, it doesn't have
The list can go a bit further. Yet, they both have their unique advantages in their favor and disadvantages against each other. Each of them is more suitable in some scenarios than the other.
So to summarize (TL;DR):
Another major difference not as yet mentioned is how caching for each storage engine is done.
The main mechanism used is the key cache. It only caches index pages from .MYI files. To size your key cache, run the following query:
This will give the Recommended Setting for MyISAM Key Cache (key_buffer_size) given your current data set (the query will cap the recommendation at 4G (4096M). For 32-bit OS, 4GB is the limit. For 64-bit, 8GB.
The main mechanism used is the InnoDB Buffer Pool. It caches data and index pages from InnoDB tables accessed. To size your InnoDB Buffer Pool, run the following query:
This will give the Recommended Setting for the size of the InnoDB Buffer Pool (innodb_buffer_pool_size) given your current data set.
Don't forget to resize the InnoDB Log Files (ib_logfile0 and ib_logfile1). MySQL Source Code places a cap of the combined sizes of all InnoDB Log Files must be < 4G (4096M). For the sake of simplicity, given just two log files, here is how you can size them:
At the End of both queries is a an Inline Query
There is no substitute for common sense. If you have limited memory, a mixture of storage engines, or a combination thereof, you will have to adjust for different scenarios.
Possible scenarios are endless !!!
Remember, whatever you allocate for, leave enough RAM for DB Connections and the Operating System.
One more thing: you can backup InnoDB tables just by taking a snapshot of the filesystem. Backing up MyISAM requires using mysqldump and is not guaranteed to be consistent (e.g. if you insert into a parent and a child table, you might find only the child table's row in your backup).
Basically, if you have another copy of the data and are only caching it in MySQL e.g. to allow a standard means of accessing it from a PHP website, then MyISAM is fine (i.e. it's better than a flat CSV file or a logfile for querying and concurrent access). If the database is the actual "master copy" of the data, if you are doing
(My personal experience: a 2 terabyte DB in MyISAM).
In InnoDB all data in a row except for TEXT and BLOB can occupy 8,000 bytes at most. Full text indexing is not available in InnoDB until MySQL 5.6 (Feb 2013). In InnoDB the
MyISAM has table-level locking, but no row-level locking. No transactions. No automatic crash recovery, but it does offer repair table functionality. No foreign key constraints. MyISAM tables are generally more compact in size on disk when compared to InnoDB tables. MyISAM tables could be further highly reduced in size by compressing with myisampack if needed, but become read-only. MyISAM stores indexes in one file and data in another. MyISAM uses key buffers for caching indexes and leaves the data caching management to the operating system.
Overall I would recommend InnoDB for most purposes and MyISAM for specialized uses only. InnoDB is now the default engine in new MySQL versions.
In my experience, the most significant difference is the way each engine handles locking. InnoDB uses row locking while MyISAM uses table locking. As a rule of thumb, I use InnoDB for write heavy tables and MyISAM for read heavy tables.
Other important differences include:
I tend to view MyISAM as the 'default' table choice for MySQL, so I'll point out the differences for most users of InnoDB
Includes MySQL 5.6 changes
INNODB STORAGE ENGINE:
So, there is no point in using
A bit late to the game...but here's a quite comprehensive post I wrote a few months back, detailing the major differences between MYISAM and InnoDB. Grab a cuppa (and maybe a biscuit), and enjoy.
The major difference between MyISAM and InnoDB is in referential integrity and transactions. There are also other difference such as locking, rollbacks, and full-text searches.
Referential integrity ensures that relationships between tables remains consistent. More specifically, this means when a table (e.g. Listings) has a foreign key (e.g. Product ID) pointing to a different table (e.g. Products), when updates or deletes occur to the pointed-to table, these changes are cascaded to the linking table. In our example, if a product is renamed, the linking table’s foreign keys will also update; if a product is deleted from the ‘Products’ table, any listings which point to the deleted entry will also be deleted. Furthermore, any new listing must have that foreign key pointing to a valid, existing entry.
InnoDB is a relational DBMS (RDBMS) and thus has referential integrity, while MyISAM does not.
Transactions & Atomicity
Data in a table is managed using Data Manipulation Language (DML) statements, such as SELECT, INSERT, UPDATE and DELETE. A transaction group two or more DML statements together into a single unit of work, so either the entire unit is applied, or none of it is.
MyISAM do not support transactions whereas InnoDB does.
If an operation is interrupted while using a MyISAM table, the operation is aborted immediately, and the rows (or even data within each row) that are affected remains affected, even if the operation did not go to completion.
If an operation is interrupted while using an InnoDB table, because it using transactions, which has atomicity, any transaction which did not go to completion will not take effect, since no commit is made.
Table-locking vs Row-locking
When a query runs against a MyISAM table, the entire table in which it is querying will be locked. This means subsequent queries will only be executed after the current one is finished. If you are reading a large table, and/or there are frequent read and write operations, this can mean a huge backlog of queries.
When a query runs against an InnoDB table, only the row(s) which are involved are locked, the rest of the table remains available for CRUD operations. This means queries can run simultaneously on the same table, provided they do not use the same row.
This feature in InnoDB is known as concurrency. As great as concurrency is, there is a major drawback that applies to a select range of tables, in that there is an overhead in switching between kernel threads, and you should set a limit on the kernel threads to prevent the server coming to a halt.
Transactions & Rollbacks
When you run an operation in MyISAM, the changes are set; in InnoDB, those changes can be rolled back. The most common commands used to control transactions are COMMIT, ROLLBACK and SAVEPOINT. 1. COMMIT - you can write multiple DML operations, but the changes will only be saved when a COMMIT is made 2. ROLLBACK - you can discard any operations that have not yet been committed yet 3. SAVEPOINT - sets a point in the list of operations to which a ROLLBACK operation can rollback to
MyISAM offers no data integrity - Hardware failures, unclean shutdowns and canceled operations can cause the data to become corrupt. This would require full repair or rebuilds of the indexes and tables.
InnoDB, on the other hand, uses a transactional log, a double-write buffer and automatic checksumming and validation to prevent corruption. Before InnoDB makes any changes, it records the data before the transactions into a system tablespace file called ibdata1. If there is a crash, InnoDB would autorecover through the reply of those logs.
InnoDB does not support FULLTEXT indexing until MySQL version 5.6.4. As of the writing of this post, many shared hosting providers’ MySQL version is still below 5.6.4, which means FULLTEXT indexing is not supported for InnoDB tables.
However, this is not a valid reason to use MyISAM. It’s best to change to a hosting provider that supports up-to-date versions of MySQL. Not that a MyISAM table that uses FULLTEXT indexing cannot be converted to an InnoDB table.
In conclusion, InnoDB should be your default storage engine of choice. Choose MyISAM or other data types when they serve a specific need.
MYISAM provides table level locking , FULLTEXT searching. MYISAM has the most flexible AUTO_INCREMENTED column handling off all the storage engines. MYISAM does not support transactions.
INNODB is transaction safe storage engine. INNODB has commit , rollback and crash-recovery capabilities. INNODB supports foreign key referential integrity.