MyISAM and InnoDB are the most commonly used storage engine in MySQL whereas both storage engine types have advantages and disadvantages depending on the specific application.
However, MyISAM is the default storage engine chosen by MySQL database, when creating a new table. The major differences between MyISAM and InnoDB storage engines are :
1. Referential Integrity
Referential integrity ensures that relationships between tables remain consistent.
Or more specifically, this means when a table has a foreign key pointing to a different table. When an update or delete is made to the pointed-to-table then the changes will cascade to the linking table.
Suppose that we have two tables – Books and category. Books have a foreign key(say category_id) pointing to the Category table. In our example, if a Category is renamed, the linking table?s foreign keys will also update. if a category is deleted from the Category table, any books which point to the deleted entry will also be deleted. Furthermore, any new book 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. So InnoDB supports foreign keys and referential integrity, including cascaded deletes and updates.
2. 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 does not support transactions whereas InnoDB does.
So if a table is using MyISAM engine and operation is interrupted, 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.
So if a table is using InnoDB engine and operation is interrupted, because it using transactions, which has atomicity, any transaction which did not go to completion will not take effect, since no commit is made.
When you run an operation in MyISAM, the changes are set and you cannot roll back the changes while in InnoDB, those changes can be rolled back.
InnoDB provides auto-recovery after a crash of the MySQL server or the host on which the server runs.
3. 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 the other operations. This means queries can run simultaneously on the same table, provided they do not use the same row.
4. Reliability
MyISAM offers no data integrity – Hardware failures, unclean shutdowns and cancelled 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 auto-recover through the replay of those logs.
5. FULLTEXT Indexing
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.
6. Caching
InnoDB requires a lot of memory (buffer pool). The data and indexes are cached in memory. Changes are written to the log buffer (physical memory) and are flushed every second to the log files (method depends on innodb_flush_log_at_trx_commit value). Having the data in memory is a huge performance boost. MyISAM only caches indexes (key_buffer_size) so that’s where you would allocate most of your memory if you’re only using MyISAM.
Conclusion
In conclusion, InnoDB should be your default storage engine of choice. Choose MyISAM or other data types when they serve a specific need.
About Author
