MySQL Full-Text Search- Search words with exact match & wildcard but get exact match first other match

Question

I've used Boolean Full-Text Searches to searches words from the database column. I want to get records that exactly match with my records & also including with wildcard search. I am getting the correct result but I want to set order of that result by exact match first. Is there any possible way to achieve this?

I want to perform a search on each word also & get records by relevancy.

I've tried using the following query but it doesn't work because it gives me the wrong rank for the records.

    SELECT
      id,
      search_tags,
      MATCH(search_tags) AGAINST(REPLACE(concat("'car'"," ")," ","* ")  IN BOOLEAN MODE) AS relevance_rank
    FROM
      images
    WHERE
      MATCH(search_tags) AGAINST(REPLACE(concat("'car'"," ")," ","* ")  IN BOOLEAN MODE)
    ORDER BY relevance_rank DESC;

I expect the output of the query to be

   id  search_tag             relevance_rank
   11  car,cards,food,code    2.1669161319732666
   12  car,water,sky          2.1669161319732665
   13  carrier,food,drink     2.1669161319732664

, but the actual output is like

   id  search_tag             relevance_rank1
   11  carrier,food,drink     2.1669161319732666
   12  car,cards,food,code    2.1669161319732666
   13  car,water,sky          2.1669161319732666

I think you should use laravel scoute : https://laravel.com/docs/5.8/scout — Dharmesh Rakholia, Jul 31 '19 at 11:12
@DharmeshRakholia - but I've already used Full-Text Search, Laravel Scout uses the same search. I am not using MVC structure so that's not possible to move on Laravel Scout. Do you have any another way? — Pinal Patel, Jul 31 '19 at 11:19
Possible duplicate of [Getting EXACT matches from full-text search returned first?](https://stackoverflow.com/questions/7217887/getting-exact-matches-from-full-text-search-returned-first) — JorisJ1, Aug 01 '19 at 19:01
@JorisJ1 - My question is not only for order, but I've also the confusion of the same rank. Do you have any idea why it's getting same rank? — Pinal Patel, Aug 02 '19 at 11:09
Sorry, I removed the duplicate flag. No, I do not have an answer yet but I will look at it again later today. — JorisJ1, Aug 02 '19 at 11:18
@JorisJ1 - No problem, I've also edited my question for better understanding. — Pinal Patel, Aug 02 '19 at 11:48

JorisJ1 · Accepted Answer · 2019-08-02T18:44:13.450

This query ranks records with the whole word higher, but car,cards ranks the same as just car so it is still not optimal.

SELECT id, search_tags, 
    MATCH(search_tags) AGAINST('car *') + 
    MATCH(search_tags) AGAINST('car') AS relevance_rank
FROM images
WHERE MATCH(search_tags) AGAINST('car *') 
OR MATCH(search_tags) AGAINST('car')
ORDER BY 3 DESC

+----+---------------------+----------------+
| id |     search_tags     | relevance_rank |
+----+---------------------+----------------+
| 12 | car,cards,food,code | 0.1677478002   |
| 13 | car,water,sky       | 0.1677478002   |
| 11 | carrier,food,drink  | 0.0093915509   |
+----+---------------------+----------------+

This next query also looks at the number of times the exact word occurs in the string. Better result, but perhaps a bit too convoluted.

SELECT id, search_tags, 
    MATCH(search_tags) AGAINST('car *') + 
    MATCH(search_tags) AGAINST('car') AS relevance_rank,
    LENGTH(search_tags) - 
    LENGTH(REPLACE(search_tags, 'car', space(LENGTH('car')) -1 )) AS occurences
FROM images
WHERE MATCH(search_tags) AGAINST('car *') 
OR MATCH(search_tags) AGAINST('car')
ORDER BY 3 DESC, 4 DESC

+----+---------------------+----------------+------------+
| id |     search_tags     | relevance_rank | occurences |
+----+---------------------+----------------+------------+
| 12 | car,cards,food,code | 0.1677478002   |          2 |
| 13 | car,water,sky       | 0.1677478002   |          1 |
| 11 | carrier,food,drink  | 0.0093915509   |          1 |
+----+---------------------+----------------+------------+

score 0 · Answer 2 · answered Aug 02 '19 at 12:31

Should work when using Union :

SELECT
  id,
  search_tags,
  MATCH(search_tags) AGAINST(REPLACE(concat("'car'"," ")," "," ")  IN BOOLEAN MODE) AS relevance_rank,
1 as prio -- sort by priority first
FROM
  images
WHERE
  MATCH(search_tags) AGAINST(REPLACE(concat("'car'"," ")," "," ")  IN BOOLEAN MODE)
ORDER BY relevance_rank DESC 
UNION SELECT
  id,
  search_tags,
  MATCH(search_tags) AGAINST(REPLACE(concat("'car'"," ")," ","* ")  IN BOOLEAN MODE) AS relevance_rank,
 2 as prio -- sort by priority first
FROM
  images
WHERE
  MATCH(search_tags) AGAINST(REPLACE(concat("'car'"," ")," ","* ")  IN BOOLEAN MODE)
ORDER BY prio, relevance_rank DESC;

MySQL Full-Text Search- Search words with exact match & wildcard but get exact match first other match

2 Answers2