Mysql Search - InnoDB and transactions vs MyISAM for FULLTEXT search

Question

I'm currently doing research on the best ways to provide an advanced search for my php project.

I have narrowed it down to using FULLTEXT search rather than using LIKE to find matching strings in a table row. However, to do this it appears I need to sacrifice using the InnoDB engine which will make me lose the ACIDity of transactions and table relationships.

Is it really worth using the MYISAM mysql engine or are there better ways of providing search functionality.

Any pointers would be appreciated!

Answer 1

It really depends on the application... Using MyISAM for anything that needs referential integrity is an instant fail. At the same time, it's text search isn't all that efficient.

Basically, there are two ways to go. If you find you don't need true referential integrity, consider a NoSQL datastore. MongoDB is a great document store database.

If, on the other hand, you really need referential integrity, but also need fast, indexed full-text searching, you might do better to use Sphinx or Apache Solr to create an indexed cache for full-text search.

Either way, I consider MyISAM to be a legacy datastore. I wouldn't use it on a new project. YMMV.

Answer 2

MyISAM has several drawbacks - lack of transaction support, table-level locks which makes it very slow in heavy read+write load type. Another inconvenience of MyISAM tables - they are not crash safe so you can lost some data in case of unexpected shutdown or power loss on server. However MyISAM is very fast on some queries.

Regarding the FullText search I would suggest to use InnoDB + external search engine like Lucene or Sphinx so you could benefit from both safe and reliable storage engine and fast Full-text queries.

For quick start with InnoDB and Sphinx you can refer to http://astellar.com/2011/12/replacing-mysql-full-text-search-with-sphinx/