Geek Answers Handbook

MySQL - How to Get Search Results with Exact Relevance

I have repeatedly reviewed this issue, and I have never found the right answer.

Is it possible to do a MySQL search that returns ACTUAL exactly sorted results by relevance?

I am trying to create an ajax search form that makes suggestions when the user types in the input field, and did not find a suitable solution for this, using only pure MySQL queries. I know that there are search engines such as ElasticSearch, I want to know how to do this, only with a raw MySQL query.

I have a table of school subjects. There are less than 1200 lines, and that will never change. Let us do a basic FULLTEXT search, where the user starts typing "Bio".

Request ("Bio ...") - FULLTEXT BOOLEAN MODE

SELECT name, MATCH(name) AGAINST('bio*' IN BOOLEAN MODE) AS relevance
FROM subjects
WHERE MATCH(name) AGAINST('bio*' IN BOOLEAN MODE)
ORDER BY relevance DESC
LIMIT 10

results

name                                        |  relevance
--------------------------------------------------------
Biomechanics, Biomaterials and Prosthetics  |  1
Applied Biology                             |  1
Behavioural Biology                         |  1
Cell Biology                                |  1
Applied Cell Biology                        |  1
Developmental/Reproductive Biology          |  1
Developmental Biology                       |  1
Reproductive Biology                        |  1
Environmental Biology                       |  1
Marine/Freshwater Biology                   |  1

To show how bad these results are, here is a comparison with a simple LIKEquery that shows increasingly relevant results that were not shown:

Request ("Bio ...") - LIKE

SELECT id, name
WHERE name LIKE 'bio%'
ORDER BY name

results

name                                        |  relevance
--------------------------------------------------------
Bio-organic Chemistry                       |  1
Biochemical Engineering                     |  1
Biodiversity                                |  1
Bioengineering                              |  1
Biogeography                                |  1
Biological Chemistry                        |  1
Biological Sciences                         |  1
Biology                                     |  1
Biomechanics, Biomaterials and Prosthetics  |  1
Biometry                                    |  1

And already you see how many items are not offered, even if they are more likely that the user will search.

However, the problem with use LIKEis how to search across multiple words and in the middle of type words FULLTEXT.

The main ordering I would like to implement is something like:

  • First words starting with search query
  • Second word starting with search query
  • Words where the term is not at the beginning of words
  • Everything is usually in alphabetical order, unless otherwise specified

, , MySQL ?

+7
5

, :

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;

, @search. , , , , , .

, name like concat(@search, '%') desc MySQL. 1 = true, 0 = false, true.

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

+10

, ( ): , . , LIKE '%word%', , :

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);
    }
+5

, , :

$searchTerm = 'John';
// $searchTerm = 'John Smit';
if (substr_count($searchTerm, ' ') <= 1)
    $sql = "SELECT id, name
    FROM people
    WHERE name like '%{$searchTerm}%')
    ORDER BY
      name LIKE '{$searchTerm}%') DESC,
      ifnull(nullif(instr(name, ' {$searchTerm}'), 0), 99999),
      ifnull(nullif(instr(name, '{$searchTerm}'), 0), 99999),
      name
    LIMIT 10";
}
else {
$searchTerm = '+' . str_replace(' ', ' +', $searchTerm) . '*';
$sql = "SELECT id,name, MATCH(lead.name) AGAINST('{$searchTerm}' IN BOOLEAN MODE) AS SCORE
        FROM lead
    WHERE MATCH(lead.name) AGAINST('{$searchTerm}' IN BOOLEAN MODE)
    ORDER BY `SCORE` DESC
    LIMIT 10";

, ( , , ), reset , OPTIMIZE table_name.

, Jo, , Jo, John, !

+2

.

SET @src := 'bio';
SELECT name,
name LIKE (CONCAT(@src,'%')),
         LEFT(SUBSTRING_INDEX(SUBSTRING_INDEX(name,' ',2),' ',-1),LENGTH(@src)) = @src,
         name LIKE (CONCAT('%',@src,'%'))
FROM subjects
ORDER BY name LIKE (CONCAT(@src,'%')) DESC,
         LEFT(SUBSTRING_INDEX(SUBSTRING_INDEX(name,' ',2),' ',-1),LENGTH(@src)) = @src DESC,
         name LIKE (CONCAT('%',@src,'%')) DESC,
         name

http://sqlfiddle.com/#!9/6bffa/1

, , @src too VARCHAR?

+1
MATCH(s.name) AGAINST('"Applied Bio"' IN BOOLEAN MODE)

, , .

ORDER BY s.name like concat("Applied Bio", '%') desc,
ifnull(nullif(instr(s.name, concat(' ', "Applied Bio")), 0), 99999),
ifnull(nullif(instr(s.name, "Applied Bio"), 0), 99999),
s.name

, .

SQL:

SELECT SQL_NO_CACHE 
s.id, s.name
FROM subjects s use index(name_fulltext) 
WHERE 
MATCH(s.name) AGAINST('"Applied Bio"' IN BOOLEAN MODE) 
GROUP BY s.id 
ORDER BY 
s.name like concat("Applied Bio", '%') desc,
ifnull(nullif(instr(s.name, concat(' ', "Applied Bio")), 0), 99999),
ifnull(nullif(instr(s.name, "Applied Bio"), 0), 99999),
s.name
LIMIT 100;
0

More articles:


All Articles