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

Question

I have two fields in posts table - post_title and post_content. Now I use standard full text search to match some keywords against both fields. I need to give the title field more relevance than the content field and than order the results by relevance...

What would the mysql syntax look like to achieve this goal? I use 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, build your query in the following manner:

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 title 2 times more relevance than the body.

This is quite handy when you need to allow the content to be sorted, for instance, by tags (which are not viewed by the users) because it allows you to tweak the results from behind the scenes.