Search MySQL relevance for ranking specific columns above

Question

So, I read below:

How can I manipulate the relevance of MySQL full-text search to make one field more “valuable” than another?

I was interested in the answer below and tried to implement this with success.

SELECT url, keywords, title, MATCH (keywords) AGAINST ('green watermelon') AS rel1, MATCH (title) AGAINST ('green watermelon') AS rel2 FROM straight WHERE MATCH (keywords,title) AGAINST ('green watermelon') ORDER BY (rel1)+(rel2*1.5) 

My only problem is this.

If the user should search for "Green Watermelon" that matches 3 rows in a table with two columns, as shown below:

  + ------------ + ------------ +
 |  Keyword |  Title |
 + ------------ + ------------ +
 |  Green |  Green |
 |  Green |  Watermelon |
 |  Watermelon |  Watermelon |
 + ------------ + ------------ +

I would like the second entry to be ranked first, as it is most relevant, but they all have the same relevance to the search term “Green Watermelon” because “Green” is used twice in the keyword and title.

The best way, in my opinion, can be solved if he finds the word “green”, as soon as he increases the relevance, say 1, and at any other time, when he sees the word “green”, he increases it by 0 if he sees a watermelon, it does the same thing. This would give the second result a relevance score of 2, and the remaining 2 lines - a relevance of 1. Therefore, make it more relevant, as it should be. This will work for longer searches when searching in a larger database.

EDIT:

If possible, I can create another column that has both a heading and keywords, so let's say the column is called “mashup”, now the table will look larger:

  + ------------ + ------------ + ----------------------- +
 |  Keyword |  Title |  Mashup |
 + ------------ + ------------ + ----------------------- +
 |  Green |  Green |  Green Green |
 |  Green |  Watermelon |  Green Watermelon |
 |  Watermelon |  Watermelon |  Watermelon Watermelon |
 + ------------ + ------------ + ----------------------- +

Then, if possible, we will need to trick the fields so that they end like this:

  + ------------ + ------------ + ----------------------- +
 |  Keyword |  Title |  Mashup |
 + ------------ + ------------ + ----------------------- +
 |  Green |  Green |  Green |
 |  Green |  Watermelon |  Green Watermelon |
 |  Watermelon |  Watermelon |  Watermelon |
 + ------------ + ------------ + ----------------------- +

Then the search will work as intended with an exact match.

Thank you in advance

Answer 1

You need to specify only the more important column in the first position of your MATCH ()

  SELECT url, keywords, title FROM straight WHERE MATCH(title, keywords) AGAINST('green watermelon') ORDER BY MATCH(title, keywords) AGAINST('green watermelon') DESC; +------+------------+------------+ | id | keywords | title | +------+------------+------------+ | 2 | Green | Watermelon | | 3 | Watermelon | Green | | 1 | Green | Green | +------+------------+------------+