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
1. MyISAM
2. InnoDB
3. MERGE
4. Memory
5. BDB
To check storage engines supported by MySql , open phpMyAdmin and click on show engines option. Another alternative is to use show engines command.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 |
mysql> show engines; +--------------------+---------+----------------------------------------------------------------+--------------+------+------------+ | Engine | Support | Comment | Transactions | XA | Savepoints | +--------------------+---------+----------------------------------------------------------------+--------------+------+------------+ | FEDERATED | NO | Federated MySQL storage engine | NULL | NULL | NULL | | MRG_MYISAM | YES | Collection of identical MyISAM tables | NO | NO | NO | | MyISAM | YES | MyISAM storage engine | NO | NO | NO | | BLACKHOLE | YES | /dev/null storage engine (anything you write to it disappears) | NO | NO | NO | | CSV | YES | CSV storage engine | NO | NO | NO | | MEMORY | YES | Hash based, stored in memory, useful for temporary tables | NO | NO | NO | | ARCHIVE | YES | Archive storage engine | NO | NO | NO | | InnoDB | DEFAULT | Supports transactions, row-level locking, and foreign keys | YES | YES | YES | | PERFORMANCE_SCHEMA | YES | Performance Schema | NO | NO | NO | +--------------------+---------+----------------------------------------------------------------+--------------+------+------------+ 9 rows in set (0.00 sec) |
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.
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
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.
