How to weight 'ORDER BY' in mysql?

Question

I have a full text query that ends with:

 ORDER BY RELEVANCE DESC, CLICK_RATE DESC

Can I give weight to the columns in the order by? Maybe 0.3 to relevance and 0.7 to click_rate?

As things are right now, even if I switch them around, the results are not satisfactory.

As an alternative how can have the top 3 results sorted by CLICK RATE and the rest sorted by relevance.

Conrad Frix · Answer 1 · 2012-04-23T21:54:55.360

12

This should work

ORDER BY (.3 * RELEVANCE) + (.7 * CLICK_RATE) DESC

DEMO

Update from comments

to make top 3 results sort by click_rate, and the rest sort by relevance

You'd need to first identify the first 3 using a subquery and do the ordering

SELECT test.id, 
       test.relevance, 
       test.click_rate, 
       top_3_click_rate.id         t3_id, 
       top_3_click_rate.click_rate t3_click_rate 
FROM   test 
       LEFT JOIN (SELECT id, 
                         click_rate 
                  FROM   test 
                  ORDER  BY click_rate DESC 
                  LIMIT  3) top_3_click_rate 
         ON test.id = top_3_click_rate.id 
ORDER  BY top_3_click_rate.click_rate DESC, 
          test.relevance DESC

DEMO

edited Apr 23 '12 at 21:54

answered Apr 23 '12 at 19:36

Conrad Frix

51,984
12
96
155

the relevance field is 0-1 and the click_rate field is 0.1 and up ... would this still work? i really appreciate your answer, ty – califmerchant Apr 23 '12 at 20:01
@califmerchant I don't know why I thought the conversion would be an issue but its not. That said because the relevance field is an order of magnitude larger than the click_rate you may find that it overwhelms the click_rate. You'll probably have to play with the numbers to find values you're happy with – Conrad Frix Apr 23 '12 at 20:20
is there any way to make top 3 results sort by click_rate, and the rest sort by relevance? – califmerchant Apr 23 '12 at 21:27
@califmerchant yes you can and I've updated my answer. You might want to consider editing your question to include asking for this other alternative. That way more people can see that you're asking that question as well. This helps other people that might answer as well as others that might have the same question in the future. – Conrad Frix Apr 23 '12 at 21:57
@califmerchant no problem. I hope you don't mind I updated your question to include both your original question and then your second question. People tend to grumble when they've answered your question and you change it drastically. – Conrad Frix Apr 23 '12 at 22:09
is there anyway to do this under 1 select query and not to use 2 select queries? it's just that 2 full text queries currently will slow things down – califmerchant Apr 23 '12 at 22:11
@califmerchant Sorry I don't know of any. If I were you I would try working on different techniques to optimize your query like using temp tables or calculating and storing the relevance or having your inner query [generate an iterator](http://stackoverflow.com/a/1895127/119477) and then use a [case in your `order by`](http://stackoverflow.com/questions/4346823/mysql-order-by-case-issue) which avoids the double query on your full-text. If you're not successful with that you can ask another question, but be sure to include what you've tried as well as your query execution plans. – Conrad Frix Apr 23 '12 at 22:23

score -4 · Answer 2 · answered Apr 23 '12 at 19:48

-4

You can always use WHERE.

Try this: WHERE RELEVANCE > 0.3 AND RELEVANCE < 0.7 ORDER BY RELEVANCE DESC, CLICK_RATE DESC

answered Apr 23 '12 at 19:48

MaxGenius

23
4

How to weight 'ORDER BY' in mysql?

2 Answers2