Menu
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?
763
A foreign key is a column in one table that links the data to another table. It prevents users from adding records that destroy the link between two tables. MyISAM doesn't support the Foreign key option. InnoDB supports the Foreign Key option.
Having active foreign keys on tables improves data quality but hurts performance of insert, update and delete operations. Before those tasks database needs to check if it doesn't violate data integrity. This is a reason why some architects and DBAs give up on foreign keys at all.
In terms of data queries (SELECT), InnoDB is the clear winner, but when it comes to database writes (INSERT and UPDATE), MyISAM is somewhat faster. However, the lower speed of InnoDB is more than compensated for by its transaction protocol.
InnoDB permits a foreign key to reference any index column or group of columns. However, in the referenced table, there must be an index where the referenced columns are the first columns in the same order. Hidden columns that InnoDB adds to an index are also considered (see Section 14.6.
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 InnoDB as of MySQL 5.6.
101
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.
25
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
