mysql SORT BY amount of unique word matches

Question

I've found many questions that ask for amount of appearences, but none that ask the very same as I wish to do.

A dynamically generated (prepared-statement) query will result in something like this:

SELECT * FROM products WHERE 
( title LIKE ? AND title LIKE ? ) AND 
( content LIKE ? OR content LIKE ? ) AND 
( subtitle LIKE ? AND author LIKE ? )
ORDER BY relevance LIMIT ?,?

The amount of words entered, (and so the amount of LIKE) are for title,content and author a variable amount (depending on the search query).

Now I've added a ORDER BY relevance. But I wish this order to be the amount of unique words from the content-field that match. (Note: Not on the amount of appearences, but on the amount of entered strings in the content column that have at least one match).

Example table products:

id | title   | subtitle  | content
------------------------------------
1  | animals | cat       | swim swim swim swim swim swim swim
2  | canimal | fish      | some content
3  | food    | roasted   | some content
4  | animal  | cat       | swim better better swims better something else
5  | animal  | cat       | dogs swim better

Example query (with prepared statements ? filled in):

SELECT * FROM products WHERE 
( title LIKE %animal% ) AND 
( content LIKE %dog% OR content LIKE %swim% OR content LIKE %better% ) AND 
( subtitle LIKE %cat% )
ORDER BY relevance LIMIT 0,10

Expected results (in correct order!):

id      | amount of matches
-----------------
5       | 3 (dog, swim, better)
4       | 2 (swim, better)
1       | 1 (swim)

I have an Innodb table and mysql version lower than 5.6, therefore I can't use MATCH...AGAINST. I was thinking this could be solved with WHEN CASE ... THEN. But I have no idea how I could create this sorting.

score 1 · Accepted Answer · answered Oct 23 '13 at 11:39

1

You can do it in many ways for example

ORDER BY SIGN(LOCATE('dog',content))+
         SIGN(LOCATE('swim',content))+
         SIGN(LOCATE('better',content)) DESC

SQLFiddle demo

or with CASE

ORDER BY 
CASE WHEN content LIKE '%dog%' 
        THEN 1
        ELSE 0
END
+
CASE WHEN content LIKE '%swim%' 
        THEN 1
        ELSE 0
END
+
CASE WHEN content LIKE '%better%' 
        THEN 1
        ELSE 0
END

DESC

answered Oct 23 '13 at 11:39

valex

23,966
7
43
60

performance wise any difference? And if i wish to add a count(*) of all found rows (for pagination), is that possible? – PSZ_Code Oct 23 '13 at 11:48
It depends ... I think the second way will be faster [Locate](http://sqlfiddle.com/#!2/d41d8/23552) vs [Like](http://sqlfiddle.com/#!2/d41d8/23554). And also here the same result: [MySQL LIKE vs LOCATE](http://stackoverflow.com/questions/7499438/mysql-like-vs-locate) – valex Oct 23 '13 at 11:56
I will propably be using the second way, as the %-signs are already appended to the words, so I can't use the first method. Can I write CASE WHEN content LIKE ? (for prepared statements)? And does the ELSE 0 have to be added? – PSZ_Code Oct 23 '13 at 11:58

Sanal K · Answer 2 · 2013-10-23T12:06:45.273

1

Check like this.

    SELECT id,CONCAT_WS('-',COUNT(LENGTH(content) - LENGTH(REPLACE(content, ' ', '')) + 1),REPLACE(content,' ',',')) AS amount of matches FROM products 
        WHERE 
        ( title LIKE %animal% ) AND 
        ( content LIKE %dog% OR content LIKE %swim% OR content LIKE %better% ) AND 
        ( subtitle LIKE %cat% )
        GROUP BY id
ORDER BY id

edited Oct 23 '13 at 12:06

answered Oct 23 '13 at 11:59

Sanal K

723
4
14

mysql SORT BY amount of unique word matches

2 Answers2

Linked