ordering results based on combine weightage of fields

Question

I have a very interesting question for all you SQL masters out there.

As you all know that one of the prime requirement of any e-commerce based shopping cart is a good search.

Few days back, I got to know about MySQL full-text search, and I must say it's quiet interesting.

I want to go one step ahead in search query, and need to provide weightage to fields. These weightage would allow sorting of search result.

For example: I have following fields in product table

productid
name
description
price

'name' and 'description' fields are full-text indexed.

For any search keyword, query should look into both fields: name, description. But name field should have more weightage compared to description field.

Incase, if you haven't understand my question well, then following example should help.

 productid             name                   description                price
 1                  nice keyboard      this is a nice nice keyboard     10                
 2                  nice nice keyboard this is a nice nice keyboard     10                
 3                  keyboard           this is a nice keyboard     10

when search keyword is 'nice', and 'name' field have weightage: 10, and 'description' field have weightage: 1

for productid: 1, nice was found 1 (once) in name, 2 (twice) in description. so combine weightage is (1*10 + 2*1 = 10 + 2 = 12)

for productid: 2, nice was found 2 (twice) in name, 2 (twice) in description. so combine weightage is (2*10 + 2*1 = 20 + 2 = 22)

for productid: 3, nice was found 0 (none) in name, 1 (once) in description. so combine weightage is (0*10 + 1*1 = 0 + 1 = 1)

2 questions

(1) How can I provide weightage to a particular field? Do I need to create full-text index again to provide weightage?

(2) I need query, which can output results in desc. order of combine weightage.

Kevin Burton · Accepted Answer · 2011-08-08T15:33:24.730

The full text search can give you the relative relevance of the search:

SELECT * FROM
(
    SELECT id,
    (MATCH(name) AGAINST ('nice')*10 ) + MATCH(description) AGAINST('nice') AS relevance
    FROM products
    WHERE MATCH(name) AGAINST ('nice') OR
    MATCH(description) AGAINST ('nice')
) ORDER BY revelence DESC

By multiplying the name relevance by 10 I have skewed the relevance score towards that of names

ordering results based on combine weightage of fields

1 Answers1

Linked