Full-Text Search

Kendra Little gives the scoop on full-text indexing:

The “dirty little secret” about full-text search indexes is that they don’t help with ‘%blabla%’ predicates.

Well, it’s not a secret, it’s right there in the documentation.

A lot of us get the impression that full-text search is designed to handle “full wildcard” searches, probably just because of the name. “Full-Text Searches” sounds like it means “All The Searches”. But that’s not actually what it means.

Kendra’s take is a bit more optimistic than mine; I’m definitely more inclined to dump text out to a Lucene-based indexing system (like Solr or ElasticSearch), as they’ll typically perform faster and solve problems that full-text cannot.  Some of that may just be that I was never very good at full-text indexing, though.

Related Posts

Adaptive Joins and Index Width

Erik Darling wants to pump your indexes up: Now, there’s an Extended Event that… Used to work. These days it just stares blankly at me. But since I’ve worked with this before, I know the problem. It’s that Key Lookup — I’ll explain more in a minute. Adaptive joins won’t do all the work for you, […]

Read More

Making Non-SARGable Queries SARGable with an Index

Denis Gobo violates Betteridge’s Law of Headlines: This question came up the other day from a co-worker, he said he couldn’t change a query but was there a way of making the same query produce a better plan by doing something else perhaps (magic?) He said his query had a WHERE clause that looked like […]

Read More

Categories

February 2017
MTWTFSS
« Jan Mar »
 12345
6789101112
13141516171819
20212223242526
2728