All geek questions in one place

Why would I like to use full-text search?

I'm new to full-text search, I used the following query

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

The students table contains millions of random entries and looks like this: 'QZAQHIEK VABCNLRM KFFZJYUU'

It took just 2 seconds and led to 1,100 rows. If a million records are searched in two seconds, why will I use full-text search ?! Like the predicate also used the full text index?

+6
source share
4 answers

I think you answered your question, at least to your satisfaction. If your prototyping yields results in an acceptable amount of time, and you are sure that caching does not explain the quick answer (for Paul Sasik), be sure to skip the overhead of full-text indexing and continue with LIKE.

+1
source

Not. LIKE does not use full text indexing. See here.

Computers are pretty good these days, but if you see the search results faster than you expect, you might just have returned the cached result set because you previously performed the same query. To make sure you are not getting cached results, you can use DBCC DROPCLEANBUFFERS. Take a look at this post for some SQL Server cache cleanup options.

Excerpt from linked page:

Comparing LIKE with full-text search

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

+4
source

No, actually your sample query cannot even use a regular index to speed things up, because it does not know the first letters of any potential matches.

In general, full-text searches are faster than regular searches. But LIKE is much slower.

0
source

Source: https://habr.com/ru/post/915208/

More articles:


All Articles