Give some fields more relevance and sort by relevance in mysql full text search

Question

I have two fields in the message table - post_title and post_content . Now I use standard full-text search to match some keywords for both fields. I need to specify the header field more relevant than the content field and order the results by relevance ...

What will be the mysql syntax to achieve this? I am using mysql 5.1

Answer 1

First create three FULLTEXT indexes:

* one on the title column * one on the body column * one on both title and body columns 

Then create your request as follows:

 SELECT field1, field2, field3, title, body, MATCH (title) AGAINST ('word_to_search') AS rel_title, MATCH (body) AGAINST ('word_to_search') AS rel_body FROM table_to_use WHERE MATCH (title,body) AGAINST ('word_to_search') ORDER BY (rel_title*2)+(rel_body) 

This will give the name 2 times more than the body.

This is very convenient when you need to enable sorting of content, for example, using tags (which are not viewed by users), since it allows you to customize the results from behind the scenes.