Best way to implement search functionality using innoDb

Question

I need to implement something like a full text search on a couple if index's in a large products table, using innodb, MyISAM is not an option due to its lack of transactions and relationship support.

I can think of a couple of ways of doing this, plugin, stored procedure, search table with keys and copied index's in MyIsam format.

How have you acheived this in the past, which is the best way (including any I have not mentioned) and why?

The plugin sounds expensive, the stored procedure sounds slow, and the search table sounds like and admin nightmare.

Love to hear your views.

Answer 1

A possibility might be to use an external (independant of the Database) full-text engine, like, for instance, Lucene (if you are using PHP, you might also want to take a look at Zend_Search_Lucene)

There are plenty of those (some free, some costly, some open source, and some proprietary) ; but they'll generally allow you to go farther than what MySQL full-text allows -- and it won't be integrated inside the database (which might be good : you can index documents, for instance -- or bad : you can't join those results with one of your SQL query results that easily)

The advantage of using such an engine is that full-text search is really their job ; and they're supposed to do it well (and be "intelligent" about what users tend to input).

Drawback is you must have another engine ; which means more work, more configuration, and more development, as you can't just have them "plugged-in" in you DB.

Answer 2

Have a look at Sphinx Search