Database Indexes

Mysql database has  InnoDB and MyISAM storage engines. Both of them have different features. Differences between are,

• MyISAM tables use table-level locking where as InnoDB implements standard row-level locking , which means InnoDB is better. A lock that prevents any other transaction from accessing a table. So, during select or insert query in MyISAM table, all others transaction on same table will not be executed. So, wastage of resources. But in InnoDB, only that row will be locked.
•  InnoDB have Clustered index and MyISAM has no clustered index. There are two types of Indexes (Clustered and non-clustered index.) A clustered index is a special type of index that reorders the way records in the table are physically stored. Therefore table can have only one clustered index. The leaf nodes of a clustered index contain the data pages. A nonclustered index is a special type of index in which the logical order of the index does not match the physical stored order of the rows on disk. The leaf node of a nonclustered index does not consist of the data pages. Instead, the leaf nodes contain index rows. Example, in book, there is index which tells page number direct to the content.Second way is to write page number of content as well as number of next index, and so on. So, MyISAM is not so efficient in way of time of selection but it saves space and time in insertion in database. As in InnoDB, all indexes are shuffled and re- arranged according to order. It helps in speed up searching. Down-side of InnoDBis, it required more space. 
• MyISAM supports full text search but InnoDB don't.
• InnoDB supports Foreign Key and Transaction Model. MyISAM doesn't support transation and doesn't support foreign key natively (what natively means?I will read and tell you later). More next time.