MySQL Full-Text Search with Relevance Ranking and Solution

Full-Text Search with Relevance Ranking

Write a MySQL query to search for "performance" in the "Content" column and display a relevance score for each result.

Solution:

-- Search for 'performance' and display the relevance score.

-- Select all columns from the Articles table and calculate the relevance score for the term 'performance'.
SELECT *, 
    -- Use MATCH...AGAINST to calculate the relevance score for the term 'performance'.
    -- The relevance score indicates how well the Content column matches the search term.
    MATCH(Content) AGAINST('performance') AS Relevance

-- Specify the table from which to retrieve the data.
FROM Articles

-- Filter rows where the Content column matches the term 'performance'.
WHERE MATCH(Content) AGAINST('performance')

-- Order 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 articles containing "performance" and rank them by relevance.
• Demonstrates how to calculate and order results based on relevance scores.
• Key Components:
• MATCH(Content) AGAINST('performance') AS Relevance : Computes the relevance score.
• ORDER BY Relevance DESC : Orders the results with the highest scores first.
• Real-World Application:
• Useful for search engines and applications that need to display the most pertinent results at the top.

Notes:

• Ensure that the full-text index is maintained for accurate relevance scoring.
• Relevance scoring may vary based on the frequency of terms.

For more Practice: Solve these Related Problems:

• Write a MySQL query to search for "performance tuning" in the "Content" column and return only rows with a relevance score above a defined threshold.
• Write a MySQL query to retrieve articles matching "cache optimization" in the "Content" column and alias the computed relevance score as "MatchScore".
• Write a MySQL query to search for "resource allocation" in the "Content" column and sort the results by descending relevance score.
• Write a MySQL query to filter articles from the "Content" column where the relevance score for "load balancing" exceeds the average score of the dataset.

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

Previous MySQL Exercise: Boolean Mode Full-Text Search.
Next MySQL Exercise: Full-Text Search Using Query Expansion.