# Indexed Search Engine vs MySql fulltext search **URL:** \ **Category:** Engines\ **Created:** [March 13, 2006, 7:51pm UTC](https://www.ruby-forum.com/t/indexed-search-engine-vs-mysql-fulltext-search/54729 "2006-03-13T19:51:05Z")\ **Posts on this page:** 3\ **Page:** 1
**Author:** ![Henrik\_SSiso-8859-1](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/h/3be4f8/32.png) [@Henrik\_SSiso-8859-1](https://www.ruby-forum.com/u/Henrik_SSiso-8859-1)\ **Post date:** [March 13, 2006, 7:51pm UTC](https://www.ruby-forum.com/t/indexed-search-engine-vs-mysql-fulltext-search/54729/1 "2006-03-13T19:51:05Z")
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 ---
**Author:** ![Henrik\_SSiso-8859-1](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/h/3be4f8/32.png) [@Henrik\_SSiso-8859-1](https://www.ruby-forum.com/u/Henrik_SSiso-8859-1)\ **Post date:** [March 13, 2006, 9:26pm UTC](https://www.ruby-forum.com/t/indexed-search-engine-vs-mysql-fulltext-search/54729/2 "2006-03-13T21:26:37Z")
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 you’d get with fulltext searches) on large data sets is going to slow down your 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 it: “Descendents of class Exception[http://www.ruby-doc.org/core/classes/Exception.html](http://www.ruby-doc.org/core/classes/Exception.html)are used to communicate between raise methods and rescue statements in begin/end blocks. Exception[http://www.ruby-doc.org/core/classes/Exception.html](http://www.ruby-doc.org/core/classes/Exception.html)objects carry information about the exceptionâ??its type (the exception’s class name), an optional descriptive string, and optional traceback information. Programs may subclass Exception[http://www.ruby-doc.org/core/classes/Exception.html](http://www.ruby-doc.org/core/classes/Exception.html)to 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 “classes” (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 record is stored individually and indexed. ## When you create/update a record, you submit the content to be indexed to the engine. The engine tokenizes the content, removes stop words such as “and”, “or”, “of”, etc, and then stems each word - so that something like “running” becomes “run” in the database. Each stemmed word, or term, is stored in a 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 codebase if you are interested in doing some benchmarks. I haven’t published these 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 your 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 way 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”, etc. - A downside to indexed\_search is that you may end up with a very long terms 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 info, drop me a note. Lance ---
**Author:** ![Henrik\_SSiso-8859-1](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/h/3be4f8/32.png) [@Henrik\_SSiso-8859-1](https://www.ruby-forum.com/u/Henrik_SSiso-8859-1)\ **Post date:** [March 15, 2006, 11:55am UTC](https://www.ruby-forum.com/t/indexed-search-engine-vs-mysql-fulltext-search/54729/3 "2006-03-15T11:55:02Z")
“Lance B.” [removed\_email\_address@domain.invalid](mailto:removed_email_address@domain.invalid) 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 search and the Indexed Search Engine. It would be great if you also outline the differences between using ferret and indexed\_search\_engine. ## Thanks. Surendra S. [http://ssinghi.kreeti.com](http://ssinghi.kreeti.com), [http://www.kreeti.com](http://www.kreeti.com) Read my blog at: [http://cuttingtheredtape.blogspot.com/](http://cuttingtheredtape.blogspot.com/) ,---- | “War is Peace! Freedom is Slavery! Ignorance is Strength!” | – Orwell, 1984, 1948 `----