Main differences between INNODB and MYISAM

Good Morning to all…. 🙂
In MYSQL, the MyISAM and InnoDB are the most commonly used storage engine. Both these storage enginee have advantages and disadvantages depending on the specific application.

The main and important difference between InnoDB and MYISAM is : the MyISAM does not support the advanced transaction process, and InnoDB support advanced transaction process.

MyISAM does not support the advanced transaction process

MyISAM does not support the advanced transaction process InnoDB support the advanced transaction process
It’s very easy to use InnoDB is very difficult to use
MyISAM can do only a table-level lock. InnoDB implements row-level.
FULLTEXT Search is supported. FULLTEXT Search indexes doesn’t support until v5.6.
The main mechanism used is the key cache. It only caches index pages from .MYI files. Implemented transactions, foreign keys and relationship constraints.
ACID( Automicity, Consistency, Isolation and Durability) properties is not supported. ACID Properties is supported.
Supported high level table compression. It has Buffer Pool to cache data and index pages.
To take backup of MYISAM table the mysqldump helps to this process. You can take backup of InnoDB tables using just a snapshot of the filesystem.
MyISAM data and indexes are separate. The index is compressed the memory usage on the corresponding increase a lot. InnoDB have index and data are closely tied but do not use compression because of it InnoDB is huge
MYISAM doesn’t support transaction processing and foreign keys InnoDB support transaction processing and foreign keys
MyISAM can set up join index and other fields together. In the part of index, the InnoDB table have AUTO_INCREMENT field.
MYISAM depends upon file system block cache for storing reads in rows and indexes. InnoDB does caching inside the enginee, merging the row caches along with the index caches.
Indexes are stored in one file and datas are stored in another file. Both data and indexes are stored in a single file
COUNT(*) performance is very fast. COUNT(*) performance is very slow
It requires less system resources It requires more system resources like RAM.
MYISAM is generally rapid due to its simple construction and hence cost less in terms of server sources. The InnoDB is generally slower than MYISAM.

If anyone has doubts on this topic then please do let me know by leaving comments or send me an email.

Leave a Reply

Your email address will not be published. Required fields are marked *