6

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?

RThomas
  • 10,702
  • 2
  • 48
  • 61
Costa
  • 3,897
  • 13
  • 48
  • 81

4 Answers4

4

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.

Community
  • 1
  • 1
Paul Sasik
  • 79,492
  • 20
  • 149
  • 189
  • It's not really a search if you just get back a cached result. Also, if the machine has plenty of RAM and there's little else pressing the server then it's possible that many of the pages that make up the table are already loaded into memory and nearly the entire search happens in memory (little or no need to read from disk.) This means that you would get VERY quick results. – Paul Sasik May 08 '12 at 17:05
1

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.

David Gorsline
  • 4,933
  • 12
  • 31
  • 36
  • +1 Except that caching should be absolutely eliminated as playing a role before this option is chosen. The only way to do so is to restart SQL Server (clears everything from the system) and then run the query when the server restarts. That will give you a fairly good idea about what the performance of that search might be under load. – Paul Sasik May 08 '12 at 17:11
  • Actually, you could try to clear the cache via some of the T-SQL from this post: http://stackoverflow.com/questions/1873025 – Paul Sasik May 08 '12 at 17:13
  • I clear the cache and it took 5 seconds, that is not acceptable. – Costa May 08 '12 at 17:33
  • It doesn't surprise me that clearing the cache degrades performance to an unacceptable level. – David Gorsline May 08 '12 at 17:40
0

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

qux
  • 1,155
  • 7
  • 17
0

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.

Jonathan Wood
  • 65,341
  • 71
  • 269
  • 466