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

mysql database innodb myisam
Mukilarasan picture Mukilarasan · Oct 19, 2012 · Viewed 44.6k times · Source

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?

Answer

eggyal picture eggyal · Oct 19, 2012

  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.

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

Related questions

MyISAM versus InnoDB

I'm working on a projects which involves a lot of database writes, I'd say (70% inserts and 30% reads). This ratio would also include updates which I consider to be one read and one write. The reads can be dirty (e.g. …

Read More
What's the difference between MyISAM and InnoDB?

I understand that this question has been asked before, but most of the time it is asked in relation to a specific database or table. I cannot find an answer on this site that describes the two engines and their …

Read More
Changing Table Engine in MySQL

I am using mysql and mysql workbench. I created 5 tables with innodb engine. I checked their engine and it was innodb before I insert data into them. I inserted data from 5 MyISAM tables and now my innodb tables are MyISAM. …

Read More