MySQL storage engine dilemma

Question

There are two MySQL database features that I want to use in my application. The first is FULL-TEXT-SEARCH and TRANSACTIONS.

Now, the dilemma here is that I cannot get this feature in one storage engine. It's either I use MyIsam (which has the FULL-TEXT-SEARCH feature) or I use InnoDB (which supports the TRANSACTION feature). I can't have both.

My question is, is there anyway I can have both features in my application before I am forced to make a choice between the two storage engines.

Answer 1

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.

Answer 2

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!

Answer 3

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.

Answer 4

there is no way to have both in the same database engine, these are the constraints given by the design how MySQL works. You can not change physics. ;-)

http://dev.mysql.com/doc/refman/5.1/en/innodb-restrictions.html
http://dev.mysql.com/doc/refman/5.1/en/ansi-diff-transactions.html

when you're still in design phase, you may also consider using another SQL implmentation for your project which supports both in the same engine. Postgres for example does.

http://wiki.postgresql.org/wiki/Why_PostgreSQL_Instead_of_MySQL_2009#Transactions_and_the_Database_Engine_Core

Answer 5

One another option is to use MySQL's support for replication:

For example you can setup master server with InnoDB storage engine. Then replicate to another read-only server with the MyISAM storage engine.

You can use almost any MySQL storage engine and there might be some that support better search features than MyISAM for some use cases.