1

Probably a noob question, but I'll go for it nevertheless.

For sake of example, I have a Person table, a Tag table and a ContactMethod table. A Person will have multiple Tag records and multiple ContactMethod records associated with them.

I'd like to have a forgiving search which will search among several fields from each table. So I can find a person by their email (via ContactMethod), their name (via Person) or a tag assigned to them.

As a complete noob to FTS, two approaches come to mind:

  1. Build some complex query which addresses each field individually
  2. Build some sort of lookup table which concatenates the fields I want to index and just do a full-text query on that derived table.

(Feel free to edit for clarity; I'm not in it for the rep points.)

Larsenal
  • 49,878
  • 43
  • 152
  • 220
  • This is not so much a request for clarification from yourself as it is from the general Stack Overflow community at large, but is this really a job for full-text search? My understanding always has been that FTS is for searching big blobs of text in the database-- is this incorrect? Given that the name, contact and tag fields described are likely to me small, could this not be handled by a bunch of LIKE queries? – Ken Keenan Jul 14 '09 at 21:11
  • @Ken: Is not only a question of size, but also FTS can do linguistic stuff LIKE cannot, like thesaurus, word breaker, stemmers etc. – Remus Rusanu Jul 14 '09 at 21:21
  • MS wrote a good article http://technet.microsoft.com/en-us/library/ms345119(SQL.90).aspx "A LIKE query (for example, '%cencini%') against millions of rows of text data can take minutes to return; whereas a full-text query (for 'cencini') can take only seconds or less against the same data, depending on the number of rows that are returned." – u07ch Jul 14 '09 at 21:22
  • @Ken: my point: is not *only* the size that matters. I'm not saying that FTS *is* the right answer, FTS comes with a big baggage (runtime external indexing service, maintenance, licensing etc). – Remus Rusanu Jul 14 '09 at 21:25
  • @Ken it may be that FTS is not the right tool for the job. I could have included in my example the fact that we will be searching some freely input text where the linguistic stuff will be handy to search for particular input. Additionally, I think we'll see some use of weighting and ranking. My simplistic example may not have suggested these uses. – Larsenal Jul 14 '09 at 22:20

3 Answers3

0

One possibility is to make a view which has these columns: PersonID, ContentType, Content. ContentType would be something like "Email", "PhoneNumber", etc... and Content would hold that. You'd be searching on the Content column, and you'd be able to see what the person's ID is. I'm not 100% sure how full text search works though, so I'm not sure if you could use that on a view.

Jon Onstott
  • 13,499
  • 16
  • 80
  • 133
0

The FTS can search multiple fields out-of-the-box. The CONTAINS predicate accepts a list of columns to search. Also CONTAINSTABLE.

Remus Rusanu
  • 288,378
  • 40
  • 442
  • 569
  • I see how I can query multiple columns, but I'm (ignorantly) unsure whether that addresses the issue of hierarchical data. Thoughts? – Larsenal Jul 15 '09 at 16:03
0

If your sql server supports it you can create an indexed view and full text search that; you can use containstable(*,'"chris"') to read all the columns.

If it doesn't support it as the fields are all coming from different tables I think for scalability; if you can easily populate the fields into a single row per record in a separate table I would full text search that rather than the individual records. You will end up with a less complex FTS catalog and your queries will not need to do 4 full text searches at a time. Running lots of separate FTS queries over different tables at the same time is a ticket to query performance issues in my experience. The downside with doing this is you lose the ability to search for Surname on its own; if that is something you need you might need to look at an alternative.

In our app we found that the single table was quicker (we can't rely on customers having enterprise sql at hand); so we populate the data with spaces into an FTS table through an update sp then our main contact lookup runs a search over the list. We have two separate searches to handle finding things with precision (i.e. names or phone numbers) or just for free text. The other nice thing about the table is it is relatively easy and low cost to add further columns to the lookup (we have been asked for social security number for example; to do it we just added the column to the update SP and we were away with little or no impact.

Rich Seller
  • 83,208
  • 23
  • 172
  • 177
u07ch
  • 13,324
  • 5
  • 42
  • 48