mariadb - MySQL fulltext with score by columns

Question

I created an index like this:

ALTER TABLE `blog_posts`
ADD FULLTEXT `title_description_content` (`title`, `description`, `content`);

I can search with:

SELECT * FROM `blog_posts`
WHERE MATCH(title, content, description)
AGAINST("lorem ipsum" IN NATURAL LANGUAGE MODE)
LIMIT 12

But I want to score by column. For example, that the title column is worth 3 points, that the description is worth 2 and the content is worth 1. So that words found in the title have a higher score.

I know this is possible because I already did it once, but I lost the source and I couldn't find any example on Google.

Thanks!

Answer 1

You can use multiple indexes for your problem

SELECT *,
  MATCH (title) AGAINST ("lorem ipsum" IN NATURAL LANGUAGE MODE) AS rel1,
  MATCH (content) AGAINST ("lorem ipsum" IN NATURAL LANGUAGE MODE) AS rel2,
  MATCH (description) AGAINST ("lorem ipsum" IN NATURAL LANGUAGE MODE) AS rel3
FROM `blog_posts`
WHERE MATCH(title, content, description) AGAINST("lorem ipsum" IN NATURAL LANGUAGE MODE)
ORDER BY (rel1 * 3)+(rel2 * 2)+(rel3 * 1) DESC
LIMIT 12

The weight multiplications can be finetuned