How to sort mysql search result by relevance?

Question

I have a similar query as asked on this question

MySQL - How to ORDER BY RELEVANCE? INNODB Table

Difference is, here I want to search from 5 fields as

add1, add2, add3, post_town, post_code

And only records in post_code field will be NOT EMPTY, other fields records may be empty at some places. If I search for keyword kingston, it returns

Acre Road, Kingston upon Thames, KT2 6EA

Kingston Road, Epsom, KT19 0DG

Kingston Road, Epsom, KT20 0DH

and these results are combination of all fields add1, add2, add3, post_town, post_code

I need this result in following order

Kingston Road, Epsom, KT19 0DG

Kingston Road, Epsom, KT20 0DH

Acre Road, Kingston upon Thames, KT2 6EA

My Current SQL query is like this

SELECT add1, add2, add3, post_town, post_code FROM address_mst 
WHERE add1 LIKE '%".$keyword."%'
OR add2 LIKE '%".$keyword."%'
OR add3 LIKE '%".$keyword."%'
OR post_town LIKE '%".$keyword."%'
OR post_code LIKE '%".$keyword."%'

So I need records with search Keyword coming in the beginning would come first. How can I do that ?

score 5 · Accepted Answer · answered Sep 28 '11 at 11:55

Not the prettiest but should work!

select a.* from (
SELECT add1, add2, add3, post_town, post_code, 1 as rank FROM address_mst 
WHERE add1 LIKE '%".$keyword."%'
UNION
SELECT add1, add2, add3, post_town, post_code, 2 as rank FROM address_mst 
WHERE add2 LIKE '%".$keyword."%'
UNION
SELECT add1, add2, add3, post_town, post_code, 3 as rank FROM address_mst 
WHERE add3 LIKE '%".$keyword."%'
UNION
SELECT add1, add2, add3, post_town, post_code, 4 as rank FROM address_mst 
WHERE post_town LIKE '%".$keyword."%'
UNION
SELECT add1, add2, add3, post_town, post_code, 5 as rank FROM address_mst 
WHERE post_code LIKE '%".$keyword."%'
) a
order by a.rank asc;

thank you very much, this worked perfactly. just did one modification in the last line to look it more smarter order by a.rank asc, add1, add2, add3, post_town, post_code; — aslamdoctor, Sep 28 '11 at 12:14

DhruvPathak · Answer 2 · 2011-09-29T04:48:57.003

1

Why not select the minimal qualifying results with query, and use own logic in the script to give advance ranking weightage to your result and sort them accordingly ?

That would help you to :

Have more complex ranking criteria which may or may not be possible in the query.
Not editing the sql query if your ranking criteria changes.

edited Sep 29 '11 at 04:48

answered Sep 28 '11 at 11:59

DhruvPathak

42,059
16
116
175

Agree. I can't help feeling that this is something that would be best solved outside of a SQL query – Tom Mac Sep 28 '11 at 12:01

score 1 · Answer 3 · answered Sep 28 '11 at 13:14

1

For relevancy based sorting, FULL TEXT SEARCH is the more reliable option.

FULL TEXT SEARCH used relevancy based on the number of occurrences of the searched term which could be later on used to ORDER results returned.

answered Sep 28 '11 at 13:14

Ghazanfar Mir

3,493
2
26
42

How to sort mysql search result by relevance?

3 Answers3