Rank by relevance for boolean AND non boolean sear

binzy

Hello

I have a problem with my search code.

I wanted to have boolean search for exact phrases, ranked by relevance - I looked around and found that this query worked..

$query = "SELECT *,MATCH (col1, col2) AGAINST ('var')
AS relevance FROM Table1 WHERE MATCH (col1, col2) AGAINST ('+var' IN BOOLEAN MODE)";

So search for "var1 var2"" results in rows with only var1 and var2 together.

However, if I use this query, the search queries without double quotes "" (i.e. var1 OR var2) are NOT ranked by relevance. I want that if a user puts var1 var2 as search variables, the rows which have var1 and var2 should be displayed before the rows with either of these.

Is there a best of both worlds way to do this - I mean boolean search queries for exact phrases ("var1 var2"), ranked by relevance, as well as inexact search queries (var1 var2), ranked by relevance too?

Please help. Thanks!

Weedpacket

Without an ORDER BY clause in the SELECT statement the DBMS is free to order matched records in whatever fashion it pleases. Which might be the order you want. Or it might not. No guarantees.

Since "relevance" is one of the fields you're retrieving, I'd guess you'd want to order by that.

binzy

Thanks, I put in the ORDER BY relevance clause as suggested, but it did not work 🙁
For some reason the boolean search for exact phrases did not rank by relevance too, which was working earlier, without the ORDER BY clause.

Any other suggestions, please?

johanafm

How did it not work? No ordering at all, or ordering in the "wrong" direction? ORDER BY relevance DESC should do the trick. Relevance is a floating point number where 0 means no relevance at all.

binzy

No ordering at all...At the risk of sounding completely foolish, I am not very sure about this relevance field. I assume that this number is assigned automatically by MySQL as I dont have this field in my db explicitly?

Without the ORDER BY, the above query gives correct results for exact phrases, ranked by relevance. With the ORDER BY, that doesnt work too.

binzy

Sorry, my previous message was incorrect. Using the ORDER BY relevance DESC clause, the exact phrase boolean search works fine, ranked by relevance in decreasing order. However, my main problem remains same as before - the results for OR search queries such as (var1 var2) do NOT get ranked by relevance.

Any help will be appreciated. Thanks.

Weedpacket

Hm, what is the value of "relevance", then? Try printing that alongside the results to see.

Your idea of what is "relevant" might not be the same as MySQL's. And note that I don't use MySQL enough to know whether your query is correct to begin with.