Wednesday 26 March 2014

Difference Between MyISAM and innoDB - MySQL

Difference Between MyISAM and innoDB - MySQL

MySQL is Open Source database means its free to use and reliable. that's why lot of application use mysql as a database. But when we going to create  a new database, lot of doubts comes in mind like what will be database maxsize, Database engine and number of tables in database.

Before you create you databases, you must think below important points.
  • Why I am creating database.
  • What are most use of database means going to add/update statement or search the list
  • What type of record will be stored means in english character or chinse/japense character
  • Maximum records in table.
  • What are different types of database engine supported by MySQL.
  • What tables will use for tractions

Following are list of different database engines types

Name Vendor License Transactional active development
Archive Oracle GPL No Yes
Aria Monty Program GPL No Yes
Berkeley DB Oracle No
BlitzDB GPL No Yes
CONNECT Monty Program GPL Yes
CSV Oracle GPL No Yes
Falcon Oracle GPL Yes No
InnoDB Oracle GPL Yes Yes
MyISAM Oracle GPL No No
NDB Oracle Yes Yes
InfiniDB Calpont GPL Yes Yes
TokuDB TokuTek Modified GPL Yes Yes
XtraDB Percona GPL Yes Yes
Federated Oracle GPL No
FederatedX Monty Program GPL Yes
CassandraSE Monty Program GPL No Yes
sequence Monty Program GPL Yes Yes
mroonga Monty Program GPL Yes
SphinxSE Sphinx Technologies GPL No Yes

Following are Difference between MyISam and InnoDB
MyISAM Advantage
1. Faster than InnoDB for search results
2. support Full-text indexing
3. When use "select" statement, gives faster results as compare to Innodb

InnoDB Advantage

1. Support for transactions (ACID: Atomicity, Consistency, Isolation, Durability).
2. Better "Row-level locking"
3. Foreign key constraints
4. Repair better incase of table corruption
5. Support Rollback abilities
6. Automatic crash recovery

MyISAM Disadvantage

1. Cascading delete/update not support
2. NOt support ACID compliance
3. Row limit 4,284,867,296
4. When we use indexing, insert/update/delete become bit slow
5. Not Support Row Locking

InnoDB Disadvantage

1. Not support full text indexing (<5.6 mysql version)
2. Can't compressed
3. Search Slow as compare to MyISAM

Change table engine to MyISAM

Change table engine to innodb
ALTER TABLE `tableName` ENGINE = innodb