MySql: Ordering results by number of matches in a space-delimited value column

Question

I have a MySQL database table that has a "word" column with space-delimited values. For example:

one two three four five

So given the record above, I want to find other records that contain one or more of those space-delimited values, and order the results by most number of relevant matches. How can I go about achieving this?

For example:

one two six seven eight
three nine ten eight seven
one two three six seven
one two three four ten
six seven eight nine ten

I would want the following returned:

one two three four ten --4 matching values
one two three six seven --3 matching values
one two six seven eight --2 matching values
three nine ten eight seven --1 matching value

This is the query I'm using so far:

SELECT * from MyTable WHERE words like '%one%' OR words like '%two%' OR words like '%three%' OR words like '%four%' OR words like '%five%'

But that only selects results that share one or more of the words. How can I keep track of how many matches there are and order the results in terms of that?

Answer 1

Hi,

You might take a look at implementing a full text search, such as the type described here.

Although another very messy method would be to use the like statements and then parse the result set with a regular expression to get the number of matches per record therefore allowing you to implement a rank based on number or matches.

Enjoy!

Answer 2

I think there is no simple and elegant way how to achieve that. My solution is this:

SELECT MyTable.* FROM MyTable JOIN 
(SELECT row_id, COUNT(*) AS count
    FROM
        (
        SELECT row_id from MyTable WHERE words like '%one%'
        UNION
        SELECT row_id from MyTable WHERE words like '%two%'
        )
    GROUP BY row_id
) AS count_result
ON MyTable.row_id=count_result.row_id
ORDER BY count_result.count DESC