MySQL storage engine dilemma

Possible workarounds:

1. Use Sphinx or Solr or some other external text search engine for your text searches and use InnoDB engine.

2. Write your own search code - and use InnoDB. This is not really an option, unless you search needs are limited or your budget is huge.

3. Use both engines, MyISAM and InnoDB. Keep the columns you want to be full-text searching in MyISAM and the rest in InnoDB. This will be risky as the data in MyISAM will not be transaction safe.

4. Use both engines, MyISAM and InnoDB. Keep all data in InnoDB and duplicate the columns you want to be full-text searching in MyISAM. This will need some mechanism (triggers) for the data duplication.

5. Wait for the version of MySQL where full-text search will be supported by InnoDB or other transactional engine.

6. (Option 4) but use MariaDB (a MySQL fork) which has "crash-safe" (but still not transaction-safe) full text indexes: When-will-transactional-fulltext-indexes-be-ready?

7. Use other RDBMS like PostgreSQL that has full-text support in transactional engine.

If you need to perform transactions and full-text against a single table in MySQL, you have a few options. But really the salient point to take away from this entire discussion is that databases are not good at doing full-text search to begin with (especially MySQL!) and ideally you want to offload this work to a component that is better at performing this type of task.

Option 1:

Create one table that you need to do the transaction against as InnoDB and then create another "mirror" table that is MyISAM that you can do full-text search against. You can keep the data in sync through the use of a trigger on the InnoDB table. Sort of a hack but it will work.

Option 3:

Take a look at a 3rd party full-text engine like Sphinx, Lucene or Solr. This way you can focus on designing your database to be optimal at querying data and not forcing it to do text search as well.

Option 3:

You can choose to go with a different database server that supports transactions and full-text search at the same time, such as SQL Server.

Hope this give you some clarity.

Enjoy!

The MyISAM full-text index probably isn't as good as you think. It works ok(ish) on small data, but is lousy on bigger data.

In MySQL 5.6 we may have full-text on InnoDB, however it still does not support most of the features that a real full-text search engine would have.