Tabular comparison of MYISAM INNODB

FACTOR	INNODB	MYISAM
Locking	Row Level	Table Level
Transaction	Allowed	Not Allowed
crash recovery	Reliable	Less Reliable
Referential integrity (FOREIGN KEYs)	Supported	Not Supported
Insert / Updates Speed	Faster because of row level locking	slower because of table level locking
Concurrent Insertion	If update/inserted rows are different , then the operation can be perormed simultanosly	No
Concurrent Updation	If update/inserted rows are different , then the operation can be perormed simultanosly	No
Concurent insert and updates	If update/inserted rows are different , then the operation can be perormed simultanosly	If a MyISAM table has no holes in the data file (deleted rows in the middle), an INSERT statement can be executed to add rows to the end of the table at the same time that SELECT statements are reading rows from the table. If there are multiple INSERT statements, they are queued and performed in sequence, concurrently with the SELECT statements.
Large number of selects as compared to inserts/delete	comparatively slow	fast
storage limit	1/4x than myisam	4x than innodb
full-text search	No	No
system resources	High	Low
Count(*)	speed is slow	Fast
Backing Up	Difficult	Easy,  just need to copy FRM , MYD & MYI files.

Mysql Optimization - Find Remove Duplicate Indexes

Duplicate index means when we have multiple index in the same column of a table.

Example : duplicate on same column
CREATE TABLE DUPLICATE_INDEX (
  EMAIL varchar(100) NOT NULL,
  PRIMARY KEY (EMAIL),
  KEY EMAIL(EMAIL)
) ENGINE=MyISAM

In this example on column 'EMAIL' we have primary key and index as well and one of them is unnecessary. So ideally we should remove one of index from this table.

Example : duplicate because of composite index
CREATE TABLE DUPLICATE_INDEX (
  EMAIL varchar(100) NOT NULL,
  PHONE varchar(100) NOT NULL,
  UNIQUE KEY (EMAIL,PHONE),
  KEY EMAIL(EMAIL)
) ENGINE=MyISAM

In this example index on column is multiple because of index key EMAIL and UNIQUE key (EMAIL,PHONE) .so key on index EMAIL is irrelevant.

Why we need to remove these duplicate keys
1. They make the optimizer phase slower because MySQL needs to examine more query plans.
2. For every insert / delete / update  , the storage engine needs to maintain, calculate and update more index
3. Disk space will increase and hence the backup time.

How To Find Duplicate Keys
There is a tool in Percona Toolkit that can help you to find all those keys in your schema, its name is pt-duplicate-key-checkerand it can find the  different types of duplicates key


command: pt-duplicate-key-checker --database=test