MySQL order by relevance

Question

I have a search form which searches a site content table to pull back appropriate results.

I want to search the title and content fields and pull back results in order of relevance. Giving highest priority to the title.

Say we had a table (tblContent) of

intID | strTitle    | txtContent

1     | Smith John  | Lorem Ipsum
2     | Lorem Ipsum | Lorem John Smith Ipsum
3     | John Smith  | Lorem Ipsum Lorem Ipsum
4     | Lorem Ipsum | Lorem Ipsum Lorem Ipsum
5     | Lorem Ipsum | Lorem Ipsum Smith John

And you were searching for "John Smith" the results should come back in the order of 3,2,1,5

How is this possible?

~150 rows currently. It's a fairly small site so will never get near even 1k rows — Fraser, Feb 22 '13 at 14:23
See that, maybe it might help: http://stackoverflow.com/a/35512410/1673161 — Emmanuel Mahuni, Feb 19 '16 at 18:37
A good explanation of the multi-index relevance solution here https://stackoverflow.com/questions/547542/how-can-i-manipulate-mysql-fulltext-search-relevance-to-make-one-field-more-val — coterobarros, Sep 07 '18 at 13:57

score 22 · Accepted Answer · answered Feb 22 '13 at 14:33

22

I managed to get pretty spot on with this:

SELECT *, 
( (1.3 * (MATCH(strTitle) AGAINST ('+john+smith' IN BOOLEAN MODE))) + (0.6 * (MATCH(txtContent) AGAINST ('+john+smith' IN BOOLEAN MODE)))) AS relevance 
FROM content 
WHERE (MATCH(strTitle,txtContent) AGAINST ('+john+smith' IN BOOLEAN MODE) ) 
ORDER BY relevance DESC

answered Feb 22 '13 at 14:33

Fraser

14,036
22
73
118

Some things weigh more than others if he uses 1.3 and .6 The numbers are his preference – keji Oct 19 '13 at 01:13
This method is great, but produces non-deterministic ordering of results that have the same exact relevance score. To work around that, add more order-by columns. – jkt123 Aug 18 '17 at 21:59
I have checked it, using this method the query time is 0.094 seconds, while using the case method below it's 0.063 seconds, and the accuracy of results is better in case method – Rehan Anis Aug 06 '18 at 14:19
3

I have no idea what's happening in this query. Can someone please add documentation in it. – Faizan Anwer Ali Rupani Oct 14 '18 at 16:26
@FaizanAnwerAliRupani it's using a fulltext index. You have to create the index first, then you can use the MATCH operator. One oddity in MySQL is that you cannot order by the MATCH column unless you include it in the SELECT statement. https://dev.mysql.com/doc/refman/8.0/en/fulltext-natural-language.html – Dave Hilditch Apr 01 '20 at 21:17
@FaizanAnwerAliRupani When we ORDER BY relevance DESC can we order it to the another column in that relevance. In my query I need to display latest relevant records based on the posted_date. – Prageeth Liyanage Nov 06 '20 at 09:17

score 4 · Answer 2 · answered Dec 19 '13 at 15:57

4

mysql fulltext search is a good thing but it has a limit of minimum 4 characters word to be indexed. Al tough the limit can be changed but changing server variables isn't possible in all scenarios. In such a situation, I recommend the solution suggested in order by case

select 
    *
from
mytable a
WHERE
    (a.title like 'somthing%'
    OR a.title like '%somthing%'
    OR a.title like 'somthing%')
ORDER BY case
WHEN a.title LIKE 'somthing%' THEN 1
WHEN a.title LIKE '%somthing%' THEN 2
WHEN a.title LIKE '%somthing' THEN 3
ELSE 4 END;

answered Dec 19 '13 at 15:57

Rehan Anis

788
1
5
7

LIKE operators are incredibly slow once you scale. And the minimum of 4 characters is just the default setting for MyISAM tables. The default is 3 characters for InnoDB, but in any case you can change the configuration to allow 2 character words: https://dev.mysql.com/doc/refman/8.0/en/innodb-parameters.html#sysvar_innodb_ft_min_token_size https://dev.mysql.com/doc/refman/8.0/en/server-system-variables.html#sysvar_ft_min_word_len – Dave Hilditch Apr 01 '20 at 21:15

score 3 · Answer 3 · answered Feb 22 '13 at 14:23

3

There is probably a more efficient way and given the search string could be more than 2 words this probably isn't feasible, but i'd do something like

ORDER BY CASE 
 WHEN strTitle LIKE '%John Smith%' THEN 1
 WHEN txtContent LIKE '%John Smith%' THEN 2
 WHEN strTitle LIKE '%Smith John%' THEN 3
 WHEN txtContent LIKE '%Smith John%' THEN 4
ELSE 5 END

answered Feb 22 '13 at 14:23

Matt Busche

14,216
5
36
61

I like the idea but as you mention the search could be an unlimited number of words as it is coming from a search input – Fraser Feb 22 '13 at 14:25
@Fraser you should never give user an oportunity of anything unlimited. Anyway I dont see problem with query contatenate :) (you can use dynamic placeholder, dynamicly sanitize user data and dynamicly concatenate query :) – jave.web Dec 20 '13 at 15:05

score 0 · Answer 4 · answered Feb 22 '13 at 14:23

0

What about something along the lines of

SELECT INT(id), strTitle, txtContent
FROM tblContent
WHERE name like '%John%'
GROUP BY strTitle
ORDER BY CASE WHEN strTitle like 'John %' THEN 0
           WHEN strTitle like 'John%' THEN 1
           WHEN strTitle like '% John%' THEN 2
           ELSE 3
      END, strTitle

answered Feb 22 '13 at 14:23

Sim

570
1
10
22

The issue is that the search criteria is to come from a user populated search input so the correct solution needs to be able to handle anything the user throws at it – Fraser Feb 22 '13 at 14:26

MySQL order by relevance

4 Answers4

Linked