How Can I Prioritize Column Relevance in MySQL Full-Text Search?

Prioritizing Column Relevance in MySQL Full-Text Search with match() and against()

In a full-text search using MySQL's MATCH() AGAINST(), ordering results by relevance is straightforward. However, what if you want to prioritize the relevance of a specific column, such as head, while still considering the relevance of other columns like body?

Consider this initial query:

SELECT * FROM pages WHERE MATCH(head, body) AGAINST('some words' IN BOOLEAN MODE)

It performs a full-text search on the head and body columns, returning matching rows.

To order by relevance, we can add a computed column:

SELECT *, MATCH (head, body) AGAINST ('some words' IN BOOLEAN MODE) AS relevance 
FROM pages
WHERE MATCH (head, body) AGAINST ('some words' IN BOOLEAN MODE)
ORDER BY relevance

This introduces a relevance column that indicates the overall relevance of each row. However, it treats both the head and body columns equally.

To prioritize the head column, one approach is to add a separate relevance column for it:

SELECT *,
       MATCH (head, body) AGAINST ('some words') AS relevance,
       MATCH (head) AGAINST ('some words') AS title_relevance
FROM pages
WHERE MATCH (head, body) AGAINST ('some words')
ORDER BY title_relevance DESC, relevance DESC

This creates a title_relevance column that represents the relevance of the head column alone. By ordering first by title_relevance and then by relevance, we prioritize rows with highly relevant head content.

As a bonus, we could modify the ORDER BY clause to factor in the number of occurrences of the specified words in the head column:

ORDER BY title_relevance + (total_head_words * weight_per_word) DESC

Here, total_head_words represents the number of times the words appear in the head column and weight_per_word is a multiplier that adjusts their importance.

Alternatively, if you have the flexibility to use PostgreSQL, it offers more advanced ranking and weighting capabilities for full-text search.

The above is the detailed content of How Can I Prioritize Column Relevance in MySQL Full-Text Search?. For more information, please follow other related articles on the PHP Chinese website!