MySQL Relevance Order

Question

I have a search form that looks for a table of content for a site to reverse the relevant results.

I want to search the header and content fields and select the results in order of relevance. Giving the highest priority to the name.

Say we had a table (tblContent)

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 searched for "John Smith", the results should return in order of 3.2.1.5

How is this possible?

Answer 1

I managed to handle 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 

Answer 2

There is probably a more efficient way, and if the search string can be more than two words, this is probably not possible, but I would 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 

Answer 3

A full-text mysql search is fine, but it has a limit of at least 4 characters to index. Al hard restriction can be changed, but changing server variables is not possible in all scenarios. In such a situation, I recommend the solution proposed in order

 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; 

Answer 4

How about something in the lines

 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