Full text search in Ruby on Rails 2 - MySQL

My previous post compared MySQL and ferret full text search
engines. For our project, the ferret was the winner. Nevertheless, I
will try to show the beauty and simplicity of using MySQL indexes.


Create table and indices


First of all it is necessary to create table and the corresponding
index.

CREATE TABLE articles(
  id integer NOT NULL PRIMARY KEY AUTO_INCREMENT,
  title varchar(20),
  body varchar(100),
  fulltext(title, body)
) engine = MyISAM;<BR>

or create index after the table exists.

CREATE fulltext INDEX x_f_articles_body ON articles(body);

Please note the MyISAM engine. You cannot create full text index
on InnoDB tables.


And now, let’s insert some data

INSERT INTO articles(title, body)
  SELECT "Databases and IT", "Todays world ... database... MySQL, Sybase";
 
INSERT INTO articles(title, body) 
  SELECT "Sybase RS manual", "Sybase Replication server is a ...";
 
INSERT INTO articles(title, body) 
  SELECT "Sybase technology", "ASE, RS, IQ, PowerBuilder, all of them are...";
 
INSERT INTO articles(title, body) 
  SELECT "Databases and people", "People are using databases without knowing it...";
 
INSERT INTO articles(title, body) 
  SELECT "People everywhere", "Human population is increasing...";

Query syntax


Querying is simple. It is part of the MySQL dialect. Simple Boolean query searches for articles with “Databases”
keyword looks similar to this:

SELECT * 
FROM articles 
WHERE match(title,body) against ("Databases");






















id

title

body

4

Databases and people

People are using databases without knowing it…

1

Databases and IT

Today’s world … database… MySQL, Sybase

or you can create query looking for all database related articles

SELECT * 
FROM articles 
WHERE match(title,body) against ("Databases" WITH query expansion);



























id

title

body

1

Databases and IT

Todays world … database… MySQL, Sybase

4

Databases and people

People are using databases without knowing it…

5

People everywhere

Human population is increasing…

Note, that the “match” columns must be the same as they were in the
create index statement.


See also the record ID=5. There is nothing about databases, but it is
in the result set anyway. The query expansion means, that the MySQL
engine goes through the index twice. In first run it finds all
records with the searched keyword and builds a set of keywords that
appear together with the search string. In the second run it
searches for the expanded set of keywords. Since the “Databases”
are together with “People” in record ID=4, it was
returned as relevant.

This is useful. Unfortunately the result set is often too big.


Also note, that there are not records 2 and 3 containing the Sybase keyword (remember, record 1 contains both Databases and Sybase keywords). The reason is
simple. MySQL weights the words according to their frequency. If the
word is too often in the articles, the weight becomes 0 and thus not
relevant. You have always to remember it, because it’s a feature, not a
bug!


Search


So, let’s see it in action. The fulltext search in rails is as simple as
any other search.

Articles.find(:all, :conditions => ["match(title,body) against  (?)", "Databases"] )

naturally it is possible to combine it with other expressions:

Articles.find(:all, :conditions => ["match(title,body) against  (?) and id > ?",
"Databases", 2] )

Pagination


Pagination is as simple as it could be. You can use the same
pagination methods as for any other ActiveRecord query.

def list
  @article_pages, @articles = paginate :articles,
    :per_page => 10, 
    :conditions => ["match(title,body) against  (?)", "Databases"]
end

Scoping


Scoping is a feature you cannot do with ferret. How does it work?


First of all, imagine that you have a really “complex”
function looking for new articles (ID > 2).

def new_articles
  Articles.find( :all, :conditions => [ "id > ?", 2 ] )
end

Than your boss come and say… OK, but I would like to have
there a possibility to display only new articles about… for example
“Databases”.

Well, you can change the function, or you can scope it. Since you cannot learn now anything new by rewriting functions, we will try to
scope it.

def bosses_DB_articles
  Articles.with_scope(:find => {:conditions => ["match(title,body) against  (?)", "Databases"]} ) do
    new_articles
  end
end

And that’s the whole trick!


Final word


As I said. Searching with MySQL is really easy, but sometimes
it gives unexpected results.

8 Responses to “Full text search in Ruby on Rails 2 - MySQL”

  1. Chris Says:

    A very interesting read.

    It would be nice to see examples of setting up the database tables using ruby migrations, and the corresponding schema.rb .

  2. Roman Mackovcak Says:

    There is some information on http://kekova.ca/articles/2006/01/10/intro_to_migrations.

  3. Nick Says:

    Thanks for this. I’m pretty new to RoR. Can you explain what the pagination conditions would be if I wanted to perform a “LIKE” query against several columns? I’ve tried with ”(term1, term2) LIKE (?)”, “Databases” but with no joy.

  4. Roman Mackovcak Says:

    2 Nick
    The “like queries” are possible in boolean mode. Example:


    select * from articles where match (title, body) against (‘mobil*’ in boolean mode);

    The columns that will be searched are determined by the index structure. You can search through 2 columns if the index is created so.

    More query features of MySQL: http://dev.mysql.com/doc/refman/5.1/en/fulltext-boolean.html

  5. Tony Says:

    I know this sounds retarded but i’m sorta new. How to I create a new column without going into MYSQL and RoR. That’s getting tired and I seen my friend do it but he quickly does it like I know.

    Tell me the steps please thanks

  6. Roman Mackovcak Says:

    2 Tony

    Honestly, I do not know. If you want to create a column in the database, you have to go there. The question is how you get into it. I use only mysql native client. I get used to use just command line to know exactly what I am doing.

    Nevertheless, there are tools that are more user friendly than command line. I guess that your friend uses some kind of the tool. Looking on http://www.mysql.com I found http://mysql.com/products/tools/administrator/… but I have never used it.

  7. Zak Mandhro Says:

    There are some issues with using Rails migration and unit testing with MySQL full-text search.

    Here’s a solution: http://www.mandhro.com/2006/11/20/using-mysql-full-text-querying-with-rails/

  8. /bin/recykl » Blog Archive » Full text search in Ruby on Rails 3 - ferret Says:

    […] To show the syntax and code, I will use the same data objects as in the Full text search in ruby on rails 2 – MySQL […]