Any third party search engines (fulltext search and so on) work fine with InnoDB tables?

I know, that InnoDB tables do not support fulltext searches, yet. So I thought of using a third party search engine like solr, xapian or whoosh. Do t开发者_JAVA技巧hose third party tools work equivalently fine with InnoDB tables as they work with MyIsam tables? I need to find e.g. spelling suggestions, and similar strings...

---

You could use Solr/Lucene to do the fulltext-search over your DB data. Since my MySQL DB is to big for an fast fulltext-search, i decided to combine mysql and Solr/lucene.

It's important to know, that Solr/Lucene is not an MySQL Plugin. So you will not be able to search the fulltext-index by using typical MySQL SQL-Statements. An fulltext-search, initiated by the application, should be first send the request to the 3rd party fulltext-index (Solr), which returns the primary keys of the related documents. Second step is to run an SQL statement against your MySQL innoDB with an where clause with the corresponding primary keys from the Solr result set. That solution works in my case very well and much, much faster (and better) than an typical MySQL Myisam fulltext-search.

As an alternative you could not only index the data in solr. You also could store the data in solr additionally. In that case, solr is able to return the full text. So you don't need get the data form the database, as in the example above.

Do those third party tools work equivalently fine with InnoDB tables as they work with MyIsam tables?

Absolutely. Solr has an DataImportHandler. Ther you define an SQL statement in order to get the data you like to index in solr, like: select * from MyTable; But keep in mind: right now (as far as I know) ther is no MySQL solr plugin available. The cooperation of Solr and MySQL should be handled by the application.

---

Third-party fulltext search engines typically copy data returned by a MySQL query, and use it to populate their search index. There's no difference between MyISAM and InnoDB data sources in this respect.

I gave a presentation Practical Full-Text Search in MySQL a few years ago. You might find it interesting.

---

Sphinx supports its own index and just takes data from MySQL on a timely basis by issuing a query.

It is not even aware of the underlying table structure and as long as the query runs and returns the results, it's OK for Sphinx.

Other third party engines work in a similar way.