MySQL Full-Text Search with Relevance Sort and Solution

Combining Full-Text Search with Sorting by Relevance

Write a MySQL query to search for "cybersecurity" in the "Content" column and order the results by their relevance score.

Solution:

-- Search for the term 'cybersecurity' in the Content column and calculate its relevance score.
SELECT *, MATCH(Content) AGAINST('cybersecurity') AS Relevance
FROM Articles
-- Filter results to include only rows where the term 'cybersecurity' is found.
WHERE MATCH(Content) AGAINST('cybersecurity')
-- Sort the results by the relevance score in descending order to show the most relevant articles first.
ORDER BY Relevance DESC;

Explanation:

• Purpose of the Query:
• To retrieve and rank articles based on how well they match the search term "cybersecurity".
• Combines full-text search with dynamic ordering based on computed relevance.
• Key Components:
• Calculating a relevance score with MATCH...AGAINST.
• ORDER BY Relevance DESC : Orders results with the highest relevance first.
• Real-World Application:
• Enhances user search experiences by presenting the most relevant content at the top.

Notes:

• Relevance scores help in prioritizing search results.
• Performance may be affected on large datasets; proper indexing is crucial.

For more Practice: Solve these Related Problems:

• Write a MySQL query to search for "cybersecurity" in the Content column and order the results by the computed relevance score in descending order.
• Write a MySQL query to perform a full-text search for "cyber attack" in the Content column, compute relevance scores, and sort the articles from highest to lowest relevance.
• Write a MySQL query to search for "data breach" in the Content column, filter out rows with a relevance score below a certain threshold, and order the remaining results by relevance.
• Write a MySQL query to search for "security protocol" in the Content column, calculate the relevance score for each match, and then sort the results in descending order.

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

Previous MySQL Exercise: Updating a Row and Rerunning a Full-Text Search.
Next MySQL Exercise: Limiting Full-Text Search Results.