Best way to implement search functionality using innoDb

Question

Hi,

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

Have a look at Sphinx Search

Answer 2

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 3

Xapian do a nice set of php bindings, and like a few others xapian can run as a seperate server listening on a port for search requests, great for sharing content between multiple sites.

"Did you mean" functionality is present in xapian but not in the remote server version (yet)