Mysql like and BOOLEAN MODE (FULLTEXT) search

Question

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

I have two queries below which works but I like to know which one of the is more optimise.

Query one does gives me what i'm looking for but query two gives me different results.

Which one I should be using.

1. Using Like in my query.

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

2. Then I have boolean mode (FULLTEXT) query.

   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's a significant difference between the two queries.

The first query is searching for an occurrence of the single string 'County Down' within the name column.

The second query is searching for occurrences of either of the two separate words (separate strings) 'County' and 'Down' within the text. (The purpose and effect of that '%' character before 'County' in that second query is unknown to me.)

The relevance from a BOOLEAN MODE fulltext search is going to be 1.0. If you want to return only those rows that have both the words 'County' and 'Down', then you'd really want to use the '+' qualifier before each word, for example:

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

Note that this predicate will also "match" to a name containing 'Some Down and out County', for example, where the first query would not.

---

Also, the approach used to get the result set ordered by relevance is almost right. There's a subtle problem: including IN BOOLEAN MODE modifier causes the expression return 1.0, instead of returning the weighted float as would be returned with NATURAL LANGUAGE MODE.

---

To answer your question: if the first query is returning the result set you need, then use that query. The downside of that query is that the LIKE predicate in that query is not sargable, that is, MySQL can't make use of a index range scan to satisfy that predicate. (An index may be used for the other predicates, but that name column on each of those rows needs to be checked.

The advantage of a query of the second form is that it can make use of a FULLTEXT INDEX, if one is created, which can improve performance.