CODEWITHSUNDEEP
sqlsql-serverfull-text-search
Costa
Costa

Reputation: 4095

Why I would bother using full text search?

I am new to Full Text Search, I used the following query

Select * From Students Where FullName LIKE '%abc%'

Students table contains million records all random and look like this 'QZAQHIEK VABCNLRM KFFZJYUU'

It took only 2 seconds and resulted 1100 rows. If million record is searched in two seconds why I would bother using Full Text Search ?!! Did Like predicate used the Full Text Index as well?

Upvotes: 6

Views: 1091

Answers (4)

Paul Sasik
Paul Sasik

Reputation: 81537

No. LIKE does not make use of full text indexing. See here.

Computers are pretty darn fast these days but if you're seeing search results faster than you expect it's possible that you simply got back a cached result set because you executed the same query previously. To be sure you're not getting cached results you could use DBCC DROPCLEANBUFFERS. Take a look at this post for some SQL Server cache clearing options.

Excerpt from the linked page:

Comparing LIKE to Full-Text Search

In contrast to full-text search, the LIKE Transact-SQL predicate works on character patterns only. Also, you cannot use the LIKE predicate to query formatted binary data. Furthermore, a LIKE query against a large amount of unstructured text data is much slower than an equivalent full-text query against the same data. A LIKE query against millions of rows of text data can take minutes to return; whereas a full-text query can take only seconds or less against the same data, depending on the number of rows that are returned.

Upvotes: 4

David Gorsline
David Gorsline

Reputation: 5018

I think you have answered your own question, at least to your own satisfaction. If your prototyping produces results in an acceptable amount of time, and you are certain that caching does not explain the quick response (per Paul Sasik), by all means skip the overhead of full-text indexing and proceed with LIKE.

Upvotes: 1

Jonathan Wood
Jonathan Wood

Reputation: 67345

No, in fact your example query can't even take advantage of a regular index to speed things up because it doesn't know the first letters of any potential matches.

In general, full-text search is faster than a regular lookup. But LIKE is considerably slower.

Upvotes: 0

qux
qux

Reputation: 1165

You might be interested in full-text search if you care about ranking your set of result or lexical stemming.

Upvotes: 0

Related Questions