-
Kindly tell me, In this situation what engine I have to use for improve performance?
The performance of each storage engine will depend on the queries you perform. However, be aware that different tables within the same database can use different storage engines.
-
Why MyISAM engine does not support foreign key relationship and InnoDB does?
As documented under Foreign Key Differences:
At a later stage, foreign key constraints will be implemented for
MyISAMtables as well.Therefore, foreign key constraints have simply not yet been implemented in
MyISAM.
EDIT: As that comment is removed from docs, it appears that it is no longer planned to implement foreign key constraints in MyISAM engine.
-
Why MyISAM engine does support full text search and InnoDB does not?
As documented under What Is New in MySQL 5.6:
You can create
FULLTEXTindexes onInnoDBtables, and query them using theMATCH() ... AGAINSTsyntax.Therefore, full text search has been implemented in
InnoDBas of MySQL 5.6.