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.
The basic difference between MyISAM and InnoDB is, MyISAM does not support the advanced transaction process, and InnoDB support.MyISAM storage engine emphasizes that performance and it implementation is several times faster than the InnoDB but does not support transaction whereas InnoDB provides transaction support with external keys and other advanced database features.
The following are the details and differences MyISAM and InnoDB storage engines:
| InnoDB | MyISAM |
| It is not support FULLTEXT index | MyISAM supports FULLTEXT indexes and compressed read-only storage. |
| InnoDB is not save data as the table level so the implementation of select count (*) from table in InnoDB will again scan the entire table to calculate the number of rows. | MyISAM save data as table level so it simply read out the saved row number. |
| InnoDB table have AUTO_INCREMENT field as the part of index. | MyISAM can set up join index and other fields together. |
| You can not re-establish the deleted table using InnoDB. | |
| LOAD TABLE FROM MASTER operations does not work on InnoDB so the solution is first import data into a MyISAM table and then import the data into InnoDB table but for the extra use InnoDB characteristics such as foreign keys will not apply. | |
| InnoDB support row level locking so operations will not lock the whole table but InnoDB tables are not absolute for row level locking/ | MyISAM support table level locking so it will lock whole table when perform the the implementation of a SQL statement. |
| InnoDB support transaction processing and foreign keys | MyISAM does not |
| InnoDB have index and data are closely tied but do not use compression because of it InnoDB is huge. | MyISAM indexes and data are separate and the index is compressed also the memory usage on the corresponding increase a lot. |
MyISAM has often been suitable only for small projects as it is easy to use.If you are using MyISAM, then, merge engine can greatly accelerate the development of the application as long as they merge the table select count (*) operation which is very suitable for the large application.
Comments (1)