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]
as
      /* SET NOCOUNT ON */      
      CREATE TABLE #SearchResults (
            BookID int,
            Points int );

      insert into #SearchResults (BookID,Points)
            SELECT [key],100 as Points
            FROM CONTAINSTABLE(BookTitleTable, *, 'Bible')
            union
            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,
Pete

LVL 2
ZuZuPetalsAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

David ToddSenior 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

HTH
  David
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
ZuZuPetalsAuthor Commented:
I haven't looked at the actual execution plan yet; I'll do that.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2005

From novice to tech pro — start learning today.