MySQL Full-text search in Ruby on Rails

Question

I am trying to implement a basic full-text search with MySQL.

I wrote this migration:

def self.up
  execute 'ALTER TABLE photos ENGINE = MyISAM'
  execute 'CREATE FULLTEXT INDEX fulltext_photos ON photos (place, info)'
end

def self.down
  execute 'ALTER TABLE photos ENGINE = InnoDB'
  execute 'DROP INDEX fulltext_photos ON photos'
end

And here's my model:

def self.search(*args)
  options = args.extract_options!
  find_by_sql [ "SELECT * FROM photos WHERE MATCH (place, info) AGAINST (?)", options[:query] ]
end

The problem is that this code always returns an empty array.

For example:

% Photo.find(:first)
=> Photo id: 1, place: "Baceno", info: "Era immerso in erba alta." ... 

% Photo.search(:all, :query => 'baceno')
=> []

Answer 1

I created a project (Rails 2.3.2, Ruby 1.9.1 MySQL 5.0) to emulate this. With one record in the database, I got the same results you did. When I added more records, the Photo.search command found the record.

This could be because "words that are present in 50% or more of the rows are considered common and do not match". Ref.

The 50% threshold does not apply in binary mode. Ref.

IN BINARY MODE belongs inside the parentheses: AGAINST ('baceno' IN BOOLEAN MODE)

Answer 2

In my test (just in MySQL, not in Rails) when I add the option IN BOOLEAN MODE to the SELECT statement, it seems to return rows.

SELECT * FROM photos WHERE MATCH (place, info) AGAINST (?) IN BOOLEAN MODE

I would also recommend using a seperate search product such as Solr or Sphinx for searching.

Answer 3

Check out the Thinking Sphinx gem. It's great.