Mysql full text search is empty
I am using ruby on rails with a MySQL backend. I have a table called notes, and here is the migration I am using 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 search on title and body fields. The problem is that full text searches always return empty. For example, if I add this line to the database: Title: test, Body: test
. Then I run this query SELECT * FROM notes WHERE MATCH(title, body) AGAINST('test')
. It returns a null set. Can anyone tell me what I am doing wrong and how to get the full text to work?
source to share
I'm just guessing, but the documentation states:
A natural language search is interpreted as a search string as a phrase in natural human language (phrase in plain text). Special operators. The list of notes is applied. In addition, words that are present in 50% or more lines are common and do not match. Full text search - natural language search if the IN IN NATURAL LANGUAGE MODE modifier, or if the modifier is not given.
Therefore, if there is only one line and it contains "test" then "test" is present on more than 50% of the lines and therefore is not considered a match. Maybe try:
SELECT * FROM notes WHERE MATCH(title, body) AGAINST('test' IN BOOLEAN MODE)
source to share
While this probably doesn't answer your specific question, I would just go and use Ferret or Solr as a search engine, especially if a lot of text is involved. Both are very easy to set up and powerful to download. Ultrasphinx is another option.
With RoR, I'm usually not scared of trying to get things to work in a certain way. It's all about being Agile;) you see.
source to share