Why doesn't MySQL's MyISAM engine support Foreign keys?

Question

I am writing a web-app for my studies which includes fulltext search and foreign keys.

I have read somewhere, MyISAM engine is suitable for fulltext searching, and InnoDB for foreign keys.

In this situation what engine should I use for the best performance?

• Why does MyISAM not support foreign key relationship but InnoDB does?
• Why does MyISAM support full text search but InnoDB does not?

Solution 1

1. 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.

2. 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 MyISAM tables 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.

3. 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 FULLTEXT indexes on InnoDB tables, and query them using the MATCH() ... AGAINST syntax.

   Therefore, full text search has been implemented in InnoDB as of MySQL 5.6.

Solution 2

I do remember the times when mysql had only myisam and innodedb was in development. MyIsam has no foreign keys because it is old system that does not support relations in database. It will never use foreign keys! To Use it you have innodb. If you don't need all stuff, like relations in DB, use MyISAM to get better performance.

Comments

[shabbychef]

I am not seeing the quote "At a later stage, foreign key constraints will be implemented for MyISAM tables as well." in the MySQL docs. Perhaps that feature has been un-roadmapped?

[eggyal]

@shabbychef: You're right—it appears that comment was removed some time between Sep 10, 2013 and Oct 11, 2013; I can't see any reference elsewhere in the current documentation to indicate that FK constraints are still intended for MyISAM in the future, nor can I see any specific documentation/discussion/record for this change. So yes, I would guess it is no longer planned.