How to Boost Relevance in MySQL Fulltext Search for Specific Fields?

How to Enhance MySQL Fulltext Search Relevance for Specific Fields

Suppose you have a database with two columns: keywords and content. You've created a fulltext index covering both columns. But, you want to prioritize rows with specific words in the keywords column over those with the same words in the content column.

Solution:

Create three fulltext indexes:

1. Index on keywords column only
2. Index on content column only
3. Index on both keywords and content columns

Then, modify your query as follows:

SELECT id, keyword, content,
  MATCH (keyword) AGAINST ('watermelon') AS rel1,
  MATCH (content) AGAINST ('watermelon') AS rel2
FROM table
WHERE MATCH (keyword,content) AGAINST ('watermelon')
ORDER BY (rel1*1.5)+(rel2) DESC

Explanation:

• rel1 and rel2 calculate the relevance of your query in the keywords and content columns, respectively.
• By multiplying rel1 by 1.5, you give higher weight to keywords.
• The WHERE clause still uses the composite index on keywords and content to ensure results are returned.
• This technique allows you to control recall (what results are returned) and relevance (how matching is prioritized), giving more prominence to specific fields.

The above is the detailed content of How to Boost Relevance in MySQL Fulltext Search for Specific Fields?. For more information, please follow other related articles on the PHP Chinese website!