Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

MySQL order by relevance

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?

like image 785
Fraser Avatar asked Feb 22 '13 14:02

Fraser


People also ask

Does order matter in MySQL?

Whereas if the telephone book were organized by first name then by last name, you'd find all the Johns together, then within the Johns, all the 'S' last names would be grouped together. So the order of columns in a multi-column index definitely matters. One type of query may need a certain column order for the index.

Does order of columns matter in MySQL?

column order does not matter. This is purely a convenience feature. just to allow you to restructure your database table the way you like after it has been created.

Can we use 2 ORDER BY in MySQL?

If you want to select records from a table but would like to see them sorted according to two columns, you can do so with ORDER BY . This clause comes at the end of your SQL query.

How do I use ORDER BY before GROUP BY?

When combining the Group By and Order By clauses, it is important to bear in mind that, in terms of placement within a SELECT statement: The GROUP BY clause is placed after the WHERE clause. The GROUP BY clause is placed before the ORDER BY clause.


2 Answers

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
like image 189
Fraser Avatar answered Oct 07 '22 16:10

Fraser


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;
like image 42
Rehan Anis Avatar answered Oct 07 '22 17:10

Rehan Anis