Mysql like and BOOLEAN MODE (FULLTEXT) search

Question

I am trying to write a query to search for a record using a wild card.

I have two queries below that work, but I like to know which one is more optimized.

Query one gives me what I'm looking for, but query two gives me different results.

Which should I use.

• Usage As in my request.

  SELECT code, name
  FROM countryCounty
  WHERE name LIKE  '%County Down%'
  AND isActive =1
  AND countryISO2FK =  'GB'
  LIMIT 1
  

• Then I have a logical mode request (FULLTEXT).

  SELECT code,name, match( name )
  AGAINST ( 'County Down' IN BOOLEAN MODE ) AS relevance
  FROM opjb_countryCounty 
  WHERE match( name ) AGAINST ( '%County Down%' IN BOOLEAN MODE ) 
      AND isActive=1 
      AND countryISO2FK='GB' 
  ORDER BY relevance DESC LIMIT 1
  

Answer 1

There is a significant difference between the two queries.

The first query is to find the occurrence of one row 'County Down'in the name column.

- ( ) 'County' 'Down' . ( '%' 'County' .)

BOOLEAN MODE 1.0. , "" "", '+' , :

MATCH(name) AGAINST('+County +Down' IN BOOLEAN MODE)

, "" , 'Some Down and out County', , .

---

, , , , . : IN BOOLEAN MODE return 1.0 , , NATURAL LANGUAGE MODE.

---

: , . , LIKE , MySQL . ( , name .

, FULLTEXT INDEX, , .

Answer 2

, , .

WHERE name LIKE '% County Down%'

WHERE match (name) AGAINST ('% County Down%' BOOLEAN MODE)

"LACounty Down" "NYCounty Down", . , "LA County Down" "NY County Down", .

" ", ...

WHERE match( name ) AGAINST ( '*County Down*' IN BOOLEAN MODE )