Explain the difference between and MyISAM and InnoDB?
These are most commonly used storage engine in MySQL are MyISAM and InnoDB.
Difference between MyISAM and InnoDB are given below:-
- MyISAM does not support transactions, but InnoDB supports transactions.
- MyISAM supports Table-level Locking, but InnoDB supports Row-level Locking.
- MyISAM supports full-text search, but InnoDB does not.
- MyISAM designed for the need of speed but InnoDB designed for maximum performance.
- MyISAM does not support foreign keys, but InnoDB supports foreign keys.
- We can use commit and rollback with InnoDB but not in MyISAM.
- MyISAM does not support ACID (Atomicity, Consistency, Isolation, and Durability) but InnoDB supports the ACID property.
- In InnoDB table, AUTO_INCREMENT field is a part of the index but not in MyISAM.
- MyISAM stores its tables, data, and indexes in disk space using a separate table name.FRM, table name.MYD and table name.MYI but InnoDB stores its tables and indexes in a tablespace.