MySQL - How to get search results with accurate relevance

For others landing here (like I did): in my experience, for best results you can use a conditional depending on the number of search words. If there is only one word use LIKE '%word%', otherwise use boolean full-text searches, like this:

if(sizeof($keywords) > 1){
   $query = "SELECT *,
             MATCH (col1) AGAINST ('+word1* +word2*' IN BOOLEAN MODE) 
             AS relevance1,
             MATCH (col2) AGAINST ('+word1* +word2*' IN BOOLEAN MODE) 
             AS relevance2
             FROM table1 c
             LEFT JOIN table2 p ON p.id = c.id
             WHERE MATCH(col1, col2) 
             AGAINST ('+word1* +word2*' IN BOOLEAN MODE) 
             HAVING (relevance1 + relevance2) > 0
             ORDER BY relevance1 DESC;";
    $execute_query = $this->conn->prepare($query);
}else{          
   $query = "SELECT * FROM table1_description c
             LEFT JOIN table2 p ON p.product_id = c.product_id
             WHERE colum1 LIKE ? AND column2 LIKE ?;";
        // sanitize
        $execute_query = $this->conn->prepare($query);
        $word=htmlspecialchars(strip_tags($keywords[0]));
        $word = "%{$word}%";
        $execute_query->bindParam(1, $word);
        $execute_query->bindParam(2, $word);
    }

You could use string functions, such as:

select id, name
from subjects
where name like concat('%', @search, '%')
order by 
  name like concat(@search, '%') desc,
  ifnull(nullif(instr(name, concat(' ', @search)), 0), 99999),
  ifnull(nullif(instr(name, @search), 0), 99999),
  name;

This gets you all entries containing @search. First those that have it at the beginning, then those that have it after a blank, then by the position of the occurrence, then alphabetical.

name like concat(@search, '%') desc uses MySQL's boolean logic by the way. 1 = true, 0 = false, so ordering this descending gives you true first.

SQL fiddle: http://sqlfiddle.com/#!9/c6321a/1