Difference between InnoDB and MyISAM – MySQL
MySQL is Open Source database means it's free to use and reliable. that's why a lot of application use MySQL as a database. But when we going to create a new database, a lot of doubts comes in mind like what will be database max size, Database Storage Engine Type and the Number of tables in the database etc.
Following are the main difference between InnoDB and MyISAM:
|1.||InnoDB implements row-level lock||MyISAM implements table-level lock|
|2.||You will find better/automatic crash recovery in InnoDB
Reliability => Yes
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 replay of those logs.
Reliability => No
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
|3.||InnoDB doesn't have
||MyIsAM Support FullText search|
|4||InnoDB support 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.
The most common commands used to control transactions are COMMIT, ROLLBACK, and SAVEPOINT.
|MyISAM doesn't support 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.|
|5||InnoDB support Referential Integrity||MyISAM doesn't support|
|6||You can not repair a table||You can repair a table|
You may also like - MYSQL Interview Questions Answers
Includes MySQL 5.6 changes
INNODB STORAGE ENGINE:
- It provides full ACID (atomicity, consistency, isolation, durability) compliance. Multi-versioning is used to isolate transactions from one another.
- InnoDB provides auto-recovery after a crash of the MySQL server or the host on which the server runs.
- InnoDB supports foreign keys and referential integrity, including cascaded deletes and updates.
- MySQL 5.6 builds on the platform of InnoDB fully integrated as the default storage engine
- Persistent Optimizer Stats: Provides improved accuracy of InnoDB index statistics, and consistency across MySQL restarts.
- Pruning the InnoDB table cache: To ease the memory load on systems with huge numbers of tables, InnoDB now frees up the memory associated with an opened table. An LRU algorithm selects tables that have gone the longest without being accessed.
- Supports Full-text search: A special kind of index, the FULLTEXT index, helps InnoDB deal with queries and DML operations involving text-based columns and the words they contain. These indexes are physically represented as entire InnoDB tables.
- InnoDB seems to be way faster on Full-Text Search than MyISAM
- So, there is no point in using
MyISAMEngine if you are already upgraded to 5.6, if not then don't wait for upgrading to MySQL 5.6.
- InnoDB should be your default storage engine of choice. Choose MyISAM or other data types when they serve a specific need.
You may also like - Laravel Interview Questions Answers