8

What I want to know what is difference between fulltext searching (searching data in files) and standard database searching (LIKE, IN ect).

What I notice here is that in files you don't have data types, but in database you can define data types for particular data.

I am interested in what kind of search is faster and what are advantages and disadvantages of both.

Thanks.

Dean Kuga
  • 11,878
  • 8
  • 54
  • 108
zajke
  • 1,675
  • 2
  • 12
  • 13

1 Answers1

20

There's a few advantages to full text searching.

Indexing:

Something like:

WHERE Foo LIKE '%Bar';

Cannot take advantage of an index. It has to look at every single row, and see if it matches. A fulltext index, however, can. In fact, fulltext indexes can offer a lot more flexibility in terms of the order of matching words, how close those words are together, etc.

Stemming:

A fulltext search can stem words. If you search for run, you can get results for "ran" or "running". Most fulltext engines have stem dictionaries in a variety of languages.

Weighted Results:

A fulltext index can encompass multiple columns. For example, you can search for "peach pie", and the index can include a title, keywords, and a body. Results that match the title can be weighted higher, as more relevant, and can be sorted to show near the top.

Disadvantages:

A fulltext index can potentially be huge, many times larger than a standard B-TREE index. For this reason, many hosted providers who offer database instances disable this feature, or at least charge extra for it. For example, last I checked, Windows Azure did not support fulltext queries.

Fulltext indexes can also be slower to update. If the data changes a lot, there might be some lag updating indexes compared to standard indexes.

Mike Christensen
  • 88,082
  • 50
  • 208
  • 326
  • Thanks for your answer. This is useful for me. But I always wondering how fulltext search works in background. What I need is some reference to learn about it. I would like to know the patterns by which all that works. – zajke Jul 22 '13 at 20:42
  • That's a huge topic, and it differs between SQL Server, Oracle, Postgres, etc. – Mike Christensen Jul 22 '13 at 20:46
  • Anything would helped me, no matter if it is for SQL Server, Oracle or Postgres. If you know some reference I would appreciate it. Thanks. – zajke Jul 22 '13 at 20:52
  • Maybe read up on GiN and GiST indexes? Here's some [Postgres](http://www.postgresql.org/docs/current/static/textsearch-indexes.html) documentation. – Mike Christensen Jul 22 '13 at 20:56
  • 1
    @MikeChristensen Just to correct, MS FTS can't search for leading wild card search. It can't work for the case your mentioned i.e. '%Bar'. However it can for 'Bar%' – Ashwini Mohan Feb 08 '19 at 08:06