Indexed Search Engine vs MySql fulltext search

Sorry for my unknowings, but what’s the pros and cons for Search
Engine vs. MySql fulltext search (mfs)?

I’m using mfs now, and wonder if going over to the Search Engine is an
good idea :-).

  • Henrik

Hi Henrik

MySql fulltext search is fine if you have a relatively small data set
and if
the data in each row is relatively small. Indexed search is really
beneficial where you have a lot of data. Doing full table scans (like
get with fulltext searches) on large data sets is going to slow down
searches quite a bit.

In addition, fulltext search requires you to be smart about your search
phrase. For example, if I have a row in my database with this text in

“Descendents of class
Exception used
to communicate between
raise methods and rescue statements in begin/end blocks.
carry information about the exceptionâ??its type (the exception’s
class name), an optional descriptive string, and optional traceback
information. Programs may subclass
Exception add
additional information.”

A fulltext search for “exception traceback” will not return that record,
whereas an indexed search for that phrase will. And a search for
(as opposed to “class”) will also not return that record, but an indexed
search will.

Indexed search does not use table scans. Instead, each word in your
is stored individually and indexed.

When you create/update a record, you submit the content to be indexed to
engine. The engine tokenizes the content, removes stop words such as
“or”, “of”, etc, and then stems each word - so that something like
becomes “run” in the database. Each stemmed word, or term, is stored in
separate table with a pointer to the original record. This allows the
“term” column to be indexed, and so queries on this column are very fast

table scans are not involved.

So, overall, I’d say the benefits of Indexed Search Engine are:

  • Speed on large datasets. There are some performance tests in the
    if you are interested in doing some benchmarks. I haven’t published
    results because it’s all pretty relative. I’m running the tests on my 2
    year old G4 powerbook with less than 1GB RAM. On a different system
    results may very well be different.

  • Flexiblity on searches. You don’t have to get the phrase exactly
    right to
    get a search hit. Search phrases are tokenized and stemmed in the same
    that the content is, and searches occur at the term level.

  • Stored content and search phrases are stemmed so that a search for
    “growing” will return records containing “grows”, “growing”, “grow”,

  • A downside to indexed_search is that you may end up with a very long
    table. And the index for this table is likely to be pretty big. You’re
    basically trading storage for performance.

I hope that helps you understand the differences. If you need more
drop me a note.


“Lance B.” [email protected] writes:

I hope that helps you understand the differences. If you need more info,
drop me a note.

Hello Lance,
Thanks for the detailed post containing the differences between mysql
and the Indexed Search Engine.
It would be great if you also outline the differences between using
ferret and


Surendra S.,
Read my blog at:
| “War is Peace! Freedom is Slavery! Ignorance is Strength!”
| – Orwell, 1984, 1948

This forum is not affiliated to the Ruby language, Ruby on Rails framework, nor any Ruby applications discussed here.

| Privacy Policy | Terms of Service | Remote Ruby Jobs