MySQL Full-Text Search with Weighting and Solution

Full-Text Search with Column Weighting Simulation

Write a MySQL query to perform a full-text search for "technology" in both the "Title" and "Content" columns and simulate column weighting by comparing relevance scores.

Solution:

-- Search for the term 'technology' in both the Title and Content columns.
-- Calculate relevance scores for matches in the Title and Content separately.
-- Assign a higher weight to matches in the Title by multiplying its relevance score by 2.
-- Combine the weighted Title relevance and Content relevance into a TotalRelevance score.
-- Filter results to include only rows where 'technology' is found in either Title or Content.
-- Sort the results by TotalRelevance in descending order to show the most relevant articles first.

SELECT *,
       MATCH(Title) AGAINST('technology') AS TitleRelevance, -- Calculate relevance score for matches in the Title column.
       MATCH(Content) AGAINST('technology') AS ContentRelevance, -- Calculate relevance score for matches in the Content column.
       (MATCH(Title) AGAINST('technology') * 2 + MATCH(Content) AGAINST('technology')) AS TotalRelevance -- Combine scores with Title weighted higher.
FROM Articles
WHERE MATCH(Title, Content) AGAINST('technology') -- Filter rows where 'technology' is found in either Title or Content.
ORDER BY TotalRelevance DESC; -- Sort results by the combined relevance score in descending order.

Explanation:

• Purpose of the Query:
• To prioritize matches in the Title column by simulating a weighted relevance score.
• Demonstrates a method to boost relevance from more important columns.
• Key Components:
• Calculating separate relevance scores and combining them with a weight factor.
• ORDER BY TotalRelevance DESC : Orders articles by the calculated weighted score.
• Real-World Application:
• Useful when titles are more indicative of content quality than body text.

Notes:

• Weight factors can be adjusted according to application requirements.
• Ensure consistency in full-text indexing across columns.

For more Practice: Solve these Related Problems:

• Write a MySQL query to search for "technology" in both the Title and Content columns, simulate column weighting by doubling the Title's relevance score, and order the results by the total score.
• Write a MySQL query to perform a full-text search for "innovation" in the Title and Content columns, calculate separate relevance scores, and boost the Title's score before combining them.
• Write a MySQL query to search for "startups" in both the Title and Content columns, assign a higher weight to Title matches, and filter results based on a combined weighted relevance score.
• Write a MySQL query to perform a full-text search for "entrepreneurship" in the Title and Content columns, where the Title's relevance score is multiplied by 2 and then summed with the Content's score, with the results ordered by the total.

Have another way to solve this solution? Contribute your code (and comments) through Disqus.

Previous MySQL Exercise: Full-Text Search with Custom Stopword List Consideration.
Next MySQL Exercise: Paginating Full-Text Search Results.