mysql full text search is empty

Question

I am using ruby on rails with a MySQL backend. I have a table called notes and here is the migration I use to create it:

def self.up
  create_table(:notes, :options => 'ENGINE=MyISAM') do |t|
    t.string :title
    t.text :body

    t.timestamps
  end

  execute "alter table notes ADD FULLTEXT(title, body)"

end

I want to do full text searches on the title and body fields. The problem is that the full text searches always come back empty. For example if I add this row into the database: Title: test, Body: test. Then I run this query SELECT * FROM notes WHERE MATCH(title, body) AGAINST('test'). It returns a nil set. Can anybody tell me what I am doing wrong and how to get full text search working?

Answer 1

I'm just guessing here, but the documentation states:

A natural language search interprets the search string as a phrase in natural human language (a phrase in free text). There are no special operators. The stopword list applies. In addition, words that are present in 50% or more of the rows are considered common and do not match. Full-text searches are natural language searches if the IN NATURAL LANGUAGE MODE modifier is given or if no modifier is given.

Therefore, if there is only one row, and it contains 'test', then 'test' is present in more than 50% of the rows and is therefore not considered a match. Perhaps try:

SELECT * FROM notes WHERE MATCH(title, body) AGAINST('test' IN BOOLEAN MODE)

Answer 2

While this probably does not answer your specific question, I would just go ahead and use Ferret or Solr as the search engine, especially if lots of text is involved. Both are very easy to setup and powerful to boot. Ultrasphinx is also another option.

With RoR, I usually don't get bogged trying to get something to work in one particular way. It's all about being Agile ;) you see.