Search plugin/gem recommendation for search with joins

Hi all. It's time to choose a search system for a new app i've been
working on and though i'd crowdsource some wisdom. I've used
ferret/acts_as_ferret before and encountered some problems which make me
reluctant to use it again. I know people are using sphinx/solr these
days (and other things i'm sure).

My particular requirements are to do with complex joins: when i do my
search i'm joining lots of tables together and passing various
conditions to do with the join tables, using a search term (which at the
moment is just doing a LIKE match on a keywords field), sorting by one
of the joined fields, and of course paginating the results. Obviously,
LIKE searches on a text field isn't very scalable (especially in innodb
mysql which doesn't allow full text indexing) and it's this aspect that
i need to replace with a proper indexed search system.

It was the combination of searching, sorting, joining and paginating
that seemed to cause problems for ferret and i wondered if anyone could
recommend a search system that works well with this sort of usage.

Sorry if that's a bit vague, just looking for some accounts of
experiences really. Grateful for any advice - max

Max Williams wrote:

Hi all. It's time to choose a search system for a new app i've been
working on and though i'd crowdsource some wisdom. I've used
ferret/acts_as_ferret before and encountered some problems which make me
reluctant to use it again. I know people are using sphinx/solr these
days (and other things i'm sure).

My particular requirements are to do with complex joins: when i do my
search i'm joining lots of tables together and passing various
conditions to do with the join tables, using a search term (which at the
moment is just doing a LIKE match on a keywords field), sorting by one
of the joined fields, and of course paginating the results. Obviously,
LIKE searches on a text field isn't very scalable (especially in innodb
mysql which doesn't allow full text indexing) and it's this aspect that
i need to replace with a proper indexed search system.

Then don't use InnoDB. Switch to PostgreSQL, which (among other
advantages) allows full-text indexing and referential integrity on the
same table.

I don't see why you need a search plugin here at all.

Thanks Marnen but i don't want to switch from mysql to postgres right
now. Can anyone actually recommend a search plugin rather than telling
me i don't need one?

[Please quote when replying, so that the discussion is easier to
follow.]

Max Williams wrote:

Well, fast text searching without switching to MyIsam/Postgres for a
start.

Essentially impossible. You need an index for that. The way to get a
full-text index is to switch to MyISAM or, better, Postgres. Building a
full-text index in the application layer is poor practice and less
maintainable.

Moral: let the DB do the indexing. If your DB doesn't support the
indexing you need, change your DB -- proper indexing is vital.

But also the ability to automatically deal with pluralisation

… or use a dedicated indexing server like Solr. Depending on the features you want, having an external indexer can even be beneficial to your application. I haven’t followed this thread all too much, but you could easily add pluralization to the index record when using something like Solr.

Best regards

Peter De Berdt