MySQL – Why MyISAM Supports FULLTEXT Searching and InnoDB Does Not

full-text-searchinnodbmyisamMySQL

I know the basic differences between the MyISAM and InnoDB engines. In MySQL 5.6 beta, InnoDB will support fulltext search. But, I wonder why previous versions of InnobDB didn't support FTS? My guess is because table storage format or the way that data is indexed. Sorry for my ignorance.

Best Answer

Back in the day, people have been clamoring for FTS for InnoDB and feverishly looking for workarounds. Sometime around January 2005, InnoDB found a sponsor to look for a developer.

I am sure one of the biggest gotchas that developers worried about was making FULLTEXT Searching (FTS) work in harmony with Transactions. This is especially needed since the tokenization for inserted strings is performed only at commit time, so a full-text search does not see the uncommitted data.

Please keep in mind that although there are tutorials on FTS with InnoDB, MySQL 5.6 still has a bug or two out there for InnoDB/FTS.