MS Search Service: Supports weighted search on WHERE the data matched? (not search terms)

Does the Search Service support weighted search on WHERE the data matched, rather than weights on the search terms themselves?

I need a solution that returns weighted search results for users trying to find books on a database-driven website.  For terms typed by the user, if the term matches the book's title then 100 points are contributed to the relative ranking figure.  On the other hand, if it matches the book's description, I only want, say 25 points to contributed to the ranking:

"Book" Table:
BookID, PK int
BookTitle, varchar(20)
BookDescription, varchar(8000)

Example search: The Bible

I want book titles containing "the" and "Bible" to match first, followed by books containing "the" and "Bible" in the description.

Even more importantly, it would be nice to mathematically sum the matching points so that if a book description had "the" and "Bible" many times in the description those results would even appear higher in the search results than "the" and "Bible" in the titles.

We're eager for a solution for this that is "Google" fast!

--> this seems like a fairly common thing to do on e-commerce sites so I'm wondering if I'm missing something.... Does the Search Service have this type of search built-in?

I've researched and played with the WEIGHT keyword but I can't figure out how to pull off this kind of summed search result using WEIGHT.

Loosely, following is the best I could come up with on my own but it's slow... certainly not "Google" that customers expect these days!  We only have a million books in our database but the stored proc takes seconds not milliseconds to run:

ALTER PROCEDURE [io].[BookSearch]
      /* SET NOCOUNT ON */      
      CREATE TABLE #SearchResults (
            BookID int,
            Points int );

      insert into #SearchResults (BookID,Points)
            SELECT [key],100 as Points
            FROM CONTAINSTABLE(BookTitleTable, *, 'Bible')
            SELECT [key],25 as Points
            FROM CONTAINSTABLE(BookDescriptionTable, *, 'Bible');

      select top 10 BookID,sum(Points) as SumPoints
            from #SearchResults
            group by BookID
            order by SumPoints desc;

Thanks in advance,

Who is Participating?

[Webinar] Streamline your web hosting managementRegister Today

David ToddConnect With a Mentor Senior DBACommented:
Hi Pete,

For the above search, have you looked at the actual execution plan?

About how many rows get inserted into #SearchResults in this example?

Try changing the union to union all

ZuZuPetalsAuthor Commented:
I haven't looked at the actual execution plan yet; I'll do that.
All Courses

From novice to tech pro — start learning today.