MyISAM vs InnoDB – Difference Between MyISAM and InnoDB Storage Engines

MyISAM vs InnoDB. What’s the difference between MyISAM and InnoDB storage engine. No doubt, MySql is one of the most used databases worldwide. The great thing about MySql is the flexibility to choose storage engines.

In MySql, every storage engine is designed for a specific purpose to address/solve application problems. The two popular storage engines of MySql (MyISAM and InnoDB) are widely used worldwide. So let’s check what’s the difference between MyISAM and InnoDB storage engines.

How to Install Linux,Apache,Mysql (LAMP).

List of MySql storage engines 

2. InnoDB
4. Memory
5. BDB

myisam and innodb storage engines

To check storage engines supported by MySql , open phpMyAdmin and click on show engines option.  Another alternative is to use show engines command.


How to install, uninstall & remove phpMyAdmin on Ubuntu

MyISAM vs InnoDB

MyISAM Storage Engine. 

Let’s first look some of the great features of MyISAM storage engine.

1. MyISAM uses Table Level Locking to optimize multiple simultaneous reads and writes.

2. In this storage engine, MyISAMChk utility is used to repair database files and MyISAMPack utility for wasted space.

3. It is designed for speed. Before MySQL Version 5.5.5 it is the default storage engine used in MySql.

4. MyISAM storage engine supports full-text searching.

Subscribe Our Tutorials

Get Latest Updates on Facebook

5. If your application has more read operation then go ahead with MyISAM. As it is designed for super fast read operations.

6. MyISAM does not support the transaction. You cannot commit and rollback changes with MyISAM. Once a command is issued then it’s done.

NOTE: Avoid concurrent writes, as it locks down the entire table.

How to Connect PHP Application with MySqli

MySql Interview Questions.

InnoDB Storage Engine

This the default storage engine as of MySQL 5.5.5.

1. InnoDB supports Full transaction-safe tables(ACID Compliant). So what does it mean, It means you can recover your data.

a) if your application get crashes or you get hardware problem.
b) If the update fails then all your changes are reverted.

2. It can provide better concurrency for tables. Means you can run many updates concurrently.

3. InnoDB supports Row-level Locking as compared to table level locking on MyISAM.

4. This storage engine is slower than MyISAM.

5. Support the concept of foreign keys .

6. Follow ACID property.

How to Connect PHP Application with PDO

7. Use clustered indexes to reduce I/O for common queries based on primary keys

You can read further about MySql Storage Engines

When to use MyISAM storage engine

MyISAM storage engine is ideal when you have high reads and very few write operations.

When to use InnoDB storage engine

If you need to enforce foreign key constraints and transactions then definitely InnoDB storage engine is the best choice.

MySql Reference Books for High-Performance Application

MySql Books on Flipkart

About WebRewrite

I am technology lover who loves to keep updated with latest technology. My interest field is Web Development.
Tagged , , . Bookmark the permalink.