Mysql match against alternative

Question

I need a search with a relevance algorithm and the database is mysql. I have to sort the results by date, if keyword is in title or not, number of apparitions of the keyword in the text and so on.

Match against doesn't give me that much control.

Answer 1

Something like this would do what you want:

select
  * 
from
  myTable
order by
  sum
  (
    itemdate='2009-10-09',
    title like '%keyword%',
    text like '%keyword%', 
    criteria4,
    criteria5
  )

• Each criteria returns 0 if false, and 1 if true.
• You sum each of those and order by the result.
• Rows that have the most matching criteria will be on top.

You can finetune all this of course by not just having 0 or 1 per criteria.