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.

Go to:

• Advanced Full-Text Search Techniques for MySQL Mastery Exercises Home. ↩
• MySQL Exercises Home ↩

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

PREV : Boolean Mode Full-Text Search.
NEXT : Full-Text Search Using Query Expansion.