# Search plugin/gem recommendation for search with joins **URL:** \ **Category:** Rails\ **Created:** [June 29, 2010, 5:44pm UTC](https://www.ruby-forum.com/t/search-plugin-gem-recommendation-for-search-with-joins/190873 "2010-06-29T17:44:01Z")\ **Posts on this page:** 7\ **Page:** 1
**Author:** ![cmaxvv](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/c/85f322/32.png) [@cmaxvv](https://www.ruby-forum.com/u/cmaxvv)\ **Post date:** [June 29, 2010, 5:44pm UTC](https://www.ruby-forum.com/t/search-plugin-gem-recommendation-for-search-with-joins/190873/1 "2010-06-29T17:44:01Z")
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 ---
**Author:** ![cmaxvv](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/c/85f322/32.png) [@cmaxvv](https://www.ruby-forum.com/u/cmaxvv)\ **Post date:** [June 29, 2010, 6:02pm UTC](https://www.ruby-forum.com/t/search-plugin-gem-recommendation-for-search-with-joins/190873/2 "2010-06-29T18:02:53Z")
Max W. 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. > 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 ---
**Author:** ![cmaxvv](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/c/85f322/32.png) [@cmaxvv](https://www.ruby-forum.com/u/cmaxvv)\ **Post date:** [June 30, 2010, 10:54am UTC](https://www.ruby-forum.com/t/search-plugin-gem-recommendation-for-search-with-joins/190873/3 "2010-06-30T10:54:10Z")
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? ---
**Author:** ![cmaxvv](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/c/85f322/32.png) [@cmaxvv](https://www.ruby-forum.com/u/cmaxvv)\ **Post date:** [June 30, 2010, 2:06pm UTC](https://www.ruby-forum.com/t/search-plugin-gem-recommendation-for-search-with-joins/190873/4 "2010-06-30T14:06:17Z")
Max W. wrote: > Thanks Marnen but i don’t want to switch from mysql to postgres right > now. Then you’ll have to put your full-text-indexable field in a MyISAM table. Yuck. You can do it, but switching to Postgres will give you fewer problems. (Postgres has numerous other advantages too.) > Can anyone actually recommend a search plugin rather than telling > me i don’t need one? Why? IMHO you’re just making your life harder by asking for what appears to be an unnecessary tool. If you think I’m wrong, please explain why you believe you need one in light of what’s already been suggested in this thread. Best, –Â Marnen Laibow-Koser [http://www.marnen.org](http://www.marnen.org) removed\_email\_address@domain.invalid Sent from my iPhone ---
**Author:** ![cmaxvv](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/c/85f322/32.png) [@cmaxvv](https://www.ruby-forum.com/u/cmaxvv)\ **Post date:** [June 30, 2010, 3:53pm UTC](https://www.ruby-forum.com/t/search-plugin-gem-recommendation-for-search-with-joins/190873/5 "2010-06-30T15:53:02Z")
Well, fast text searching without switching to MyIsam/Postgres for a start. But also the ability to automatically deal with pluralisation and (probably at some point) fuzzy matching. Definitely the pluralisation at least, anyway. ---
**Author:** ![cmaxvv](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/c/85f322/32.png) [@cmaxvv](https://www.ruby-forum.com/u/cmaxvv)\ **Post date:** [June 30, 2010, 4:03pm UTC](https://www.ruby-forum.com/t/search-plugin-gem-recommendation-for-search-with-joins/190873/6 "2010-06-30T16:03:30Z")
[Please quote when replying, so that the discussion is easier to follow.] Max W. 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 > and (probably at some point) fuzzy matching. Definitely the > pluralisation at least, anyway. ---
**Author:** ![cmaxvv](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/c/85f322/32.png) [@cmaxvv](https://www.ruby-forum.com/u/cmaxvv)\ **Post date:** [June 30, 2010, 7:34pm UTC](https://www.ruby-forum.com/t/search-plugin-gem-recommendation-for-search-with-joins/190873/7 "2010-06-30T19:34:57Z")
On 30 Jun 2010, at 16:03, Marnen Laibow-Koser wrote: > indexing you need, change your DB – proper indexing is vital. > … 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